Summary
Until recently a Dutch trade union brought in administrative and financial data through a monthly database restore, weeks behind. A daily feed has been put alongside it that fetches two data streams straight from the REST API of the source system. The data runs through a stage layer into system-versioned temporal tables, after which a MERGE writes history only on real changes and phases out deleted rows cleanly. The result lands in the BI layer that the Power BI reports run on, with a link between budget and actuals per period.
The brief
Get financial and administrative data out of the source system faster and more reliably for reporting, without shutting down the existing delayed restore straight away. Alongside that, a second feed linking budget figures to the actuals.
What I built
- Two Python pipelines (administrative lines and budget) that query the same REST API with a bearer token, per year
- Strongly typed transformation of the raw JSON (dates, amounts, booleans, ids) before the data goes into the database
- A stage layer (bulk insert, fast_executemany) followed by a dedup and MERGE step into temporal tables, with delete-missing scoped to the years fetched so older history does not disappear
- Filling the stage layer and the MERGE procedure in a single database transaction, with a rollback as soon as anything goes wrong
- Integration into the existing nightly SQL Agent job, logging start, end and row counts
- A BI layer that links budget and actuals, so both can be used side by side in one report
In detail
- Full history: every change stays traceable through temporal tables, including after removal from the current data
- Idempotent: a repeated run without a change in the source data adds no new history
- Transactional consistency: on an error in the merge step the whole run is rolled back, no half state in the database
- Configurable period: which years are fetched is a setting, with a separate default for the budget feed because budgets are set in advance
- Deployable alongside the old, slower data stream, so nothing had to be shut down at short notice
Outcome
In production and in daily use as a fixed step in the nightly processing; the budget feed supplies a report that puts budget and actuals side by side per period.