Your data pipeline is a crime scene. Raw Salesforce exports live in the same schema as the revenue figures your CFO uses for board meetings. Someone cleaned a customer table six months ago, but nobody documented what they changed or why. A new analyst joined last month and has already created three different versions of "active customers" because they couldn't find the authoritative one. Sound familiar?
This is the problem Medallion Architecture solves — not by adding complexity, but by giving data a clear, opinionated path from raw to refined. The concept is deceptively simple: you organize your data into three concentric layers (Bronze, Silver, and Gold), each with a specific contract about what it contains and what guarantees it makes. What makes it powerful is the discipline behind those layers and the tooling that enforces them.
By the end of this lesson, you'll be able to design and implement a production-grade Medallion Architecture using the tools most common in the modern data stack — primarily dbt for transformation logic, with Delta Lake and cloud warehouse concepts woven in where they matter most. You'll understand why each layer exists, not just what goes in it.
What you'll learn:
You should be comfortable with SQL and have working knowledge of at least one transformation tool (dbt, Spark SQL, or plain SQL in a cloud warehouse). Familiarity with the basics of data modeling — the difference between a fact and a dimension, what an SCD is — will help in the Gold layer sections. You don't need to be running Delta Lake specifically; the concepts apply to Snowflake, BigQuery, Databricks, and Redshift, with tool-specific notes called out where it matters.
Before we go deep on each layer, let's establish the contract each one holds.
Bronze is your historical archive. It contains data exactly as it arrived from the source — no transformations, no cleaning, no business logic. If Salesforce sent you a null phone number, Bronze stores a null phone number. If your IoT sensors sent a temperature reading of 9999 degrees, Bronze stores 9999. The only things Bronze adds are metadata: when the record arrived, which pipeline loaded it, and usually a unique load ID. Bronze answers the question: what did we receive, and when?
Silver is your cleaned, conformed, and integrated layer. Records here have been deduplicated, fields have been cast to appropriate types, obvious errors have been filtered or flagged, and data from different sources has been joined where it makes sense. A customer record in Silver has a consistent customer_id regardless of whether it came from Salesforce, your billing system, or a CSV upload. Silver answers the question: what do we know, with confidence?
Gold is your business-ready layer. These are the aggregations, metrics, and dimensional models your analysts, dashboards, and data products actually consume. Revenue by product by month. Customer lifetime value. The daily active user count. Gold answers the question: what does it mean for the business?
The power of this design is in the separation. If your revenue calculation is wrong, you know the bug is in Gold. If a source system changed its schema, the problem is contained to Bronze-to-Silver. You can reprocess any layer without touching the others because each layer has a clear input contract.
The most important rule of Bronze is one that practitioners routinely violate: you never transform data in Bronze. The temptation is real. The source sends you a column called Cust_ID and you want to rename it customer_id right away. Resist this. The moment you transform in Bronze, you lose your authoritative record of what the source actually sent you. When there's a discrepancy between your data and the source system six months from now, you'll have no ground truth to debug against.
Let's work with a realistic scenario. You're ingesting order data from a Shopify store into Snowflake via Fivetran (or a custom Python loader — the principle is the same).
Your raw Shopify orders table in Bronze might look like this:
-- Database: raw_db
-- Schema: shopify_bronze
CREATE TABLE shopify_bronze.orders (
-- Source columns, exactly as delivered
id VARCHAR, -- Shopify calls it 'id', not 'order_id'
email VARCHAR,
financial_status VARCHAR,
fulfillment_status VARCHAR,
total_price VARCHAR, -- Shopify sends this as a string
created_at VARCHAR, -- ISO 8601 string, not a timestamp
updated_at VARCHAR,
line_items VARIANT, -- Raw JSON blob
shipping_address VARIANT, -- Raw JSON blob
-- Metadata added by your ingestion layer
_loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
_source_file VARCHAR, -- If loading from S3/GCS
_load_id VARCHAR, -- Unique ID per ingestion run
_is_deleted BOOLEAN DEFAULT FALSE -- For CDC scenarios
);
Notice that total_price is VARCHAR, not NUMERIC. That's intentional — Shopify actually does deliver this as a string. If you cast it at load time and the source ever sends "N/A" instead of a number (it happens), your load fails and you've lost data. Bronze accepts everything.
For most production sources, you're not doing full reloads every run. You're appending new and changed records. In Delta Lake (Databricks), this looks like a merge:
from delta.tables import DeltaTable
from pyspark.sql import functions as F
def load_bronze_orders(spark, source_df, bronze_table_path):
"""
Upsert Shopify orders into Bronze layer.
Source records are trusted as-is — no transformations.
"""
# Add ingestion metadata before writing
source_df = source_df.withColumns({
"_loaded_at": F.current_timestamp(),
"_load_id": F.lit(load_run_id), # passed in from your orchestrator
"_is_deleted": F.lit(False)
})
if DeltaTable.isDeltaTable(spark, bronze_table_path):
delta_table = DeltaTable.forPath(spark, bronze_table_path)
delta_table.alias("bronze").merge(
source_df.alias("source"),
"bronze.id = source.id"
).whenMatchedUpdateAll(
).whenNotMatchedInsertAll(
).execute()
else:
# First load — create the table
source_df.write.format("delta").save(bronze_table_path)
In Snowflake without Delta Lake, you achieve the same effect with a MERGE statement, or simply append all records and let Silver handle deduplication (which is a perfectly valid pattern — Bronze can be append-only).
Key decision point: Should Bronze be append-only or upsert-based? Append-only is simpler and preserves the full history of every version of every record. Upsert-based (merge) is more storage-efficient. For most teams starting out, append-only Bronze with a
_loaded_attimestamp is the right call. You can always deduplicate in Silver.
If you're using a managed tool like Fivetran, Airbyte, or Stitch to load raw data, your Bronze layer already exists — it's whatever schema those tools write to. In dbt, you expose those tables as sources, not models:
# models/sources.yml
version: 2
sources:
- name: shopify
database: raw_db
schema: shopify_bronze
description: "Raw Shopify data loaded by Fivetran. Do not modify."
tables:
- name: orders
description: "Raw order records. Append-only, includes all versions."
columns:
- name: id
description: "Shopify order ID. Not guaranteed unique per row (upserts)."
- name: _loaded_at
description: "Timestamp when Fivetran loaded this record."
# Freshness check — alert if no new data in 6 hours
freshness:
warn_after: {count: 6, period: hour}
error_after: {count: 24, period: hour}
loaded_at_field: _loaded_at
If you're building Bronze yourself (custom ingestion), you'd create dbt models in a models/bronze/ directory. But for many teams, Bronze is the ingestion tool's output domain and dbt starts at Silver.
Silver is where your data engineering craft lives. The Bronze-to-Silver transformation has three responsibilities:
Let's walk through each.
If Bronze is append-only, the same Shopify order might appear a dozen times — once when it was placed, again when payment was confirmed, again when it shipped. Silver selects the latest:
-- models/silver/stg_shopify__orders.sql
WITH source AS (
SELECT * FROM {{ source('shopify', 'orders') }}
),
-- Select the most recent version of each order
deduped AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY _loaded_at DESC
) AS row_num
FROM source
),
latest AS (
SELECT * FROM deduped WHERE row_num = 1
),
Warning: ROW_NUMBER with
_loaded_at DESCworks well for most cases, but watch for ties — two records loaded in the same timestamp batch. If your source has a nativeupdated_atfield, preferORDER BY updated_at DESC, _loaded_at DESCto use the source's own ordering before falling back to load time.
Continuing from the CTE above:
cleaned AS (
SELECT
-- Cast and rename identifiers
id::INTEGER AS order_id,
NULLIF(TRIM(email), '') AS customer_email,
-- Cast financial fields
TRY_CAST(total_price AS NUMERIC(10,2)) AS order_total_usd,
-- Parse timestamps
TRY_TO_TIMESTAMP(created_at) AS order_created_at,
TRY_TO_TIMESTAMP(updated_at) AS order_updated_at,
-- Normalize categoricals
LOWER(TRIM(financial_status)) AS financial_status,
LOWER(TRIM(fulfillment_status)) AS fulfillment_status,
-- Extract from JSON (Snowflake syntax)
shipping_address:city::VARCHAR AS shipping_city,
shipping_address:country_code::VARCHAR AS shipping_country_code,
-- Derive a boolean from status
financial_status = 'paid' AS is_paid,
-- Preserve metadata
_loaded_at,
_load_id
FROM latest
),
-- Separate records with data quality issues
validated AS (
SELECT
*,
CASE
WHEN order_total_usd IS NULL THEN 'invalid_total_price'
WHEN customer_email IS NULL THEN 'missing_email'
WHEN order_created_at IS NULL THEN 'invalid_created_at'
ELSE NULL
END AS _quality_issue
FROM cleaned
)
SELECT * FROM validated
Notice the use of TRY_CAST and TRY_TO_TIMESTAMP instead of hard casts. Hard casts fail the entire model run when a single record has a bad value. TRY_ functions return NULL on failure, which you can track in the _quality_issue column and investigate separately — without losing the rest of your data.
Rather than dropping bad records, route them to a quarantine table. This lets you investigate issues without losing data, and re-process them once the source fixes the problem:
-- models/silver/stg_shopify__orders_quarantine.sql
SELECT *
FROM {{ ref('stg_shopify__orders') }}
WHERE _quality_issue IS NOT NULL
-- models/silver/stg_shopify__orders.sql (final SELECT)
-- Replace the final SELECT with filtered version
SELECT * FROM validated
WHERE _quality_issue IS NULL
Now you have two Silver tables: clean records go downstream, quarantined records go to an investigation schema. You can set up a dbt test that alerts when quarantine grows beyond a threshold.
The hardest Silver problem isn't cleaning — it's identity resolution. You have a customer named "Acme Corp" in Salesforce (with sf_account_id = 001Xx000003GGk2), but in your billing system they're billing_customer_id = CUST-4421. These are the same company. Silver is where you create a unified customer_id that spans both systems.
For simpler cases, a deterministic join works:
-- models/silver/dim_customers.sql
WITH sf_accounts AS (
SELECT
account_id AS sf_account_id,
LOWER(TRIM(name)) AS account_name_normalized,
billing_email
FROM {{ ref('stg_salesforce__accounts') }}
),
billing_customers AS (
SELECT
customer_id AS billing_customer_id,
LOWER(TRIM(company_name)) AS account_name_normalized,
primary_email
FROM {{ ref('stg_billing__customers') }}
),
unified AS (
SELECT
-- Generate a stable surrogate key
{{ dbt_utils.generate_surrogate_key(['sf.sf_account_id']) }} AS customer_id,
sf.sf_account_id,
bc.billing_customer_id,
sf.account_name_normalized AS customer_name,
COALESCE(sf.billing_email, bc.primary_email) AS primary_email
FROM sf_accounts sf
LEFT JOIN billing_customers bc
ON sf.billing_email = bc.primary_email -- email as join key
OR sf.account_name_normalized = bc.account_name_normalized -- fallback
)
SELECT * FROM unified
For complex cases (fuzzy matching, probabilistic entity resolution), you'd use a dedicated tool or ML model — but the output still lands in Silver as a resolved identity table.
Your Silver models live in models/staging/ (using dbt's recommended convention) or models/silver/ if you prefer explicit naming. In dbt_project.yml:
models:
your_project:
silver:
+schema: silver
+materialized: incremental
+on_schema_change: append_new_columns
bronze:
+schema: bronze
+materialized: view # or ephemeral if Bronze is external sources
For Silver models that support incremental loading, add an incremental filter:
-- models/silver/stg_shopify__orders.sql (with incremental support)
{{ config(
materialized='incremental',
unique_key='order_id',
on_schema_change='append_new_columns'
) }}
WITH source AS (
SELECT * FROM {{ source('shopify', 'orders') }}
{% if is_incremental() %}
-- Only process records newer than our last load
WHERE _loaded_at > (SELECT MAX(_loaded_at) FROM {{ this }})
{% endif %}
),
-- ... rest of the transformation
Tip: The
on_schema_change='append_new_columns'config tells dbt to automatically add new columns to your table if the source adds them, instead of failing. This is table stakes for production Silver models.
Gold models are built for consumption. They answer specific business questions, and they're designed around how the business thinks — not around how the source systems are structured. A Silver customer record has financial_status. A Gold metric has orders_placed_last_30_days.
Gold is where you implement your Kimball-style fact and dimension tables, or whatever modeling pattern your organization uses. Let's build a fact table for orders:
-- models/gold/fct_orders.sql
{{ config(
materialized='table',
schema='gold'
) }}
WITH orders AS (
SELECT * FROM {{ ref('stg_shopify__orders') }}
WHERE is_paid = TRUE -- Gold focuses on business-relevant records
),
customers AS (
SELECT * FROM {{ ref('dim_customers') }}
),
date_spine AS (
SELECT * FROM {{ ref('dim_dates') }}
),
final AS (
SELECT
-- Surrogate key
{{ dbt_utils.generate_surrogate_key(['o.order_id']) }} AS order_key,
-- Foreign keys to dimensions
c.customer_id,
d.date_key AS order_date_key,
-- Degenerate dimensions (no separate dim table needed)
o.order_id,
o.financial_status,
o.fulfillment_status,
o.shipping_country_code,
-- Facts (additive measures)
o.order_total_usd,
-- Derived facts
CASE
WHEN o.order_total_usd >= 500 THEN 'high_value'
WHEN o.order_total_usd >= 100 THEN 'mid_value'
ELSE 'low_value'
END AS order_tier,
-- Metadata
o.order_created_at,
o._loaded_at AS last_refreshed_at
FROM orders o
LEFT JOIN customers c
ON o.customer_email = c.primary_email
LEFT JOIN date_spine d
ON DATE(o.order_created_at) = d.date_day
)
SELECT * FROM final
Beyond fact tables, Gold includes pre-aggregated tables (often called "marts") that directly answer common business questions:
-- models/gold/mart_revenue_by_country_month.sql
{{ config(
materialized='table',
schema='gold'
) }}
SELECT
d.year_number,
d.month_number,
d.month_name,
f.shipping_country_code,
COUNT(DISTINCT f.order_id) AS total_orders,
COUNT(DISTINCT f.customer_id) AS unique_customers,
SUM(f.order_total_usd) AS gross_revenue_usd,
AVG(f.order_total_usd) AS avg_order_value_usd,
-- Running total within year (window function)
SUM(SUM(f.order_total_usd)) OVER (
PARTITION BY d.year_number, f.shipping_country_code
ORDER BY d.month_number
) AS ytd_revenue_usd
FROM {{ ref('fct_orders') }} f
JOIN {{ ref('dim_dates') }} d ON f.order_date_key = d.date_key
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 4
This model doesn't need to be fast at query time — it's already pre-aggregated. Your Tableau, Looker, or Metabase dashboard points at this table and gets sub-second performance because the work was done at build time.
Gold tables are almost always materialized as tables, not views. The reason is performance: your dashboards run against Gold dozens or hundreds of times per day, and you don't want that compute cost replicated on every dashboard refresh. Build the table nightly (or on whatever schedule the business needs), and let the BI tool read from a static snapshot.
# dbt_project.yml
models:
your_project:
gold:
+schema: gold
+materialized: table
+post-hook: "GRANT SELECT ON {{ this }} TO ROLE analyst_role"
The post-hook grants read access to analysts automatically after each build — a small detail that matters a lot in production.
If you're on Databricks, everything above still applies, but Delta Lake adds capabilities that reshape some decisions.
Because Delta Lake maintains a transaction log, every version of your Bronze table is queryable:
# Querying Bronze as it existed 7 days ago
df = spark.read.format("delta") \
.option("timestampAsOf", "2024-01-15") \
.load("/mnt/bronze/shopify/orders")
This makes the "Bronze as archive" rule even more powerful — you can literally replay any historical state. It also means you don't need to implement your own soft-delete pattern for CDC; Delta handles it.
Delta Lake handles schema evolution explicitly. When a source adds a new column:
spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", "true")
df_with_new_column.write \
.format("delta") \
.option("mergeSchema", "true") \
.mode("append") \
.save("/mnt/bronze/shopify/orders")
New columns appear in Bronze automatically. Silver models that don't reference the new column are unaffected. This is one of the most practically useful features for teams dealing with APIs and source systems that evolve without warning.
In Silver, if you frequently query by order_created_at or shipping_country_code, Z-ordering co-locates related data on disk and dramatically speeds up filtered queries:
# Run after a Silver layer write
from delta.tables import DeltaTable
delta_table = DeltaTable.forPath(spark, "/mnt/silver/orders")
delta_table.optimize().executeZOrderBy("order_created_at", "shipping_country_code")
Z-ordering is roughly equivalent to clustering in Snowflake or partitioning in BigQuery — pick the columns you filter on most, not every column.
Here's how all of this fits together in a dbt project:
your_dbt_project/
├── models/
│ ├── sources.yml # Bronze sources (Fivetran outputs)
│ ├── bronze/ # If you build Bronze in dbt
│ │ └── raw_shopify__orders.sql
│ ├── silver/
│ │ ├── _silver_models.yml # Tests and docs for Silver
│ │ ├── stg_shopify__orders.sql
│ │ ├── stg_shopify__orders_quarantine.sql
│ │ ├── stg_salesforce__accounts.sql
│ │ ├── stg_billing__customers.sql
│ │ └── dim_customers.sql
│ └── gold/
│ ├── _gold_models.yml # Tests and docs for Gold
│ ├── fct_orders.sql
│ ├── dim_dates.sql
│ └── mart_revenue_by_country_month.sql
├── tests/
│ └── assert_silver_no_negative_revenue.sql
├── macros/
│ └── generate_schema_name.sql # Custom schema naming
└── dbt_project.yml
The _silver_models.yml file is where your data contracts live as tests:
# models/silver/_silver_models.yml
version: 2
models:
- name: stg_shopify__orders
description: "Cleaned, deduplicated Shopify orders. One row per order."
columns:
- name: order_id
description: "Unique integer order ID from Shopify."
tests:
- unique
- not_null
- name: order_total_usd
description: "Order total in USD. Null records are in quarantine table."
tests:
- not_null
- dbt_expectations.expect_column_values_to_be_between:
min_value: 0
max_value: 100000
- name: financial_status
tests:
- accepted_values:
values: ['paid', 'pending', 'refunded', 'voided', 'partially_refunded']
These tests run as part of dbt test and catch regressions before they reach Gold.
Build a three-layer pipeline for a fictional e-commerce company, PetSupplyHub, that ingests from two sources: Shopify (orders) and a PostgreSQL CRM (customer profiles).
Step 1 — Define your Bronze sources.
Create a sources.yml that registers two source tables: shopify.orders and crm.customer_profiles. Add freshness checks to both (warn after 12 hours, error after 48 hours).
Step 2 — Build your Silver staging models.
Create stg_shopify__orders.sql that:
ROW_NUMBER() on id, ordered by _loaded_at DESCtotal_price from VARCHAR to NUMERIC using TRY_CAST_quality_issue column{{ config(materialized='incremental', unique_key='order_id') }}Create stg_crm__customers.sql that normalizes first_name and last_name (trim and title-case), validates email format with a regex, and flags bad emails.
Step 3 — Build a unified customer dimension in Silver.
Create dim_customers.sql that left-joins stg_crm__customers to stg_shopify__orders on email, generating a surrogate key with dbt_utils.generate_surrogate_key.
Step 4 — Build Gold.
Create fct_orders.sql (materialized as table) with order-level facts joined to your customer dimension. Then create mart_monthly_revenue.sql that aggregates total orders, unique customers, and gross revenue by year and month.
Step 5 — Add tests.
In your _gold_models.yml, add unique and not_null tests to fct_orders.order_key. Add an accepted_values test to financial_status. Run dbt test and fix any failures.
Stretch goal: Add a dbt exposure that documents a "Revenue Dashboard" consuming your mart_monthly_revenue model, with the dashboard URL and owner contact.
Mistake 1: Transforming data in Bronze.
Symptom: You can't reproduce a discrepancy between your data and the source system because Bronze doesn't match what the source actually sent.
Fix: Enforce a team rule that Bronze models reference only source(), never ref(). Code review any Bronze model that contains a CAST, COALESCE, or WHERE clause.
Mistake 2: Materializing Silver as views. Symptom: Dashboard queries time out because every dashboard refresh recalculates your Silver cleaning logic from scratch, including the deduplication window functions. Fix: Materialize at minimum your base Silver models as incremental tables. Views are fine for thin Silver models that don't have window functions.
Mistake 3: Putting business logic in Silver.
Symptom: Your Silver model contains a filter like WHERE financial_status = 'paid' because "we only care about paid orders." Three months later, someone needs to analyze failed payments and discovers Silver has been dropping them.
Fix: Silver keeps all records (or routes bad ones to quarantine). Business filters live in Gold. If you need a "paid orders only" view, it's a Gold model.
Mistake 4: Over-aggregating in Gold.
Symptom: Your Gold layer has a table called agg_daily_revenue with a fixed granularity, and then someone asks for weekly revenue. You have to build another model. And then monthly. And then by-region-daily.
Fix: Build fact tables at the lowest useful grain (order level, event level). Let aggregation models live in Gold alongside the fact table, built from the fact table — not from each other.
Mistake 5: Ignoring schema evolution in Bronze.
Symptom: A source system adds a column, your Bronze load silently drops it (because your INSERT statement enumerates columns), and you don't notice for three months.
Fix: Use SELECT * in Bronze, or use an ingestion tool that handles schema evolution. If building custom loaders, use mergeSchema (Delta) or COPY INTO with a flexible schema.
Mistake 6: Using updated_at from the source as your incremental filter without accounting for late arrivals.
Symptom: Records with updated_at in the past (backdated updates, timezone issues, replication lag) don't get picked up in your incremental runs.
Fix: Use _loaded_at (your ingestion timestamp) as the primary incremental filter on Bronze. In Silver, use a lookback window: WHERE _loaded_at > (SELECT MAX(_loaded_at) FROM {{ this }}) - INTERVAL '2 hours'.
Medallion Architecture isn't a technology — it's a discipline. The Bronze layer gives you an inviolable historical record. Silver gives you a single, trusted representation of reality. Gold gives your business exactly what it needs to make decisions. The architecture works because each layer has a clear contract, and those contracts make debugging, reprocessing, and evolution tractable.
With dbt, the implementation maps cleanly: sources and raw models for Bronze, staging and integration models for Silver, and mart models for Gold. Delta Lake adds time travel and schema evolution that strengthen the Bronze guarantee and simplify incremental loading. Cloud warehouses like Snowflake and BigQuery achieve the same outcomes with their own native features (clustering, zero-copy cloning, streaming inserts).
Where to go next:
constraints feature to enforce column-level rules at write time, not just at test time.The architecture you've built here can handle millions of records per day in production. The next step is making it observable — monitoring each layer's health, alerting on anomalies, and building the operational confidence to know your data is trustworthy before your CFO asks.
Learning Path: Modern Data Stack