dbt Packages & Tests

Snapshots for SCD type 2

YAML snapshots in dbt 1.9+, a successful first snapshot, and why unique_key quality decides whether dbt_scd_id helps you.

The problem

A status column is overwritten in the source as a customer or order moves through a lifecycle. Incremental merge will also overwrite it (official docs describe merge as SCD1-like). If you need to know that a row was active last week and closed now, official documentation says to use snapshots, which implement type-2 slowly changing dimensions.

A working example

dbt v1.9+ configures snapshots in YAML (official docs; the older {% snapshot %} SQL form still exists in many tutorials). This project used:

# snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: ref('stg_customers')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at

Official required pieces: a strategy of timestamp or check, a unique_key, and either updated_at (timestamp strategy) or check_cols (check strategy). Official docs also say you cannot co-locate snapshots and models in the same directory.

dbt snapshot with dbt-core 1.12.7 / dbt-duckdb 1.11.1: OK. The resulting table contained one current row per customer, including official-style meta columns. For customer 1 (ada@example.com, active), dbt_valid_to was NULL (the current version). A dbt_scd_id hash was present on each row.

A second snapshot was not re-run after a source change in this build, so this page does not claim a before/after of dbt_valid_to. Official docs illustrate the idea with an order that moves from pending to shipped: the old row gets a dbt_valid_to, and a new row is inserted with dbt_valid_to null.

hard_deletes (ignore, invalidate, new_record) is an official config for rows that disappear from the source. It was not exercised here.

The gotcha

unique_key must actually be unique in the snapshot query. If two source rows share a key, snapshot merge has the same class of problem as incremental merge: one target row cannot honestly represent two sources. People then see surprising dbt_scd_id duplicates and blame the hash. Official docs have a related debugging note: if a table was accidentally materialized as a table instead of a snapshot (possible in very old versions), it will be missing dbt_scd_id / dbt_valid_from / dbt_valid_to, and later dbt snapshot will error with “Snapshot target is not a snapshot table.” Their documented recovery is to drop the fake snapshot and start over.

timestamp strategy only records a change when updated_at moves. If the source overwrites status and does not bump updated_at, the snapshot will not notice. That is when official check strategy (and check_cols) exists.

Snapshots are not a backup tool and not a cheap incremental fact table. Official docs also point to a “incremental models or snapshots” guide when you are choosing.

When not to use this

Verification

The YAML snapshot ran successfully once on dbt-core 1.12.7 / dbt-duckdb 1.11.1 and produced current rows with a dbt_scd_id and a null dbt_valid_to. Strategy rules, directory constraints, hard_deletes, and the “target is not a snapshot table” recovery note are from official dbt documentation. A change-detection (second) snapshot run was not executed in this build.

Official docs