Medallion Architecture (Bronze, Silver, Gold)
Databricks · Architecture, Performance & Cost
Theory
The medallion architecture organises lakehouse data into layers of increasing quality. Bronze holds raw data as it arrived, usually append-only with load metadata such as file name and ingest time, so you can always replay history. Silver holds cleaned, typed, de-duplicated and conformed data: bad rows filtered or quarantined, keys standardised, and related sources joined, for example orders matched to customers. Gold holds business-ready tables shaped for a use case, such as daily sales by city, customer lifetime value or a star schema for BI. In Unity Catalog, layers are often separate schemas (main.bronze, main.silver, main.gold) or separate catalogs per environment.
Example: CSV order files land in bronze.orders_raw, are cleaned into silver.orders, and summarised into gold.daily_city_sales for the dashboard.
Why we use it
Mixing raw and cleaned data in one place makes errors hard to trace and fix. Layers give each step a clear job, let you rebuild silver and gold from bronze when logic changes, and give consumers stable, trusted gold tables.
When to use it
Use it as the default pattern for lakehouse ETL, implemented with Lakeflow pipelines or jobs. It is a guideline, not a rule: small projects may merge layers, and some teams add a platinum or serving layer. Avoid copying data through layers with no transformation.
How it works
- Raw files and SaaS data arrive
- Ingest as-is into bronze with load metadata
- Clean, type, de-duplicate and join into silver
- Do rows fail quality rules? Yes: Drop or send them to a quarantine table. No: Keep them in silver.
- Aggregate and model into gold for each use case
- Dashboards, Genie and ML read gold
Syntax
Typical layout:
main.bronze.<source>_raw -- raw, append-only (Auto Loader / Lakeflow Connect)
main.silver.<entity> -- cleaned, typed, de-duplicated, joined
main.gold.<business_mart> -- aggregates, star schema, ML features
Grants: engineers on bronze/silver, analysts SELECT on goldExample
-- Bronze: raw files plus metadata
CREATE OR REFRESH STREAMING TABLE main.bronze.orders_raw AS
SELECT *, _metadata.file_name AS src_file, current_timestamp() AS ingested_at
FROM STREAM read_files('/Volumes/main/sales/raw/orders/', format => 'csv');
-- Silver: typed, filtered, de-duplicated
CREATE OR REFRESH MATERIALIZED VIEW main.silver.orders AS
SELECT DISTINCT CAST(order_id AS BIGINT) AS order_id, customer_id, region,
CAST(amount AS DECIMAL(10,2)) AS amount, CAST(order_date AS DATE) AS order_date
FROM main.bronze.orders_raw
WHERE order_id IS NOT NULL;
-- Gold: business-ready aggregate
CREATE OR REFRESH MATERIALIZED VIEW main.gold.daily_city_sales AS
SELECT o.order_date, c.city, SUM(o.amount) AS sales, COUNT(*) AS orders
FROM main.silver.orders o JOIN main.sales.customers c USING (customer_id)
GROUP BY o.order_date, c.city;Key points
- Bronze keeps raw history so you can rebuild downstream tables
- Silver is where data quality rules, types and de-duplication belong
- Gold is modelled for consumers: dashboards, Genie, BI tools, ML
- Map layers to Unity Catalog schemas or catalogs and grant access per layer
- Use streaming tables for bronze and materialized views for most gold
- Do not add layers that only copy data without adding value