The Medallion Architecture

The one-liner

How it works — click a layer

▸ click each medal to see what it holds

How it works — deep dive

1 · Bronze — raw, append-only ingest

Mechanism: land data as-ingested with minimal transformation, permissive fields, and provenance columns; Auto Loader (cloudFiles) is the typical incremental ingester. Why: a replayable source of truth. Trade-off: don't clean here — it defeats reprocessing/audit.

# Bronze: incremental append ingest with provenance (Auto Loader).
(spark.readStream.format("cloudFiles")
   .option("cloudFiles.format", "json")
   .option("cloudFiles.schemaLocation", "/Volumes/main/raw/_schema")
   .load("/Volumes/main/raw/orders")
   .selectExpr("*", "_metadata.file_path AS src_file", "current_timestamp() AS ingest_ts")
   .writeStream.option("checkpointLocation", "/Volumes/main/raw/_ckpt")
   .toTable("main.bronze.orders"))     # append-only, raw

2 · Silver — cleaned, conformed, deduped, joined

Mechanism: cleanse, cast, dedupe, enforce schema, join — typically with MERGE so re-runs are idempotent; keep a non-aggregated record. Trade-off: don't aggregate here — that belongs in gold so many marts reuse one silver.

-- Silver: idempotent upsert of cleaned, deduped records into a conformed table.
MERGE INTO main.silver.orders t
USING (
  SELECT DISTINCT order_id, region, CAST(amount AS DECIMAL(10,2)) AS amount, order_ts
  FROM main.bronze.orders WHERE order_id IS NOT NULL    -- clean + dedupe
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

3 · Gold — business-level aggregates & marts

Mechanism: aggregate and model for the business (KPIs, star schemas); many gold tables read one silver; gold is what BI & ML consume. Trade-off: purpose-built per use case — expect several gold tables off one silver.

-- Gold: a business-ready aggregate the dashboard/model reads directly.
CREATE OR REPLACE TABLE main.gold.daily_revenue_by_region AS
SELECT region, date(order_ts) AS day, sum(amount) AS revenue, count(*) AS orders
FROM main.silver.orders
GROUP BY region, date(order_ts);

4 · Why layering — refinement, reprocessing, quality gates

Mechanism: each hop is a separation of concerns with its own bar; quality gates (e.g. SDP expectations) drop/quarantine bad rows between layers. Trade-off: medallion is a pattern, not a feature — you implement the gates; more layers = more storage/latency.

# Quality gate between bronze→silver in a Lakeflow SDP pipeline (expectations):
from pyspark import pipelines as dp

@dp.table()
@dp.expect_or_drop("valid_amount", "amount > 0")   # drop bad rows at the gate
def silver_orders():
    return spark.readStream.table("main.bronze.orders")

5 · How it maps to the lakehouse flow

Mechanism: every layer is a Delta table in the UC namespace; Auto Loader feeds bronze, SDP/jobs build silver→gold, gold serves BI/ML — one copy, one governance model. Trade-off: not every feed needs all three hops — names are a convention.

-- The three layers are just governed Delta tables in the UC 3-level namespace:
--   main.bronze.orders   (raw, append)     ← Auto Loader
--   main.silver.orders   (clean, deduped)  ← MERGE / SDP + expectations
--   main.gold.daily_revenue_by_region      ← aggregate, serves BI + ML
SELECT * FROM main.gold.daily_revenue_by_region;   -- what the dashboard queries

Reprocessing — change logic, rebuild from bronze (no re-pull)

▸ click to see what gets rebuilt when silver logic changes

Why it matters

• Debuggable: when gold looks wrong, trace back silver → bronze to find the break.
• Reprocessing: bronze keeps raw data, so you can rebuild silver/gold with new logic without re-pulling the source.
• Right data, right user: engineers in bronze/silver, analysts & execs in gold.

Uses, edge cases & limitations

• Uses: the default ETL structure on the lakehouse; pairs with Auto Loader (bronze ingest) and Lakeflow Declarative Pipelines (silver/gold).
• Edge case: not every pipeline needs all three layers — a simple feed may go bronze→gold; big orgs may add sub-layers. Names are a convention.
• Limitation: it's an organizing pattern, not a feature — it doesn't enforce quality itself; you still implement validation (e.g. pipeline Expectations). More layers = more storage & latency.

Common gotchas

• ❌ Aggregating in silver. Keep silver non-aggregated; aggregates belong in gold so many use cases reuse silver.
• ❌ Over-cleaning bronze. It should stay raw/auditable — heavy transforms there defeat reprocessing.
• ❌ Treating the layer names as mandatory — they're a quality convention, adapt to freshness & cost.
• ❌ Expecting the pattern to enforce quality — you implement the gates (expectations, constraints).