Summary
Survey-sync checks per survey on an external survey platform whether new responses have come in, and writes them to the data warehouse. Every survey gets its own table with a dynamic schema: columns are not fixed in advance but discovered from the incoming data on each run, and new questions automatically lead to new columns. The sync is idempotent and incremental: a high watermark per survey decides whether an API call is needed at all, and a MERGE updates existing responses without ever deleting anything. This is the first of three steps in a larger reporting pipeline.
The brief
Survey responses had to land in the data warehouse automatically and repeatably, keeping the full change history per response, so that reporting can run on the latest state without older states being lost.
What I built
- A Python script that checks per survey, through the REST API of the survey platform, whether there are new responses, paging through the full result
- A dynamic table schema per survey: unknown surveys automatically get a table, new questions automatically get a column via ALTER TABLE
- System-versioned temporal tables per survey, with an automatically maintained history table for every change to a response
- An incremental, idempotent MERGE based on a high watermark, so that a survey without new responses is skipped without an API call and a repeated run never produces duplicates
- Explicit status recording per survey per run (succeeded, skipped, empty or failed) with row counts and error messages in a central log table
- A workaround for a platform quirk where the overview endpoint reports a status field incorrectly; the sync goes by the responses that are actually there, so archived surveys with history still come along
In detail
- Schema detection at runtime: columns are discovered per survey from the incoming data, sanitised into valid SQL identifiers
- A full audit trail through temporal tables: every change to a response stays available without the main table filling up with duplicate rows
- Incremental and idempotent: a high watermark avoids needless API calls, and the MERGE on a unique ID avoids duplicates and data loss
- Explicit failure handling: on an error in one survey the high watermark is put back so the next run tries again, while the rest carries on
- Every run is traceable: the status overview is built from the log table, not from a separate count that can drift out of sync
Outcome
In production and in daily use as the first step of a three-step reporting pipeline.