Medallion Architecture: Bronze, Silver and Gold Layers

The medallion architecture is a way of organising data in layers of increasing quality: bronze, silver and gold. Databricks, which popularised the term, describes it as a series of data layers that denote the quality of data stored in the lakehouse (Databricks docs: medallion); Microsoft recommends the same pattern for Fabric, with the goal of incrementally improving the structure and quality of data (Microsoft Learn: medallion in Fabric). The names are new; the idea, raw then cleaned then presented, is as old as data warehousing. What matters is being strict about what each layer may contain.

What each layer holds

Bronze is the source of truth for replays: Microsoft's guidance is to store everything exactly as it arrives and preserve the original copies. Silver holds at least one validated, non-aggregated representation of each record. Gold holds refined, usually aggregated data for dashboards, machine learning and applications.

The layers, built on real data

The script builds all three layers, plus a quarantine, in DuckDB from January 2024 New York taxi trips (NYC TLC: trip record data). On a lakehouse the tables would be Delta or Iceberg tables and the SQL would run in Spark or a warehouse engine; the structure is identical.

import urllib.request
from pathlib import Path
import duckdb

base = "https://d37ci6vzurychx.cloudfront.net"
for name, url in {"yellow_tripdata_2024-01.parquet": f"{base}/trip-data/yellow_tripdata_2024-01.parquet",
                  "taxi_zone_lookup.csv": f"{base}/misc/taxi_zone_lookup.csv"}.items():
    if not Path(name).exists():
        urllib.request.urlretrieve(url, name)
con = duckdb.connect()
con.execute("""
-- bronze: exactly what arrived, plus where it came from
CREATE TABLE bronze_trips AS
SELECT *, 'yellow_tripdata_2024-01.parquet' AS _source_file, 'load-2024-03-01' AS _batch
FROM 'yellow_tripdata_2024-01.parquet';
CREATE TABLE bronze_zones AS SELECT * FROM read_csv('taxi_zone_lookup.csv', header = true);

-- one rule per row: the first rule a row breaks, or NULL if it passes
CREATE VIEW checked AS
SELECT *, CASE
    WHEN tpep_pickup_datetime < '2024-01-01' OR tpep_pickup_datetime >= '2024-02-01' THEN 'pickup outside the month'
    WHEN tpep_dropoff_datetime < tpep_pickup_datetime THEN 'dropoff before pickup'
    WHEN fare_amount < 0 THEN 'negative fare'
    WHEN trip_distance = 0 AND fare_amount = 0 THEN 'no distance and no fare'
  END AS broken_rule
FROM bronze_trips;

-- quarantine: rejected rows kept with the reason
CREATE TABLE quarantine_trips AS SELECT * FROM checked WHERE broken_rule IS NOT NULL;

-- silver: validated, typed, named for people, enriched with the zone
CREATE TABLE silver_trips AS
SELECT c.tpep_pickup_datetime AS pickup_at, c.tpep_dropoff_datetime AS dropoff_at,
       z.Borough AS pickup_borough, z.Zone AS pickup_zone,
       CASE c.payment_type WHEN 1 THEN 'Credit card' WHEN 2 THEN 'Cash' WHEN 0 THEN 'Flex fare' ELSE 'Other' END AS payment,
       c.trip_distance AS miles, c.fare_amount AS fare, c.tip_amount AS tip, c._batch
FROM checked c LEFT JOIN bronze_zones z ON z.LocationID = c.PULocationID
WHERE c.broken_rule IS NULL;

-- gold: shaped for one use, a daily dashboard by borough
CREATE TABLE gold_daily_borough AS
SELECT CAST(pickup_at AS DATE) AS day, pickup_borough, COUNT(*) AS trips, ROUND(SUM(fare), 2) AS fares
FROM silver_trips GROUP BY ALL;
""")
n = lambda table: con.sql(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
print(f"bronze {n('bronze_trips')} | quarantine {n('quarantine_trips')} | silver {n('silver_trips')} | gold {n('gold_daily_borough')}")
print("bronze = silver + quarantine:", n("bronze_trips") == n("silver_trips") + n("quarantine_trips"))
for rule, rows in con.sql("SELECT broken_rule, COUNT(*) FROM quarantine_trips GROUP BY 1 ORDER BY 2 DESC").fetchall():
    print(f"  {rule:<26} {rows:>6}")
print(con.sql("""SELECT pickup_borough, CAST(SUM(trips) AS BIGINT) AS trips, ROUND(SUM(fares), 2) AS fares
                 FROM gold_daily_borough GROUP BY 1 ORDER BY trips DESC LIMIT 3""").fetchdf().to_string(index=False))

bronze 2964624 | quarantine 37940 | silver 2926684 | gold 245
bronze = silver + quarantine: True
  negative fare               37447
  no distance and no fare       419
  dropoff before pickup          56
  pickup outside the month       18
pickup_borough   trips       fares
     Manhattan 2616215 39384810.81
        Queens  266616 13861624.65
      Brooklyn   24916   728572.67

What the build shows

Bronze adds only lineage. The raw rows gain two columns, the source file and the load batch, and nothing else changes. If a rule in silver turns out to be wrong, bronze lets you rebuild silver without going back to the source.

Rules live in one place. The checked view assigns each row the first rule it breaks. Silver takes the rows that pass; the quarantine table takes the rest, with the reason attached. Rejecting rows is easy; keeping them with a reason is what lets someone review the decision later. Here 37,940 rows were quarantined, almost all for negative fares, plus 419 with neither distance nor fare, 56 that end before they start and 18 dated outside the month.

The layers reconcile. Bronze equals silver plus quarantine, 2,964,624 = 2,926,684 + 37,940, so no row vanished unaccounted for. This single check catches join fan-outs, accidental filters and dropped batches, and belongs in every run.

Silver is for people, gold is for a purpose. Silver renames cryptic columns, decodes payment types and adds borough and zone names, but keeps one row per trip. Gold collapses nearly three million rows into 245 day-and-borough rows for one dashboard. A different consumer, a fare model or a finance report, gets its own gold table from the same silver.

Design decisions that matter

• Never skip bronze. Databricks advises against writing to silver directly from ingestion, because schema changes and corrupt records then fail the pipeline with nothing saved.
• Keep business logic out of bronze and aggregation out of silver. Once silver is aggregated, detail is gone for every future use.
• Make every layer rebuildable from the one below, with idempotent loads.
• Decide where history lives. Slowly changing dimensions and deduplicated current-state tables are silver concerns; snapshots for reporting are gold.
• Adapt the number of layers. Some teams split silver into cleaned and conformed stages, or add a landing zone before bronze. The discipline matters more than the count of three.

Who uses which layer

Layers are also an access model. Bronze is for the pipelines and the engineers who maintain them; it may hold personal or sensitive data that has not yet been masked, so few people should read it. Silver serves analysts, data scientists and other pipelines that need trustworthy detail. Gold serves business users and BI tools, with names and grains chosen for them. Microsoft's Fabric guidance goes as far as recommending a separate workspace per layer, so permissions and governance can be set layer by layer. Deciding this early prevents the commonest shortcut, a report built straight on bronze because it was there.

Common mistakes

• Gold tables reading from bronze "just this once", which bypasses every rule.
• Silver that is just a copy of bronze with a new name, adding cost and no quality.
• One enormous gold table for everyone, which every team then filters and re-aggregates differently.
• No quarantine, so rejected rows disappear and nobody knows how many.
• Treating layer names as the architecture. Tests, lineage and ownership make the layers trustworthy, not the labels.

How it fits with the rest

Medallion layering is ELT made explicit, as ETL vs ELT describes; table formats such as Delta Lake give each layer transactions and time travel; the checks between layers are covered in data quality testing; and gold is usually modelled as stars, as in data warehouses explained.

Data lakes, data warehousing, data quality and validation, and a data lake implementation project are part of the Data Engineering course.

Sources

• What is the medallion lakehouse architecture? (Databricks documentation) — Databricks
• Implement medallion lakehouse architecture in Fabric (Microsoft Learn) — Microsoft Learn
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission