Medallion architecture

Overview

Medallion architecture, popularized by Databricks, structures a lakehouse into three layers of tables that data flows through, each adding more cleaning, structure, or business logic than the last:

• Bronze — raw data landed as-is from source systems, preserving the original format and history for traceability and reprocessing.
• Silver — cleaned, deduplicated, and conformed data: types are fixed, invalid records are filtered or flagged, and tables are joined into a more usable structure.
• Gold — business-level, aggregated tables tailored to specific use cases — dashboards, reports, or machine learning features — often the layer that BI tools query directly.

Why layer the pipeline

Separating these stages makes each transformation step independently testable and re-runnable. If a bug is found in a Gold aggregation, Silver data doesn't need to be reprocessed from scratch; if source data was malformed, Bronze retains the original so nothing is lost. It also gives different consumers appropriate points of access — engineers might work close to Bronze/Silver, while analysts and BI tools consume Gold.

Relationship to table formats

Medallion architecture is a modeling pattern, not a technology — it's commonly implemented with open table formats like Apache Iceberg, Delta Lake, or DuckLake providing the underlying transactional tables at each layer, and orchestration tools like dbt or Airflow managing the transformations between layers.

Implementing medallion layers with DuckDB and MotherDuck

Each layer can be modeled as its own schema or database, materialized with SQL:

Copy code
-- Bronze: raw ingested data
CREATE SCHEMA IF NOT EXISTS bronze;
CREATE TABLE bronze.raw_orders AS
SELECT * FROM read_json('s3://landing/orders/*.json');

-- Silver: cleaned and typed
CREATE TABLE silver.orders AS
SELECT order_id, CAST(order_date AS DATE) AS order_date, amount
FROM bronze.raw_orders
WHERE amount IS NOT NULL;

-- Gold: business aggregate
CREATE TABLE gold.daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM silver.orders
GROUP BY ALL;

Tools like dbt are often layered on top to manage these transformations as a DAG of models against a MotherDuck database.