Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 41 ยท Sources, Seeds and Snapshots

Snapshot Strategies, Hard Deletes and SCD2

Compare dbt snapshot strategies timestamp and check, handle deleted source rows with hard_deletes, join facts to snapshot history, and learn slowly changing dimension types.

The previous lesson showed what a snapshot records. This one is about how dbt decides a row changed, what to do when rows disappear, and how to use the history in analysis. Choosing the wrong strategy is the most common reason for snapshots that miss changes or record phantom ones.

Strategy 1: timestamp

YAML
snapshots:
  - name: customers_snapshot
    relation: source('shop', 'customers')
    config:
      unique_key: id
      strategy: timestamp
      updated_at: updated_at

With timestamp, dbt compares the updated_at column of the source with the one stored in the snapshot. If the source value is newer, the row changed and a new version is recorded. It is cheap (one column compared) and reliable if the source maintains updated_at correctly. Many applications do, through framework timestamps or database triggers.

Pitfalls:

  • A source that updates a column without touching updated_at is invisible to dbt.
  • A column that is not a true "last modified" time (for example created_at) never changes, so no change is ever detected.
  • Updates in the same second may collapse if the timestamp is too coarse.

Strategy 2: check

When the source has no trustworthy timestamp, use check. dbt compares the values of the columns you list and records a new version when any of them differ.

YAML
snapshots:
  - name: products_snapshot
    relation: source('shop', 'products')
    config:
      unique_key: id
      strategy: check
      check_cols: [name, price, category_id]

You can also write check_cols: all to watch every column. That is convenient but fragile: adding a column to the source later counts as a change for every row, and noisy columns (like last_seen_at) create a new version on every run. Prefer an explicit list of the columns whose history you care about.

In 1.9 and later, with check you can set updated_at too, to control which timestamp lands in dbt_valid_from; otherwise dbt uses the time the snapshot ran.

This stand-in uses SQL to show the logic of both strategies on one changed row (not dbt). The timestamp condition notices nothing here because updated_at was not touched, while check sees the price difference:

SQL Why timestamp can miss a change ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

With timestamp, the price change is missed (0 changed rows); check finds it (1). The lesson: choose timestamp only when you know the column is dependable.

Which strategy?

timestamp Trusted updated_at column; cheapest
check No timestamp; compares chosen columns

When in doubt and the table is small, use check with explicit columns.

1 / 2

Hard deletes

What if a row disappears from the source? By default, dbt snapshots ignore deletions: the last version stays current forever, as though the row still exists. In dbt 1.9+, the hard_deletes setting controls this:

Value Behavior
ignore Default. Deleted rows stay as current versions
invalidate Closes the version, setting dbt_valid_to when the row vanishes
new_record Closes the old version and adds a new row flagged as deleted (dbt_is_deleted)
YAML
config:
  unique_key: id
  strategy: timestamp
  updated_at: updated_at
  hard_deletes: new_record

new_record is the most informative: your history shows when the row existed, and a final row says "deleted from this moment", which makes point-in-time queries correct. Older dbt versions used invalidate_hard_deletes: true, equivalent to invalidate.

Using snapshot history in analysis

The real value comes from joining facts to the version that was valid at the time of the event:

SQL Join an order to the customer's city at order time ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The February order maps to Toronto and the April order to Lyon. A join to the live customers table would have reported Lyon for both, rewriting history.

Slowly changing dimension types

Snapshots implement type 2. The types differ in what happens when an attribute changes:

Type Behavior Keeps history?
0 Never changes after the first value n/a
1 Overwrite the old value No
2 Add a new row with validity dates Yes, full
3 Add a column for the previous value Only one step

A normal dbt model that selects from the source is type 1 (always the latest). A snapshot is type 2. Choose per attribute: a customer's email probably needs only type 1, while their sales region might need type 2 for correct regional reports.

Snapshot meta fields

dbt 1.9 lets you rename the meta columns (for example to valid_from) through snapshot_meta_column_names:

YAML
config:
  snapshot_meta_column_names:
    dbt_valid_from: valid_from
    dbt_valid_to: valid_to

A pattern: snapshot, then a clean model on top

Snapshot tables are raw history. Teams usually add a staging model that renames columns and fills the open-ended valid_to with a far-future date, making range joins simpler:

SQL
select
    id as customer_id,
    city,
    dbt_valid_from as valid_from,
    coalesce(dbt_valid_to, '9999-12-31') as valid_to,
    dbt_valid_to is null as is_current
from {{ ref('customers_snapshot') }}

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A source table has no reliable updated_at column. Which strategy fits?

    The check strategy compares the values of listed columns instead of relying on a timestamp.
  2. 2. What does hard_deletes: new_record do when a source row is deleted?

    new_record closes the last version and inserts a new row flagged as deleted so history shows the deletion.
  3. 3. Which SCD type keeps full history by adding rows?

    Type 2 adds a new row with validity dates for each change.
  4. 4. Why join facts to the snapshot with a date range instead of the live table?

    A range join picks the version that was valid at the time of the event, preserving historical correctness.

Summary

  • timestamp compares an updated_at column (cheap, needs a trustworthy column); check compares chosen columns' values.
  • By default deleted source rows stay current; use hard_deletes: invalidate or new_record to record deletions.
  • Join facts to snapshot versions with event_date >= valid_from AND (event_date < valid_to OR valid_to IS NULL).
  • Snapshots implement SCD type 2; ordinary models are type 1.

Next: Jinja Basics for dbt