How to Implement dbt SCD2 Snapshots (Slowly Changing Dimensions)

What SCD2 means and why it matters

A Slowly Changing Dimension Type 2 (SCD2) preserves the full history of a row by keeping every version rather than overwriting in place. When alice@old.com changes to alice@new.com, you end up with two rows:

dbt_valid_to = NULL means "current." Without SCD2, historical reports retroactively pick up today's dimension values - every time a customer changes their email, your old revenue reports silently show the new address.

Create the snapshot block

Snapshots live in dbt_project/snapshots/. Create customers_snapshot.sql:

{% snapshot customers_snapshot %}

  {{ config(
      target_schema='dbt_demo',
      unique_key='id',
      strategy='check',
      check_cols=['name', 'email']
  ) }}

  SELECT id, name, email
  FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

Key settings: - target_schema - the schema dbt writes the snapshot table into - unique_key - the natural key used to match existing rows - strategy: check - dbt compares the listed check_cols on every run; a change in any of them closes the old row and inserts a new one - strategy: timestamp - the alternative when you have a reliable updated_at column; cheaper than comparing N columns

Run the snapshot across a simulated change

cd dbt_project

# First run: captures all rows with dbt_valid_to = NULL
dbt snapshot --profiles-dir ..

# Simulate a dimension change
psql -h localhost -U postgres -d app -c \
  "UPDATE customers SET email='alice@new-domain.com' WHERE id=1"

# Second run: dbt detects the change, closes the old row, inserts the new version
dbt snapshot --profiles-dir ..

# Inspect the result
psql -h localhost -U postgres -d app -c \
  "SELECT id, email, dbt_valid_from, dbt_valid_to
   FROM dbt_demo.customers_snapshot
   ORDER BY id, dbt_valid_from"

After the second run you see two rows for id=1 - one with dbt_valid_to set (closed, historical) and one with dbt_valid_to = NULL (current).

The four metadata columns dbt adds

dbt appends these to every snapshot table automatically:

• dbt_scd_id - unique surrogate key for each version row
• dbt_updated_at - when this version was captured
• dbt_valid_from - start of the validity window
• dbt_valid_to - end of the validity window (NULL = still current)

Production patterns

• Schedule snapshots hourly or daily - dbt snapshot is idempotent; run it from your orchestrator (Airflow, dbt Cloud, Dagster) on a tight cadence for dimensions that change frequently.
• Prefer timestamp strategy when you have a trustworthy updated_at column - it only reads the changed rows rather than comparing every column for every row.
• Pair with CDC (Change Data Capture via Debezium or Fivetran) for sub-minute change capture when hourly snapshots are too slow.
• Downstream joins - join fact tables to the snapshot using WHERE fact.event_at BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, NOW()) to get the dimension value that was current at the time of the event.