#263 Data modelling 6.8 min
Medallion Architecture: Bronze, Silver, Gold in Python
A messy CSV summed straight from raw says 99 pounds; after bronze, silver and gold it is 86.
Predict the three layers
Copy the code folder first. Open orders_raw.csv and find the broken rows: a duplicate, a text amount, a missing date and a negative amount. Predict how many rows silver keeps and which line numbers it rejects, then run python run.py bronze, python run.py silver and python run.py gold to check.
A hint
Line numbers in the rejects table count the header as line 1.
Fix the text amount
In orders_raw.csv, change the amount abc on the A4 line to 7. Predict the new silver row count and the total in gold, then run python run.py silver and python run.py gold. When I ran it, silver kept 6 orders and rejected 3, A4 appeared as 700 pence, and the gold check held at 9300 pence. Change the 7 back to abc afterwards, because the next tasks expect the original file.
A hint
The amount abc was the reason for the row 6 reject.
Add a row from Germany
Add the line A9,2026-03-03,Germany,10 to the end of orders_raw.csv. Predict why silver will refuse it, then run python run.py silver. When I ran it, row 11 was rejected as unknown country and the check read bronze rows 10 = kept 5 + rejected 5.
A hint
Silver only accepts countries found in the COUNTRIES map in layers.py.
Teach silver about Germany
With the A9 Germany line still in the CSV, add "GERMANY": "DE" to the COUNTRIES dictionary in layers.py. Predict the gold rows and the raw bronze sum, then run python run.py gold. When I ran it, gold had a DE row for 2026-03-03 with 10.00, the gold check said ok at 9600 pence, and the raw sum off bronze was 109.00.
A hint
Country names are trimmed and upper cased before the lookup.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Nine made-up rows in SQLite, not a lakehouse; no Delta tables, Spark or incremental loads, and the pattern is described by vendors.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
Related videos
- #115 Schema Evolution Without Breaking Pipelines Data engineering and pipelines
- #266 Event Data Modelling: Events, Sessions, Late Arrivals Data modelling
- #107 Data Contracts: Stop Breaking Downstream Data engineering and pipelines
- #111 Incremental Loads: Watermarks and the Late Update Trap Data engineering and pipelines
