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

Answer from memory before opening each one.

1. The dashboard shows acme at 25.00 instead of 37.50, with a second customer called Acme at 12.50. You query silver.rentals and find both spellings there too; bronze.rentals has them exactly as the source sent them. Which layer holds the bug, and which two are doing their job?

   Show answer

   Silver: the conforming step (lower(trim(customer))) is missing or broken, so one customer survives as two. Gold is fine — it faithfully summed what silver gave it — and bronze is fine, because capturing the source's inconsistent casing untouched is its job. Walking backward from gold until the data first looks right is the everyday payoff of the layers. See Why layering beats one giant transform.

2. The source keeps sending 'US ' with a trailing space. A teammate adds a trim() to the landing step: "it's obviously wrong, and it saves silver the work." What does that small fix cost?

   Show answer

   Bronze stops being proof of what the source actually sent, and the fix becomes a transformation with no audit trail and no home — nobody looking for cleaning rules will look in the loader. Bronze's only job is to capture faithfully, so that silver and gold can always be rebuilt from it; any correction, however obvious, belongs in silver where it can be read and re-run. See Bronze.

3. In the silver step, replace try_cast(started_at AS DATE) with a plain CAST and re-run it. What happens — and why did the same bad value cause no trouble at all when it landed in bronze?

   Show answer

   The whole statement fails with a conversion error on 'not-a-date', so silver isn't built. Bronze was unaffected because it is schema-on-read: every column is VARCHAR, so a malformed value can never block ingestion. try_cast turns the bad value into NULL, and the started_at IS NOT NULL filter then makes the decision about that row explicit — in silver, where you can see it. See schema-on-read and step 2.

4. To speed up the revenue dashboard, an engineer proposes building silver.rentals as "revenue per customer per month". Next quarter the business asks for a GPU-utilization mart and a finance reconciliation table. What has the proposal cost, and what rule of thumb would have prevented it?

   Show answer

   Both new marts need per-rental rows, which that silver table no longer has, so each would have to re-clean from bronze — the copy-pasted cleaning logic the layers exist to avoid. Silver stays at the source grain (one clean row per rental) so that one silver table can feed many gold marts. The rule: if a transformation is about correctness it is silver; if it is about a business question — and a monthly revenue rollup is — it is gold. See Silver and Gold.

5. In the hands-on, rental 5 (12 hours at $2.50) is dropped in silver because its date won't parse, so $30 never reaches gold. The chapter says failing rows can be dropped or quarantined. What decides between them, and what makes either choice recoverable?

   Show answer

   Dropping keeps silver simple and trustworthy, and is fine when the failures are genuine junk. Quarantining — writing failures to a side table, or flagging them — wins when the rows carry real money or when the number of failures is itself a signal that the source has changed, because a silent drop quietly understates revenue. Either way the row is still in bronze, so once the rule or the source is fixed you can rebuild silver and get it back. See Silver and why you keep raw.

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.

Takeaway

• Bronze keeps the source exactly as received, append-only and untouched, because it is the only layer you can rebuild everything else from.
• A fix that is about correctness (types, casing, duplicates, invalid rows) belongs in silver, and a rule about a business question (revenue per region) belongs in gold.
• Silver keeps the source's grain, one clean table per entity, so one silver table can feed many gold marts without re-cleaning.
• When a gold number is wrong, walk the layers backward: if silver is already wrong the bug is in cleaning, and if bronze is wrong the source sent bad data and you have proof.
• The layers map onto dbt as sources, staging models and marts, so the pattern you hand-built is the project layout you will use next.

Bronze was hand-typed here, and a real pipeline has to get data in reliably from live sources, so the next chapter covers ingestion. → Ingestion & ELT/ETL

Further reading

• What is a medallion architecture? — Databricks. The vendor definition of the three layers and their guarantees.
• Medallion architecture — Databricks docs, accessed 2026. What each layer holds and how data is validated as it moves up.
• How we structure our dbt projects — dbt Labs docs, accessed 2026. The staging, intermediate and marts layout that this chapter's layers become.