The Medallion Architecture

The medallion architecture is three tables with three jobs. It is not a Databricks feature — nothing enforces it — but it is the convention most lakehouse projects converge on, and the reasoning behind it is worth more than the vocabulary.

The property that makes it work: each layer can be rebuilt from the one before it. Drop gold and rebuild from silver. Drop silver and rebuild from bronze. Only bronze needs the source files, and only bronze is irreplaceable.

Bronze: lose nothing

(spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "csv")
    .option("cloudFiles.schemaLocation", "/Volumes/bookshop/raw/_schemas/orders")
    .option("cloudFiles.schemaHints", "order_id bigint, amount decimal(10,2)")
    .option("header", "true")
    .load("/Volumes/bookshop/raw/landing/")
    .selectExpr("*", "current_timestamp() as _ingested_at", "_metadata.file_path as _source_file")
 .writeStream
    .option("checkpointLocation", "/Volumes/bookshop/raw/_checkpoints/bronze_orders")
    .trigger(availableNow=True)
    .toTable("bookshop.bronze.orders"))

{"batchId": 0, "numInputRows": 13932, "numFilesProcessed": 3}

select order_id, status, amount, _rescued_data is not null as has_problem
from bookshop.bronze.orders
order by order_id limit 4;

order_id  status     amount  has_problem
--------  ---------  ------  -----------
    1001  completed   25.50  false
    1002  COMPLETED   12.00  false
    1003  returned    40.00  false
    1412  completed    NULL  true

Two things bronze deliberately keeps. Row 1002’s COMPLETED is inconsistently cased — not bronze’s problem. Row 1412 failed to parse — recorded, not discarded.

No transformation, no filtering, no deduplication. The temptation to “just lowercase the status here” is the one to resist: the moment bronze transforms, it stops being a faithful record of what arrived, and a bug in that transformation is unrecoverable.

Silver: make it trustworthy

create or replace table bookshop.silver.orders as
with deduplicated as (
    select *,
           row_number() over (partition by order_id order by _ingested_at desc) as rn
    from bookshop.bronze.orders
    where _rescued_data is null          -- bad rows handled separately
      and order_id is not null
)
select
    order_id,
    customer_id,
    ordered_at,
    lower(trim(status))              as status,
    amount,
    amount >= 25                     as is_large_order,
    _ingested_at
from deduplicated
where rn = 1
  and status is not null;

num_affected_rows  num_inserted_rows
-----------------  -----------------
             9788               9788

select status, count(*) as n from bookshop.silver.orders group by 1 order by n desc;

status     n
---------  ----
completed  7204
returned   1588
refunded    996

COMPLETED and completed are now one value. 13,932 bronze rows became 9,788 silver rows — the difference is duplicates from overlapping file loads, rescued rows, and nulls, and it is worth reconciling that number rather than accepting it.

The deduplication window is the piece people leave out. Files often overlap, and row_number() over the ingestion timestamp keeps the latest version of each order.

Quarantine rather than drop:

create or replace table bookshop.silver.orders_quarantine as
select order_id, _rescued_data, _source_file, _ingested_at
from bookshop.bronze.orders
where _rescued_data is not null or order_id is null;

num_affected_rows  num_inserted_rows
-----------------  -----------------
              412                412

select
    get_json_object(_rescued_data, '$.amount') as bad_amount,
    count(*) as n
from bookshop.silver.orders_quarantine
group by 1 order by n desc limit 3;

bad_amount   n
-----------  ---
n/a          281
             98
unknown       33

Now the data-quality problem has a size and a shape, and someone can go to the source team with “281 rows a day arrive with n/a in amount” instead of a vague complaint.

Gold: answer questions

create or replace table bookshop.gold.customer_orders as
select
    c.customer_id,
    c.full_name,
    c.country_code,
    count(o.order_id)                                   as order_count,
    coalesce(sum(case when o.status = 'completed' then o.amount end), 0) as lifetime_value,
    min(o.ordered_at)                                   as first_order_at,
    max(o.ordered_at)                                   as last_order_at
from bookshop.silver.customers c
left join bookshop.silver.orders o using (customer_id)
group by 1, 2, 3;

num_affected_rows  num_inserted_rows
-----------------  -----------------
             4120               4120

create or replace table bookshop.gold.daily_revenue as
select
    o.ordered_at,
    c.country_code,
    count(*)        as orders,
    sum(o.amount)   as revenue,
    sum(case when o.is_large_order then 1 else 0 end) as large_orders
from bookshop.silver.orders o
join bookshop.silver.customers c using (customer_id)
where o.status = 'completed'
group by 1, 2;

num_affected_rows  num_inserted_rows
-----------------  -----------------
              184                184

select * from bookshop.gold.daily_revenue order by ordered_at limit 4;

ordered_at  country_code  orders  revenue  large_orders
----------  ------------  ------  -------  ------------
2026-01-04  GB                42   1088.40           18
2026-01-04  US                31    802.11           14
2026-01-05  GB                38    944.02           16
2026-01-05  US                29    714.88           11

Gold reads silver, never bronze. That rule is what stops the lower(trim(status)) logic being reimplemented in six dashboards — and reimplemented slightly differently in one of them.

The layer boundaries in practice

Bronze                          Silver                        Gold
─────────────────────────────   ───────────────────────────   ─────────────────────────
append raw rows                 dedup on business key         join across entities
add _ingested_at, _source_file  cast and normalise types       aggregate
keep _rescued_data              trim, lowercase, standardise   apply business definitions
never filter                    filter genuinely invalid       shape for consumption
never join                      light lookups only             one table per question

Common mistakes, in order of how often they cause trouble:

1. Transforming in bronze. Now a bad transformation is permanent.
2. Aggregating in silver. Gold then cannot answer a question at a different grain.
3. Reading bronze from gold. The cleaning is bypassed and nobody notices until numbers disagree between two dashboards.
4. Dropping bad rows at ingestion. The bug becomes undiscoverable.

Rebuilding

drop table bookshop.gold.customer_orders;
drop table bookshop.gold.daily_revenue;
-- rerun the gold cell

num_affected_rows  num_inserted_rows
-----------------  -----------------
             4120               4120

Gold rebuilt from silver in seconds, with no reference to any source file. Test this deliberately — a gold table that cannot be rebuilt has accumulated state it should not have, usually a manual fix someone applied directly.

When it is too much

Three layers on a project with one clean source and two dashboards is ceremony. Bronze plus gold is a legitimate architecture when the source is already conformed. Equally, a large domain often needs a fourth layer — feature tables for ML, or export-shaped tables for reverse ETL — and adding it is not a violation of anything.

Keep the rule, negotiate the layer count: every table is rebuildable from the layer below, and nothing skips a layer.

Practice

select
    (select count(*) from bookshop.bronze.orders)            as bronze,
    (select count(*) from bookshop.silver.orders)            as silver,
    (select count(*) from bookshop.silver.orders_quarantine) as quarantined;

bronze  silver  quarantined
------  ------  -----------
 13932    9788          412

-- bronze
status
---------
completed
COMPLETED
Completed
returned

-- silver
status
---------
completed
returned

num_inserted_rows
-----------------
             4120

select get_json_object(_rescued_data, '$.amount') as bad_value, count(*) as n
from bookshop.silver.orders_quarantine
group by 1 order by n desc;

bad_value    n
-----------  ---
n/a          281
             98
unknown       33

Next: declarative pipelines — the same three layers with dependencies and quality rules managed for you.