How to create a snapshot (SCD type 2) in dbt

Many source tables keep only the current state: when a customer moves to another country, the old value is gone. A snapshot records every version of each row, with the period in which it was valid. That is a slowly changing dimension of type 2, and it lets you answer questions such as “which country was this customer in when they ordered?”.

Copy and paste

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

dbt snapshot                            # all snapshots
dbt snapshot -s customers_snapshot      # one snapshot

Without a reliable updated_at column, compare the values instead:

    config:
      unique_key: customer_id
      strategy: check
      check_cols: [name, country]

Read the current version of each row:

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

What the snapshot stores

Customer 11 moved from Germany to France on 20 May, and customer 12 was deleted from the source. After three snapshot runs:

customer_id	country	dbt_valid_from	dbt_valid_to
10	IT	2026-01-01 00:00
11	DE	2026-01-02 00:00	2026-05-20 08:00
11	FR	2026-05-20 08:00
12	IT	2026-01-03 00:00	time of the run that saw the delete

The current version of each row has dbt_valid_to empty. dbt also adds dbt_scd_id, a unique ID for each version, and dbt_updated_at.

The strategies

timestamp compares updated_at with the last version stored. It is fast and precise, but only as good as the column: a source that changes rows without updating it loses history.
check compares the columns in check_cols, or every column with check_cols: all. It needs no timestamp, but it is slower on wide tables.

Hard deletes

hard_deletes decides what happens when a row disappears from the source:

ignore, the default, keeps the last version open as if the row still existed;
invalidate closes it, setting dbt_valid_to to the time of the snapshot;
new_record closes it and adds a row that marks the deletion.

Things to know

A snapshot sees only what exists when it runs. If a value changes twice between two runs, the middle version is lost. Run snapshots at least as often as the source changes, often before the models, with dbt snapshot or as part of dbt build.
History cannot be rebuilt. Never drop a snapshot table to start over: the old versions are not in the source any more.
Schema names. schema: snapshots goes through generate_schema_name, so in development it may become dbt_nicola_snapshots; see how to change the target schema.
Older projects. Snapshots written as SQL files with a {% snapshot %} block still work. The YAML form above is the one introduced in dbt 1.9.

Tested with dbt Core 1.12.