Medallion architecture (bronze, silver, gold) explained with a runnable example

A payments team has a problem that is more common than any tool choice. Files arrive from 3 sources. Some rows are repeated, some have an amount that is not a number, and the finance dashboard is built straight on top of the raw files. Every time a source changes its format, the dashboard breaks, and nobody can say which rows were left out of last week's total.

The medallion architecture is a way to organise that work in stages, so each stage has one job and each table says how far its data has been cleaned. It is a pattern, not a product. Databricks popularised the bronze, silver and gold names, and the same layering is used on Snowflake, Microsoft Fabric and plain SQL engines. The examples here run in DuckDB, and the output under each query is what it printed.

The raw data: what lands in bronze

Bronze is the landing zone. The rule is to keep the data as it arrived, with nothing fixed, and to add a little metadata about where it came from. Every column here is text, because bronze does not decide what a value means. The 9 rows came from 3 files, and some of them are wrong on purpose.

CREATE TABLE bronze_payments (
  payment_id  VARCHAR,
  user_name   VARCHAR,
  amount      VARCHAR,
  status      VARCHAR,
  paid_at     VARCHAR,
  source_file VARCHAR
);

INSERT INTO bronze_payments VALUES
  ('P1', 'asha',  '1200', 'SUCCESS', '2026-03-01 09:00:00', 'file_a'),
  ('P2', 'ravi',  '800',  'success', '2026-03-01 10:15:00', 'file_a'),
  ('P2', 'ravi',  '800',  'success', '2026-03-01 10:15:00', 'file_b'),
  ('P3', 'meena', '450',  'FAILED',  '2026-03-02 11:00:00', 'file_b'),
  ('P4', 'kiran', 'abc',  'SUCCESS', '2026-03-02 12:30:00', 'file_b'),
  ('P5', 'asha',  '1500', 'SUCCESS', '2026-03-02 18:45:00', 'file_c'),
  ('P6', 'ravi',  NULL,   'SUCCESS', '2026-03-03 08:00:00', 'file_c'),
  ('P7', 'meena', '700',  'success', '2026-03-03 14:20:00', 'file_c'),
  ('P8', 'kiran', '300',  'SUCCESS', 'not a date',          'file_c');

SELECT * FROM bronze_payments ORDER BY payment_id, source_file;

payment_id | user_name | amount | status  | paid_at             | source_file
-----------+-----------+--------+---------+---------------------+------------
P1         | asha      | 1200   | SUCCESS | 2026-03-01 09:00:00 | file_a
P2         | ravi      | 800    | success | 2026-03-01 10:15:00 | file_a
P2         | ravi      | 800    | success | 2026-03-01 10:15:00 | file_b
P3         | meena     | 450    | FAILED  | 2026-03-02 11:00:00 | file_b
P4         | kiran     | abc    | SUCCESS | 2026-03-02 12:30:00 | file_b
P5         | asha      | 1500   | SUCCESS | 2026-03-02 18:45:00 | file_c
P6         | ravi      | NULL   | SUCCESS | 2026-03-03 08:00:00 | file_c
P7         | meena     | 700    | success | 2026-03-03 14:20:00 | file_c
P8         | kiran     | 300    | SUCCESS | not a date          | file_c
(9 rows)

Read it like a data engineer. P2 arrived twice, in file_a and file_b. P4 has an amount of abc, P6 has no amount, and P8 has a timestamp that is not a date. The status is written two ways, SUCCESS and success. None of this is fixed in bronze. If a rule later turns out to be wrong, this table is the untouched original you rebuild from.

1What goes wrong if you skip the layers?

Suppose the dashboard reads bronze directly and filters successful payments with status = 'SUCCESS':

SELECT COUNT(*) AS successful_payments
FROM bronze_payments
WHERE status = 'SUCCESS';

successful_payments
-------------------
5
(1 row)

Five, which looks plausible. It is the wrong five. It counts P4, P6 and P8, whose amount or timestamp is broken, and it misses P2 and P7, which are successful payments written in lower case. The dashboard is wrong in both directions and nothing failed. That one quiet error is the argument for putting a cleaning stage between the raw data and the people who read it.

2Silver: clean, type, deduplicate, and set the bad rows aside

Silver is where the rules live. It reads bronze and produces one trusted table with proper types, normalised values and no duplicates. Two decisions matter.

First, a row that cannot be cleaned is not deleted. It goes to a quarantine table with the reason, so somebody can look at it and fix the source. Dropping it silently is how a total ends up wrong with no trail.

Second, the casts must be the safe kind. TRY_CAST returns NULL when a value cannot be converted, where plain CAST would stop the whole job on the first bad row. Snowflake has TRY_CAST too, BigQuery calls it SAFE_CAST, and Spark SQL has try_cast.

CREATE TABLE typed AS
SELECT payment_id,
       LOWER(TRIM(user_name))        AS user_name,
       TRY_CAST(amount AS INTEGER)   AS amount,
       LOWER(TRIM(status))           AS status,
       TRY_CAST(paid_at AS TIMESTAMP) AS paid_at,
       source_file
FROM bronze_payments;

CREATE TABLE quarantine_payments AS
SELECT payment_id, source_file,
       CASE WHEN amount IS NULL  THEN 'amount missing or not a number'
            WHEN paid_at IS NULL THEN 'paid_at is not a valid timestamp'
       END AS reason
FROM typed
WHERE amount IS NULL OR paid_at IS NULL;

CREATE TABLE silver_payments AS
SELECT payment_id, user_name, amount, status, paid_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY source_file) AS rn
  FROM typed
  WHERE amount IS NOT NULL AND paid_at IS NOT NULL
) deduped
WHERE rn = 1;

The first statement types and normalises everything. The second collects the rows that failed a cast, with a reason. The third keeps the good rows and removes duplicates with ROW_NUMBER, keeping the copy from the earliest file. Here is each result:

SELECT * FROM silver_payments ORDER BY payment_id;

payment_id | user_name | amount | status  | paid_at
-----------+-----------+--------+---------+--------------------
P1         | asha      | 1200   | success | 2026-03-01 09:00:00
P2         | ravi      | 800    | success | 2026-03-01 10:15:00
P3         | meena     | 450    | failed  | 2026-03-02 11:00:00
P5         | asha      | 1500   | success | 2026-03-02 18:45:00
P7         | meena     | 700    | success | 2026-03-03 14:20:00
(5 rows)

SELECT * FROM quarantine_payments ORDER BY payment_id;

payment_id | source_file | reason
-----------+-------------+---------------------------------
P4         | file_b      | amount missing or not a number
P6         | file_c      | amount missing or not a number
P8         | file_c      | paid_at is not a valid timestamp
(3 rows)

Silver has five payments with real types and one spelling of each status. 3 rows sit in quarantine with a reason each. And P2 appears once. The failed payment P3 is in silver on purpose: a failed payment is a valid fact, and a business rule about what counts as revenue belongs in the next layer.

3Do the row counts add up?

The most useful check in a medallion pipeline is also the simplest. Every bronze row must end up somewhere: kept in silver, removed as a duplicate, or quarantined. If the numbers do not add up, rows have gone missing.

SELECT (SELECT COUNT(*) FROM bronze_payments)                         AS bronze_rows,
       (SELECT COUNT(*) FROM silver_payments)                         AS silver_rows,
       (SELECT COUNT(*) FROM typed) - (SELECT COUNT(*) FROM quarantine_payments)
         - (SELECT COUNT(*) FROM silver_payments)                     AS duplicates_removed,
       (SELECT COUNT(*) FROM quarantine_payments)                     AS quarantined;

bronze_rows | silver_rows | duplicates_removed | quarantined
------------+-------------+--------------------+------------
9           | 5           | 1                  | 3
(1 row)

9 rows in, split into 5 + 1 + 3. Run this check after every load and fail the pipeline when it does not balance. A quiet mismatch is how data goes missing for weeks without anyone noticing.

4Gold: business-ready tables built from silver

Gold answers a business question and is shaped for the people who ask it. Here, finance wants revenue per day from successful payments. The rule "revenue is successful payments only" lives here, in one place, so every dashboard agrees on it.

CREATE TABLE gold_daily_revenue AS
SELECT CAST(paid_at AS DATE) AS pay_date,
       COUNT(*)              AS successful_payments,
       SUM(amount)           AS revenue
FROM silver_payments
WHERE status = 'success'
GROUP BY CAST(paid_at AS DATE);

SELECT * FROM gold_daily_revenue ORDER BY pay_date;

pay_date   | successful_payments | revenue
-----------+---------------------+--------
2026-03-01 | 2                   | 2000
2026-03-02 | 1                   | 1500
2026-03-03 | 1                   | 700
(3 rows)

Revenue is 2000 on 1 March, 1500 on 2 March and 700 on 3 March. The failed P3 is not counted, and the lower-case P2 and P7 are, which fixes the section 1 problem once, upstream, for every reader. A second gold table for a different audience, say revenue per user, would also be built from silver, never from bronze.

5What if a rule turns out to be wrong?

This is the payoff of keeping bronze untouched. Suppose finance decides next week that payments over 1000 need a manual review and must not count until approved. That is a change to a gold rule. You edit the gold query and rebuild the table from silver. If the change is to a silver rule, such as accepting a second timestamp format, you edit silver and rebuild it from bronze. In both cases the original data is still there.

If bronze had been cleaned on the way in, the original abc and the duplicate would be gone, and a rule change would mean going back to the source systems, if they still have the data.

6The 3 layers side by side

The names bronze, silver and gold are a convention. Some teams add a fourth layer or call the stages raw, staging and mart. The idea that matters is that each stage does one kind of work, and data flows one way.

7What interviewers ask next

Build the quarantine and the quality gate yourself in Bronze to silver with a quarantine and Gold revenue behind a quality gate. The Data Quality track covers the checks that sit between the layers, and the warehouse questions cover the theory.