01
Bronze, raw and untouched
Six CSVs bulk-loaded exactly as they arrive. No renaming, no casting, no filtering. Its only promise is that it faithfully reproduces the source, and it is never edited afterwards.
Case study 04 · Data engineering
Six raw CSV extracts from two unrelated business systems (a CRM and an ERP) turned into a single analysis-ready star schema in SQL Server. Built on a Bronze → Silver → Gold medallion architecture, where each layer has exactly one job and the layer below it is never modified.
Learning project. This was my first time using SQL and Python inside a real project rather than for problem solving, so I followed a guided tutorial to get going and then extended it with my own analysis, visualisations and optimisations to take it past where the tutorial stopped.
01 · How it works
A CRM knows customers, products and orders. An ERP knows the same customers under different keys, plus their country and product category. Neither is wrong and neither is complete, so the moment anyone wants revenue by category or by country they have to reconcile the two, and every analyst who needs that number reconciles it again, slightly differently. A warehouse is the decision to do that reconciliation once, write it down as code, and let everyone query the result.
Six CSVs bulk-loaded exactly as they arrive. No renaming, no casting, no filtering. Its only promise is that it faithfully reproduces the source, and it is never edited afterwards.
Trimmed strings, coded values expanded into readable labels, types corrected, missing entries resolved. Same grain as Bronze, so this layer changes quality, not structure.
Business-facing views joining CRM and ERP into one fact table and two conformed dimensions. "Conformed" meaning both systems finally agree on what a customer is, which is the entire point.
Two check suites written so that green means zero rows: no nulls in keys, no duplicates, no stray whitespace, no invalid dates. If a query returns rows, the layer does not get promoted.
Each layer is its own set of DDL scripts, so any stage can be rebuilt without touching the others.
ROW_NUMBER() so downstream models never depend on a source system's key.The Gold quality suite asserts that every surrogate key is unique and that no fact row points at a missing dimension, the two failures that quietly corrupt every downstream aggregate.
SELECT with two joins instead of a nested reconciliation.02 · Challenges
The CRM identifies a customer one way, the ERP another. There is no lookup table that maps them, so joining the two systems is a rule I had to decide on, not a fact I could look up. Get it wrong and every number downstream is wrong in a way that still looks plausible. The important part was putting that rule in exactly one place in the Silver layer, versioned, instead of letting it be re-invented in each query.
It is tempting to clean while loading. One trim, one cast, it saves a step. I made Bronze append-only and completely untransformed instead, which felt like extra work for no gain until the first time a Silver rule turned out to be wrong. Because the raw layer was still exactly what the source sent, the fix was a re-run. If I had cleaned on the way in, it would have been a data-recovery job with no clean copy to go back to.
My first quality checks returned counts, and a count is easy to skim past. I rewrote them so a passing run returns zero rows, which means any output at all is a failure you cannot ignore. Nulls in keys, duplicate business keys, untrimmed values, dates outside valid ranges. Promotion to the next layer only happens on green.
03 · Limitations
Every run rebuilds from the CSVs. Fine at this size, wrong at production volume, where you would load only what changed.
Scripts run in order by hand. There is no scheduler, no retries and no dependency graph, so nothing recovers on its own.
Dimensions are overwritten. If a customer changes country, last year's orders silently move with them, because there are no slowly changing dimensions.
Sources are CSV extracts, not live connections. Real integration brings schema drift and late-arriving data that this design has never had to survive.
The suites catch nulls, duplicates and bad types. They cannot tell whether a revenue figure is business-correct.
The model answers sales questions. Anything else needs a new fact table and probably a new grain.
04 · What I learned
Bronze, Silver and Gold only help because each one promises exactly one thing. The moment a layer starts doing two jobs, the boundary stops being useful and you are back to one big script.
Refusing to clean on load felt wasteful right up until a rule was wrong. An untouched raw layer turns a bad decision into a re-run instead of a recovery.
Tests that return counts get skimmed. Tests that return zero rows on success cannot be misread, and that small framing change is what makes the gate real.
Choosing how two systems agree on a customer is a business call disguised as a join. Writing it down once, in code, is most of what a warehouse actually is.
All DDL, the load procedure, both quality-check suites and the data catalogue are in the repo, plus the data-flow, integration and model diagrams.