Samenvatting
Survey-sync kijkt per vragenlijst op een extern survey-platform of er nieuwe reacties zijn binnengekomen, en schrijft die weg naar het datawarehouse. Elke vragenlijst krijgt een eigen tabel met een dynamisch schema: kolommen worden niet vooraf vastgelegd maar bij elke run ontdekt uit de binnenkomende data, en nieuwe vragen leiden automatisch tot nieuwe kolommen. De sync is idempotent en incrementeel: een high-watermark per vragenlijst bepaalt of er uberhaupt een API-call nodig is, en een MERGE werkt bestaande reacties bij zonder ooit iets te verwijderen. Dit is de eerste van drie stappen in een grotere rapportagepijplijn.
De vraag
Reacties op vragenlijsten moesten geautomatiseerd en herhaalbaar in het datawarehouse belanden, met behoud van de volledige wijzigingsgeschiedenis per reactie, zodat rapportages op de laatste stand kunnen draaien zonder dat oudere standen verloren gaan.
Wat ik heb gebouwd
- Een Python-script dat via de REST API van het survey-platform per vragenlijst controleert of er nieuwe reacties zijn, met paginering over het volledige resultaat
- Een dynamisch tabelschema per vragenlijst: onbekende vragenlijsten krijgen automatisch een tabel, nieuwe vragen automatisch een kolom via ALTER TABLE
- System-versioned temporal tables per vragenlijst, met een automatisch onderhouden geschiedenis-tabel voor elke wijziging aan een reactie
- Een incrementele, idempotente MERGE op basis van een high-watermark, zodat een vragenlijst zonder nieuwe reacties zonder API-call wordt overgeslagen en een herhaalde run nooit duplicaten oplevert
- Expliciete statusregistratie per vragenlijst per run (geslaagd, overgeslagen, leeg of mislukt) met rijaantallen en foutmeldingen in een centrale logtabel
- Een workaround voor een platform-eigenaardigheid waarbij het overzichtsendpoint een status-veld onjuist teruggeeft; de sync gaat af op de daadwerkelijk aanwezige reacties, zodat gearchiveerde vragenlijsten met historie toch meekomen
In detail
- Schema-detectie at runtime: kolommen worden per vragenlijst ontdekt uit de binnenkomende data, met sanitizing naar geldige SQL-identifiers
- Volledige audit-trail via temporal tables: elke wijziging aan een reactie blijft raadpleegbaar zonder dat de hoofdtabel volloopt met dubbele rijen
- Incrementeel en idempotent: een high-watermark voorkomt onnodige API-calls, en de MERGE op een uniek ID voorkomt duplicaten en dataverlies
- Expliciete faalafhandeling: bij een fout op één vragenlijst wordt de high-watermark teruggezet zodat de volgende run het opnieuw probeert, terwijl de rest doorloopt
- Elke run is herleidbaar: het statusoverzicht wordt uit de logtabel opgebouwd, niet uit een losse telling die uit sync kan lopen
Resultaat
In productie en dagelijks in gebruik als eerste stap van een drie-staps rapportagepijplijn.