How do dbt snapshots keep history? The orders table in the source database overwrites status in place, and the business wants to know how long orders spent in each status. Which strategy would you use, and what can go wrong?

A snapshot turns a table that is overwritten in place into a type 2 slowly changing dimension: each time the snapshot runs, dbt compares the source rows with the latest stored version of each key, closes a changed version by setting dbt_valid_to, and adds the new one with dbt_valid_from. The current version has dbt_valid_to null. Use the timestamp strategy on updated_at when the source keeps it reliably; use the check strategy on chosen columns when it does not. What can go wrong: a snapshot only sees the state at the moments it runs, so 2 changes between runs show as one, and deletes are ignored unless hard_deletes is set.

1. The source and the snapshot

The source keeps only the latest status:

The snapshot keeps each version:

Time in each status is dbt_valid_to - dbt_valid_from, with now in place of null for the current one.

2. The configuration (dbt 1.9 and later, in YAML)

# snapshots/orders_snapshot.yml
snapshots:
  - name: orders_snapshot
    relation: source('shop', 'orders')
    config:
      unique_key: id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

With the check strategy instead:

      strategy: check
      check_cols: [status, amount]

3. Choosing the strategy

The check strategy compares columns on every run, which costs more on wide tables.

4. What can go wrong

• Changes between runs are lost. A snapshot taken once a day sees an order go from pending straight to shipped if it was packed for 3 hours. Run it more often, or capture changes from the database log (change data capture) when every step matters.
• Deletes. By default a deleted source row stays current forever. hard_deletes: invalidate closes it; new_record adds a row marking the delete.
• A unique key that is not unique creates bad versions. Test it.
• Snapshots are not rebuilt. History exists only from the first run on, and a dropped snapshot table cannot be regenerated, so treat it as data to protect, not a model to rebuild.

What interviewers want: snapshots as SCD type 2 with dbt_valid_from and dbt_valid_to, timestamp versus check, changes between runs being invisible, hard_deletes, testing the unique key, and snapshots as history that cannot be rebuilt.