Summary
The data warehouse of a Dutch trade union had grown since around 2020 into eight databases, one per source or layer, with references running between them and a metadata-driven load engine as its backbone. This migration brings everything together into a single database with a schema per layer and per source, and uses the consolidation for a round of improvement: only live objects come along, and a layer of 144 business views, all but four of them plain renames, is folded into the transformation layer. The load engine has been rebuilt for a single database and extended with a lineage harvest, a generated compatibility layer for existing reports, and quality checks. Because these are the figures the organisation steers by, the whole migration is set up to be verifiable: every difference from the old warehouse is measured daily, and whatever is not explained exactly shows up as an alert in the daily report.
The brief
Bring eight databases together into a single data warehouse and take the chance to improve it, without breaking existing Power BI reports. Everything was open for discussion. The only fixed points were that loading stays metadata-driven, that existing reports keep working, and that logging, lineage and restartability are requirements rather than extras: every step has to be measurable and verifiable.
What I built
- A single database with a schema per layer (landing, staging, transformation, data mart) and per source, for fourteen sources including ERP, CRM, contact centre, HR and the website; references between databases disappear with it
- A catalogue of source tables from which the engine generates landing and staging tables and updates them when the source schema changes: columns added, widening only, never a silent shrink, and an impact check through the lineage before any change to an existing table
- A dynamic MERGE load with temporal history and soft delete, restartable per table, and materialisation into the data mart with fixed table definitions in git: a column the view does not deliver fails loudly instead of quietly disappearing
- A generated compatibility layer with the old object and column names, on which existing Power BI reports can keep running during the switch; a name mapping that no longer holds raises an error in the database instead of producing a broken report
- Lineage at object and column level, harvested from the database itself wherever possible and registered by hand only for calculated columns, a glossary of 137 terms and 2,412 column descriptions, and seven kinds of quality check, from row counts and freshness to orphaned keys and grain
- A permission model with five roles and a GRANT/DENY matrix per schema, load procedures that run under a fixed owner while the actual caller stays visible in the logging, and a classification of special category personal data (GDPR Article 9) on 740 columns
In detail
- Reconciliation without tolerance: every night the data marts of old and new are counted per table, and a difference is either explained exactly in a table of expectations or reported. On introduction that took the daily report from 30 open differences down to 6
- Errors a comparison cannot see, recorded separately: when old and new are equally wrong, a row count reports nothing. A findings register keeps cause, evidence and status per error, separately for the old and the new warehouse, so the migration also brings existing errors to the surface
- Proof instead of assumption: rebuilt logic is measured against the old data mart (unchanged columns exactly equal, every difference explained per mechanism), and 28 end-to-end test suites run as a single regression chain, because the build cannot validate the dynamic SQL of a metadata engine
- A dress rehearsal of the data migration, with hash verification, rollback, restart and an idempotency counter-test. That rehearsal found a ported view that needed over five hours on a populated database; after the fix, 57 seconds, with proof that the result did not change
- Pace from a fixed way of working: per domain a design with explicit choices and an implementation plan with the full SQL per task; each task is carried out by an AI agent and checked by a second, independent agent that reruns the tests itself. Design choices and merges stay with a person. The engine and the first fourteen plans were in place within ten days
Outcome
State as of September 2026: the logic of all fourteen sources has been ported, with 138 data mart tables on a catalogue of 499 source tables, and since early September the new warehouse has run every night as a shadow alongside the existing one on the pre-production environment, with quality checks and the daily reconciliation. So far just over ten weeks and more than 700 commits. Still to come: the test phase and the cutover, in which the reports move over to the new warehouse.