Medallion Architecture Explained (Bronze, Silver, Gold)

Bronze: raw, as-is

Bronze captures data exactly as it arrived from the source — same structure, same values, same problems. No filtering, no deduplication, no type correction. Its entire purpose is to be a faithful, reprocessable copy of history:

CREATE TABLE bronze.orders AS
SELECT *, CURRENT_TIMESTAMP() AS _ingested_at, 'orders_api_v2' AS _source
FROM raw_orders_landing;

-- Bronze may legitimately contain duplicates, nulls in required-looking fields,
-- and inconsistent formats — none of that gets fixed at this layer

Adding metadata columns like _ingested_at and _source is standard practice here — they cost nothing and become essential later for lineage and debugging a bad batch.

Silver: cleaned and conformed

CREATE TABLE silver.orders AS
WITH deduped AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _ingested_at DESC) AS rn
  FROM bronze.orders
  WHERE order_id IS NOT NULL AND order_amount >= 0
)
SELECT order_id, TRIM(customer_email) AS customer_email,
       CAST(order_amount AS DECIMAL(12,2)) AS order_amount,
       CAST(order_timestamp AS TIMESTAMP) AS order_timestamp
FROM deduped
WHERE rn = 1;

This is where data quality actually happens: type casting, deduplication, dropping records that fail basic validation, and standardizing column names and formats across potentially multiple source systems that describe the same real-world entity differently.

Gold: business-ready

CREATE TABLE gold.daily_revenue_by_region AS
SELECT
  DATE_TRUNC('day', o.order_timestamp) AS order_date,
  c.region,
  COUNT(DISTINCT o.order_id) AS orders,
  SUM(o.order_amount)          AS revenue
FROM silver.orders o
JOIN silver.customers c ON o.customer_email = c.email
GROUP BY 1, 2;

Gold tables are shaped around a specific reporting need rather than a specific source system — this table exists to answer "what's daily revenue by region" quickly, not to be a general-purpose copy of the orders table. Multiple Gold tables commonly serve different dashboards from the same Silver layer underneath.

Medallion vs traditional staging layers

The underlying idea — never let raw and business-ready data live in the same table — predates the term "medallion architecture" by decades. What's genuinely new is running the whole pipeline on lakehouse storage with one open table format end to end, rather than moving data between a separate staging database and a separate warehouse product.

When it's worth the complexity

Three layers add real overhead — more tables, more jobs, more places for a pipeline to fail. It earns that overhead when raw history needs to be reprocessable (a bug found in Silver logic shouldn't require re-extracting from the source system), when multiple Gold tables serve different consumers from one shared Silver layer, or when auditability requires tracing a Gold-layer number back to its original raw record. A small, single-source, single-consumer pipeline can often collapse Bronze and Silver into one step without losing much.

Common mistakes

• Letting business users or BI tools query Bronze directly, exposing duplicates and malformed records that were never meant to be consumer-facing.
• Doing real data quality work in Gold instead of Silver, forcing every downstream Gold table to repeat the same cleaning logic.
• Building Gold tables that are just full copies of Silver instead of aggregated, purpose-built tables — missing the actual point of the layer.
• Skipping metadata columns in Bronze (ingestion timestamp, source system), then having no way to debug which batch introduced a bad record later.