Medallion Architecture

3 copies of the same data

A pipeline that reads a source system and writes a report in a single step has nowhere to stand when a rule changes. Suppose the report counted refunds as revenue for 6 months. Fixing it means pulling the source again, and a payments API may keep only 30 days of events, so the older months are gone for good. A layered design keeps 3 copies of the data, each with a stated promise about what it holds and who may change it.

The names describe how far the data has travelled from the source. Bronze is evidence. Whatever arrived stays there even if it's malformed, so it can be read again next year. Silver is the version you would hand to an analyst. Gold is built for one audience, with the arithmetic done so 20 dashboards don't each redo it.

What a bronze row holds

A bronze table adds columns about the delivery rather than the business. _ingested_at records when the row landed, _source_file records which file or API page it came from, and payload is the record as a single string. Nothing is parsed yet, because parsing is where mistakes happen, and bronze exists so mistakes can be undone.

Row 1 and row 3 are the same event, delivered twice because the API retried a page. The country arrives as fr on one row and DE on another, and the amount is a string. All 3 problems are left in place. The silver build is where they get fixed, and it reads bronze every time it runs:

INSERT INTO silver_payments
SELECT
  event_id,
  customer_id,
  amount_cents,
  country,
  paid_at
FROM (
  SELECT
    json_extract_scalar(payload, '$.id') AS event_id,
    json_extract_scalar(payload, '$.customer') AS customer_id,
    CAST(json_extract_scalar(payload, '$.amount_cents') AS BIGINT) AS amount_cents,
    upper(json_extract_scalar(payload, '$.country')) AS country,
    from_iso8601_timestamp(json_extract_scalar(payload, '$.paid_at')) AS paid_at,
    row_number() OVER (
      PARTITION BY json_extract_scalar(payload, '$.id')
      ORDER BY _ingested_at DESC
    ) AS rn
  FROM bronze_payments
  WHERE load_date = DATE '2026-10-20'
) AS parsed
WHERE rn = 1

The inner query parses each field out of the JSON string, casts the amount to a whole number of cents, upper-cases the country, and numbers each event's copies from newest to oldest. The outer query keeps copy number 1, so ev_91 lands in silver once. If a later audit finds that countries should have been mapped through a lookup table, the fix is a change to this query and a rerun over bronze. The API is never called again.

Gold answers one question

Gold tables are shaped by their reader. The finance dashboard asks for revenue by country by day, so that is the table, with one row per country per day and no column it doesn't need:

INSERT INTO gold_daily_revenue
SELECT
  CAST(paid_at AS DATE) AS revenue_date,
  country,
  SUM(amount_cents) AS revenue_cents,
  COUNT(*) AS payments
FROM silver_payments
WHERE CAST(paid_at AS DATE) = DATE '2026-10-20'
GROUP BY 1, 2

A bug in parsing is fixed in silver, and every gold table inherits the fix on its next run. A new question gets a new gold table built from silver, with no new parsing code. A rebuild costs a read of bronze, which sits on your own storage, rather than a request to a source that may say no.

A finance analyst finds that gold_daily_revenue has counted refunds, which arrive as events with a negative amount_cents, as revenue for the last 90 days. Which change fixes both the past and the future?

• Patch the 90 affected rows in gold_daily_revenue by hand and add a filter on amount_cents to the gold query.
• Delete the refund rows from bronze_payments so that no downstream table can ever see them again.
• Add an event_type column in the silver build, filter the gold query to payments, and rerun silver then gold for the 90 days from bronze.
• Ask the payments team to resend the 90 days of events with the refunds removed from the feed.

About this lesson

This section is part of the Design Patterns lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.