The Medallion Architecture

The medallion idea

Imagine one giant SQL query that reads raw CSVs, fixes bad dates, deduplicates, joins five sources, and produces the revenue report your CEO sees — all at once. When the number looks wrong (and it will), where do you even start debugging? You can't see the middle. You can't re-run just the broken part. And the next report has to copy-paste your cleaning logic all over again.

The medallion architecture (popularized by the lakehouse world, but useful in any warehouse) solves this by splitting your pipeline into three named layers, each with a clear job. Data flows strictly in one direction, getting more refined at every step:

The names are a convention — "raw / staging / marts" mean the same thing — but the discipline is the point: every transformation has exactly one layer it belongs in. When something breaks, you know which layer to inspect. When you need a new report, you build on silver instead of re-cleaning from scratch.

Bronze: raw, immutable, append-only

The bronze layer is the landing zone: source data captured exactly as it arrived, with as little change as possible. No fixing typos, no renaming columns, no dropping bad rows. You read from the source and write it down faithfully — ideally appending, never overwriting, so every load adds a new batch rather than replacing the old one.

This feels wasteful to beginners ("why keep messy data?"), but it's the single most important habit in the architecture. Here's why you keep raw:

Silver: cleaned, deduped, typed, conformed

Silver is where the engineering happens — the bulk of your effort and most of your code lives here. The goal is to turn faithful-but-messy bronze into clean, trustworthy, analysis-ready tables that still look roughly like the source (one silver table per source entity), just correct. Typical work in silver:

• Typing — cast the text columns from bronze into real DATE, DECIMAL, INTEGER types, rejecting or flagging values that don't parse.
• Cleaning — trim whitespace, normalize casing ("US " → "us"), standardize nulls, fix obvious garbage.
• Deduplication — the same row arrived twice from a retry? Keep one. (Pick the latest by a timestamp, drop the rest.)
• Conforming — make columns agree across sources: one canonical customer_id format, one currency, one timezone, consistent names.
• Joining / enriching — stitch related sources together so downstream layers don't have to.
• Validating — drop or quarantine rows that fail business rules (negative hours, end before start).

Notice that silver still mirrors the source's grain — one row per rental, one row per customer. It is not yet shaped for any particular report. That's deliberate: a single clean silver table will feed many different gold marts, so it stays general-purpose.

Gold: business-ready marts & metrics

Gold is the layer your BI dashboards, analysts, and stakeholders actually query. These are the dimensional models you built in Chapters 02–03 — fact tables, dimension tables, the star schema — plus aggregated metric tables built for specific questions. Gold is shaped for consumption: pre-joined, pre-aggregated, named in business language, fast to read.

A crucial point: one silver layer feeds many gold marts. A clean silver.rentals table might power a revenue star schema, a separate utilization mart, and a finance reconciliation table — all without re-cleaning the data three times. That reuse is exactly what makes the layering worth it.

Why layering beats one giant transform

You could, in principle, produce gold directly from sources in one heroic query. Engineers who've maintained that query never do it twice. Here's what layers buy you:

Hands-on: move the marketplace data through the layers

Let's build the three layers as schemas in one DuckDB database and push the marketplace rentals through them: land raw → clean to silver → build a gold star schema. Start a DuckDB session against a file so your work persists:

# from your mini-griddp repo
python -c "import duckdb; duckdb.connect('warehouse.duckdb')"   # create the db file
duckdb warehouse.duckdb     # open an interactive DuckDB shell (or use python)

Step 1 — create the layer schemas and land raw data into bronze. We'll simulate a deliberately messy source: inconsistent casing, a duplicate row, a text date, and one invalid record. Bronze stores it all as text, exactly as received.

CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

-- Bronze: schema-on-read. Everything is VARCHAR; we capture, we don't judge.
CREATE OR REPLACE TABLE bronze.rentals (
  rental_id   VARCHAR,
  customer    VARCHAR,
  gpu_model   VARCHAR,
  region      VARCHAR,
  hours       VARCHAR,
  rate_usd    VARCHAR,
  started_at  VARCHAR,
  loaded_at   TIMESTAMP DEFAULT now()   -- when WE landed it (audit)
);

INSERT INTO bronze.rentals
  (rental_id, customer, gpu_model, region, hours, rate_usd, started_at)
VALUES
  ('1', 'acme',   'A100', 'US ', '10',  '2.50', '2026-06-01'),
  ('2', 'Acme',   'A100', 'us',  '5',   '2.50', '2026-06-02'),
  ('3', 'globex', 'H100', 'EU',  '8',   '4.00', '2026-06-02'),
  ('3', 'globex', 'H100', 'EU',  '8',   '4.00', '2026-06-02'),  -- dup (retry)
  ('4', 'initech','H100', 'eu',  '-3',  '4.00', '2026-06-03'),  -- invalid hours
  ('5', 'globex', 'A100', 'US',  '12',  '2.50', 'not-a-date');  -- bad date

SELECT count(*) AS bronze_rows FROM bronze.rentals;

Step 2 — clean into silver. Now we type, normalize, deduplicate, and validate. Each fix is explicit and lives here, where we can read it:

CREATE OR REPLACE TABLE silver.rentals AS
WITH typed AS (
  SELECT
    CAST(rental_id AS INTEGER)                 AS rental_id,
    lower(trim(customer))                      AS customer,        -- conform casing
    gpu_model,
    lower(trim(region))                        AS region,          -- "US " -> "us"
    CAST(hours AS INTEGER)                      AS hours,
    CAST(rate_usd AS DECIMAL(8,2))              AS rate_usd,
    try_cast(started_at AS DATE)                AS started_at,      -- bad date -> NULL
    loaded_at
  FROM bronze.rentals
),
deduped AS (                                   -- keep one row per rental_id
  SELECT *, row_number() OVER (
            PARTITION BY rental_id ORDER BY loaded_at DESC) AS rn
  FROM typed
)
SELECT rental_id, customer, gpu_model, region, hours, rate_usd, started_at
FROM deduped
WHERE rn = 1                                   -- drop the duplicate
  AND hours > 0                                -- drop invalid hours
  AND started_at IS NOT NULL;                  -- drop the unparseable date

SELECT * FROM silver.rentals ORDER BY rental_id;

Step 3 — build the gold star schema. Shape silver into the dimensional model from Chapters 02–03: a fact table at the rental grain plus a conformed customer dimension. Gold is what a dashboard would query.

-- Dimension: one row per customer (surrogate key)
CREATE OR REPLACE TABLE gold.dim_customer AS
SELECT row_number() OVER (ORDER BY customer) AS customer_key,
       customer AS customer_name
FROM (SELECT DISTINCT customer FROM silver.rentals);

-- Fact: one row per rental, with the additive revenue measure
CREATE OR REPLACE TABLE gold.fct_rental AS
SELECT r.rental_id,
       c.customer_key,
       r.gpu_model,
       r.region,
       r.started_at,
       r.hours,
       r.rate_usd,
       r.hours * r.rate_usd AS revenue_usd      -- the metric the business wants
FROM silver.rentals r
JOIN gold.dim_customer c ON c.customer_name = r.customer;

-- A business question, answered off gold:
SELECT c.customer_name,
       sum(f.revenue_usd) AS total_revenue
FROM gold.fct_rental f
JOIN gold.dim_customer c USING (customer_key)
GROUP BY 1 ORDER BY 2 DESC;

How medallion maps to modeling — and foreshadows dbt

You didn't learn a new modeling skill today; you learned where the skills you already have belong. The medallion layers are a home for everything from the previous chapters:

The mapping is almost exact: gold is the star schema you built in the modeling chapters, and silver is the cleaning that feeds it. This is not a coincidence — it's why we taught modeling first. When we reach dbt in Chapter 07, you'll see that its conventional project layout is the medallion architecture made concrete: a staging/ folder (≈ silver) and a marts/ folder (= gold), each model in its own file, dependencies wired automatically. You're learning the pattern by hand now so the tool feels obvious later.

✓ Check yourself

• Can you state each layer's one job in a sentence — bronze, silver, gold?
• Why do we keep raw data in bronze even though it's messy? Name two reasons.
• A stakeholder's revenue number looks wrong. What's the layer-by-layer move to find the bug?
• Why does silver stay at the source grain instead of being pre-aggregated?

BRONZE  (capture faithfully, no changes)
  - Load gpu_usage.csv exactly as received, all columns as text.
  - Append, don't overwrite; stamp a loaded_at for audit.
  - Keep the duplicates and the "$2.50" string AS-IS. Don't fix anything.

SILVER  (correctness: type, clean, dedup, conform)
  - Parse "$2.50" -> DECIMAL price (strip the $, cast).
  - Lowercase/trim region; standardize the status values.
  - Deduplicate retry rows (row_number over a key, keep latest).
  - Cast the hourly timestamp to a real TIMESTAMP; drop/flag unparseable rows.
  - Result: one clean row per usage record, same grain as the source.

GOLD  (business question: marts & metrics)
  - Build dim_gpu (one row per model) and a fact at the usage grain.
  - Compute revenue = hours * price as an additive measure.
  - Aggregate revenue by gpu_model and month for the dashboard.

Next

You can organize transformations into layers — but we hand-typed the raw data into bronze. Real pipelines have to get the data in reliably from live sources, which is its own craft. → Ingestion & ELT/ETL