- 1,067,371invoice lines in the sourcetwo sheets, Dec 2009 – Dec 2011
- SHA-256pinned source archivea changed upstream file fails acquisition
- 3steps rerun in CI to prove idempotencyBronze, Silver, customer history
- 4layers of quality gatesfailed rows go to quarantine, with the raw record
End to end
One Databricks job runs the layers in order, with the calendar, customer history and simulated campaigns built in parallel from Silver. Every step records its run in ops.pipeline_runs and its checks in ops.dq_results.
Acquire downloads the public archive and verifies its size and SHA-256 checksum. Bronze lands every column as text with lineage, once per file. Silver applies the canonical model in one atomic merge per entity, behind error-level quality gates, sending rejected rows to quarantine. Gold is built with dbt, including calendar, customer history and campaign tables, and tested against Silver. Export writes Parquet and reconciliation totals for BI.
- AcquirePinned downloadSize and SHA-256 verified, three retries, atomic manifest.
- landing/
size + SHA-256fails acquisition - landing/
- BronzeAs deliveredEvery column as text, with lineage; a file already ingested is skipped.
- bronze.
*
row countserror stops run - bronze.
- SilverCanonical modelOne atomic MERGE per entity, behind pre- and post-write gates.
- transaction_
line - customer
- product
- quarantine
quality gates→ quarantine - transaction_
- GoldFacts, dims, martsdbt models and tests, plus calendar, customer and campaign tables.
- fct_
transaction_ line - dim_
date - mart_
*
reconciles to Silverdbt test - fct_
- ExportFor Power BIParquet plus the totals the Power BI semantic model must reproduce exactly.
- *.
parquet - reconciliation.
json
- *.
The data
UCI Online Retail II: a UK online retailer selling giftware, many of its customers wholesale. Two sheets of 525,461 and 541,910 lines cover 1 December 2009 to 9 December 2011. They overlap for nine days, so 22,523 lines appear in both and are dropped once as duplicates rather than counted twice. One line in five to one in four has no customer ID: those lines still count towards sales but not towards customer metrics.
Quality gates
Two severities and one rule: error stops the pipeline, warn is recorded and the run continues. Rows that fail validation are not dropped silently: they go to silver.quarantine with the raw record, so every gap can be explained.
Bronze checks that row counts match the landed file and source rows are unique, and warns on exact duplicate lines. Silver checks before writing that keys are unique, required fields are present, line types are valid, amount equals quantity times price and sale quantities are positive; after writing, that references exist and Silver never exceeds Bronze per file. Rows with missing ids or unparseable values go to quarantine. Gold dbt tests reconcile net sales and row counts to Silver.
Deployment and CI
The platform is defined in code. Terraform creates the storage, the Databricks workspace and Unity Catalog with its schemas and grants; a Databricks Asset Bundle defines the job for development and production, where it runs as a service principal. Every change runs three CI jobs, the last of them the whole pipeline on the real dataset.
Three CI jobs run on every change. Python checks: lint, formatting, tests and a clean-room scan. Infrastructure and security: full-history secret scan, Terraform format and validate, and bundle schema validation. Pipeline on real data: every step from acquisition to export, with Bronze, Silver and customer history rerun to prove idempotency. Deployment uses Terraform modules for storage, the Databricks workspace and Unity Catalog, and a Databricks Asset Bundle with dev and prod targets.