learning-it by example

#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.

1. In the medallion architecture, what is the job of each of the bronze, silver and gold layers?
2. The same messy 9-row CSV was summed by amount straight from the raw data and then through bronze, silver and gold. What were the two totals?
3. Why does the bronze table keep every column as text and store each row untouched?
4. In a SQLite silver layer where the order id is the primary key, how is an order that appears twice detected and handled?
5. In the silver layer of a medallion pipeline built in SQLite, why is each amount stored as whole pence?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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

All Data modelling videos

Related videos