Azure SQL to Lakehouse, with SCD2 History

Why I built it

Anyone can show a pipeline that ran green once. The harder question is what happens on the second run, and on the run after a source row changes. This project exists to answer that with evidence.

Questions it answers

• How do you load only what changed, without a full table scan every night?
• What stops a re-run from double-counting rows that were already loaded?
• When a customer changes plan, can the warehouse still answer what plan they were on last quarter?

How I built it

1. One pipeline, every table. A ForEach loop reads a config array of tables and their CDC columns, so adding a table is a config change rather than a new pipeline.
2. Watermark incremental loading. Each run looks up the last loaded timestamp, pulls only rows newer than it, then writes the new high-water mark back to the lake.
3. Bronze to silver with Auto Loader. Databricks streams the landed Parquet into Delta tables with checkpoints, so reruns are idempotent by construction.
4. Shared transformation library. Common cleanups live in one reusable class the silver notebook imports, instead of being copy-pasted per table.
5. Gold with Declarative Pipelines. Auto CDC flows build Type 2 dimensions keyed on the business key and sequenced by the update timestamp, plus a Type 1 upsert for the fact table.
6. Governance and alerting. Unity Catalog external locations and a managed catalog control access; a Logic App emails on pipeline failure.

How I know it works

• Idempotency: a second full notebook run left row counts unchanged — the checkpoints held.
• Reconciliation: bronze and silver row counts match exactly, so nothing was silently dropped in transformation.
• History: after updating three source rows, gold returned twelve rows for those users — contiguous validity windows where each end date equals the next start date, exactly one open row per user, and a plan change preserved as history.
• Layer counts at the final run: 509 rows in silver, 509 in gold, 500 of them current.