Data Engineering · Episode 06 · 2:05
Medallion architecture explained: bronze, silver and gold layers
Raw events land in bronze untouched. A '$' parsing bug in silver silently dropped 3,713 rows, and replaying from bronze after the fix moved revenue from $804,262 to $1,200,704.
What you’ll needPython 3.10+, DuckDB
What you’ll need
- python.org/downloads →
Python 3.10 or newerRuns the code. Check yours with python3 --version. - code.visualstudio.com →
VS Code or CursorEither works; so does any editor you like. - git-scm.com/downloads →
gitGets the code from GitHub. 
DuckDB 1.0+SQL on files and tables, no database serverpip install "duckdb>=1.0"
Set it up
- 1Get the code
git clone https://github.com/DayanEbrar0X/data-anatomy.ai.git cd data-anatomy.ai
- 2Make a virtual environmentIt keeps this project's packages separate from the rest of your computer.
python3 -m venv .venv source .venv/bin/activate
- 3Install the packagesrequirements.txt installs every lesson's packages. This one needs only: DuckDB.
pip install -r requirements.txt # or just this lesson: pip install "duckdb>=1.0"
- 4Run it
cd data-engineering/20-medallion-architecture python3 scripts/make_events.py cd src python3 medallion.py
Project structure20-medallion-architecture/
Project structure
data-engineering/20-medallion-architecture/Open on GitHub →
- scripts/
- make_events.pymainwrites events.csv into src/ and short/ (seeded, 10,000 rows)
- short/
- layers.pyprints row counts per layer and the gold table
- medallion.pyrunthe code from the video
- replay.pythe 8-line version from the short
- src/
- layers.pyprints row counts per layer and the gold table
- medallion.pyrunthe code from the video
- README.mdthe idea, how to run it, a line-by-line walkthrough, things to try
data-engineering/20-medallion-architecture/src/medallion.pyOpen on GitHub →
import duckdb
from layers import report, show_gold
def bronze(db): # land raw events as-is, all text
db.sql("""COPY (FROM read_csv('events.csv',
all_varchar=true)) TO 'bronze.parquet'""")
def silver(db, amount): # clean, dedupe, type
db.sql(f"""CREATE OR REPLACE TABLE silver AS
SELECT DISTINCT ON (event_id) event_id,
upper(trim(country)) AS country,
TRY_CAST(ts AS TIMESTAMP) AS time,
TRY_CAST({amount} AS DECIMAL(9,2)) AS usd
FROM 'bronze.parquet'
WHERE time IS NOT NULL
AND usd IS NOT NULL""")
def gold(db): # the business table
db.sql("""CREATE OR REPLACE TABLE gold AS
SELECT country, count(*) AS orders,
round(sum(usd)) AS revenue
FROM silver GROUP BY country""")
if __name__ == "__main__":First 24 of 31 lines
Transcript
Every serious data platform has three layers. Here's why. Bronze, silver, gold. The medallion architecture.
Like a kitchen: groceries delivered, then chopped, then plated. Here are 10,000 raw order events, from three apps. 400 are retries, sent twice. Country codes come padded, in mixed case.
Web amounts carry a dollar sign. And a few rows can't be read. Bronze lands the file as it is. Every column stays text.
Parquet makes it cheap to keep. Nothing is lost. Silver takes an amount expression, and builds the clean table. DISTINCT ON keeps one row per event.
The retries are gone. Country codes are trimmed and uppercased. TRY_CAST types the time and amount. What it can't read becomes null, and the filter drops those rows.
Gold is what the business reads: orders and revenue, per country. Bronze loads once. Version one passes the raw amount column. Version two strips the dollar sign first, then rebuilds from the same bronze.
Let's run it. Bronze: 10,000 rows, 9,600 unique, still raw text. Version one: silver keeps 6,287 and drops 3,713. Revenue, 804 thousand.
That's the bug. TRY_CAST can't read a dollar sign, so every web order was dropped. Version two, replayed from bronze: 9,420 kept, 580 dropped. Revenue, 1.
2 million. And gold holds four countries, about 300 thousand each. Without bronze, those orders would be gone, unless every app could send them again. Bronze is your undo button.
Silver, one trusted copy. Gold, the business answers. One gotcha: bronze keeps raw personal data too. Lock it down, and set retention.
Land it raw, clean it once, shape it for the business. Found a bug? Replay from bronze.
Read the lesson on GitHub →- #dataengineering
- #medallionarchitecture
- #duckdb
- #datalakehouse
