Snapshots for SCD type 2
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
- You only need the current value. Use a table or a
mergeincremental model. - The source already provides valid-from / valid-to history. Model that history; do not snapshot on top without a reason.
- You cannot name a unique key.
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.