Home / Data Platform / dbt & Quality

Data PlatformHow-toIntermediate

dbt snapshots for slowly changing dimensions (SCD type 2) - setup, strategies and fixing common mistakes

dbt snapshots record how source rows change over time as SCD type 2 history. Pick the timestamp or check strategy, set a stable unique key, handle hard deletes, and avoid the mistakes that corrupt history.

Deepak Bhardwaj4 min read

Applies todbt Core and dbt Cloud 1.8+ (YAML snapshot configuration from 1.9); Snowflake, Databricks, BigQuery, Postgres and other adapters

What a snapshot does

Each time you run dbt snapshot, dbt compares the current source rows with the snapshot table:

  • New rows are inserted with dbt_valid_from set and dbt_valid_to NULL.
  • Changed rows get the old version closed (dbt_valid_to set) and a new version inserted.
  • Unchanged rows are left alone.

The result is full history of a mutable source table, such as customers, products or account status.

Warning

Snapshot history can't be rebuilt from the source. If the snapshot table is dropped or corrupted, the history is gone. Back it up, and never run it with --full-refresh thinking it behaves like a model.

1. Configure a snapshot (dbt 1.9+ YAML)

# snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: source('crm', 'customers')
    config:
      schema: snapshots
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

Older versions use a SQL file with a {% snapshot %} block:

{% snapshot customers_snapshot %}
{{ config(target_schema='snapshots', unique_key='customer_id',
          strategy='timestamp', updated_at='updated_at', invalidate_hard_deletes=True) }}
select * from {{ source('crm', 'customers') }}
{% endsnapshot %}

Run it:

dbt snapshot --select customers_snapshot

2. Choose the strategy

Strategy Use when Watch out
timestamp Source has a reliable updated_at that changes on every update If some updates don't touch updated_at, those changes are missed
check No reliable timestamp Set check_cols to the columns you care about; all compares every column and costs more 
config:
  strategy: check
  check_cols: [email, segment, status]

3. Unique key must be truly unique

If two source rows share the unique_key, the snapshot produces duplicate current rows or fails during the merge. Test the source first:

sources:
  - name: crm
    tables:
      - name: customers
        columns:
          - name: customer_id
            tests: [unique, not_null]

For composite keys, build a surrogate in the snapshot select, or pass a list of columns to unique_key where your dbt version supports it.

4. Hard deletes

By default, rows deleted from the source stay "current" in the snapshot for ever. Options (dbt 1.9+):

hard_deletes Behaviour
ignore (default) Deleted rows stay open
invalidate Closes the current version (dbt_valid_to set)
new_record Inserts a new row flagged as deleted (dbt_is_deleted)

5. Using the history

Current state:

select * from {{ ref('customers_snapshot') }} where dbt_valid_to is null

As at a date:

select * from {{ ref('customers_snapshot') }}
where dbt_valid_from <= '2026-06-30'
  and (dbt_valid_to > '2026-06-30' or dbt_valid_to is null)

Build a dimension model on top of the snapshot that renames the columns to valid_from / valid_to / is_current, rather than having reports query the snapshot directly.

dbt 1.9 also lets you set dbt_valid_to_current (for example '9999-12-31') so current rows get a far-future date instead of NULL.

6. Schedule

  • Run snapshots before models in each job: dbt snapshot && dbt build.
  • Run them often enough to catch changes. A change that happens and reverts between two runs is never recorded.
  • If the source is CDC or append-only with full history already, you may not need a snapshot. Build SCD2 from the change log instead.

Common problems

Problem Cause Fix
Duplicate current rows Non-unique key in source Fix the key, deduplicate in the snapshot select, then repair the table
Every run creates new versions for all rows check strategy with columns that change every time (load timestamps), or a non-deterministic select Exclude volatile columns from check_cols
Changes not captured updated_at not maintained by the source Use check strategy
Snapshot table wiped Someone dropped it or ran a destructive operation Restore from warehouse time travel or backup
Type changes in source break the run Column type changed Alter the snapshot table column, then rerun

Fix a duplicate current row directly in the warehouse (example for one key), after taking a backup:

create table snapshots.customers_snapshot_bak as select * from snapshots.customers_snapshot;

update snapshots.customers_snapshot
set dbt_valid_to = current_timestamp
where customer_id = 1042 and dbt_valid_to is null
  and dbt_scd_id <> (select max(dbt_scd_id) from snapshots.customers_snapshot
                     where customer_id = 1042 and dbt_valid_to is null);

Checklist

  • Strategy matches how the source records changes
  • Unique and not-null tests on the source key
  • Hard-delete behaviour chosen
  • Snapshots run before models, frequently enough
  • Snapshot tables backed up or covered by time travel
  • Dimension model built on top for reporting

Related fixes