dbt snapshot check vs timestamp strategy

TL;DR

Use the timestamp strategy when the source has a trustworthy updated_at column; use the check strategy (comparing all or listed columns) when it does not. Timestamp is cheaper and precise; check catches changes the timestamp misses but costs a full comparison every run.

dbt snapshot check vs timestamp strategy

Use this when

• Setting up a new dbt snapshot
• Check strategy is slow or misses changes
• The source's updated_at column is unreliable

Not for this skill when

• You are writing unit tests
• Seeds infer wrong types
• You are choosing model materializations

Steps

1. The timestamp strategy, the default choice when you can trust the column:

{% snapshot user_snapshot %}
{{
    config(
      target_schema='snapshots',
      strategy='timestamp',
      unique_key [your value]
      updated_at='updated_at',
    )
}}
SELECT * FROM {{ source('raw', 'users') }}
{% endsnapshot %}

Expected output: rows with a newer updated_at than the last snapshot get versioned. Cheap: only changed-timestamp rows are even examined closely.

1. Verify the timestamp column is trustworthy before choosing it:

SELECT count(*) FILTER (WHERE updated_at IS NULL) AS nulls,
       max(updated_at) AS max_ts
FROM raw.users;

Expected output: zero nulls and a recent max. If the source bulk-updates without touching updated_at, or nulls exist, the timestamp strategy silently misses changes.

1. The check strategy, for sources without a reliable timestamp:

{{
    config(
      target_schema='snapshots',
      strategy='check',
      unique_key [your value]
      check_cols='all',
    )
}}

Expected output: every run compares all columns and versions any row that differs. Correct without timestamps, but it reads and hashes the full source every time.

1. Narrow the check to the columns that matter when all is too slow:

check_cols=['name', 'email', 'plan']  -- instead of 'all'

Expected output: faster snapshots that ignore irrelevant churn (like a last_login column that changes constantly). Ignored columns never trigger new versions, which is the point.

1. Read the snapshot output table correctly:

Each versioned row gets dbt_valid_from / dbt_valid_to.
Current rows have dbt_valid_to IS NULL.
Query "as of" a date with: dbt_valid_from [= X AND (dbt_valid_to ] X OR dbt_valid_to IS NULL).

Expected output: correct point-in-time queries. The most common snapshot bug is querying the table without the validity filter and double-counting history.

Variant phrasings

dbt snapshot strategy timestamp vs check

Timestamp when updated_at is trustworthy (cheaper); check when it is not (more thorough). Steps 1-3.

dbt snapshot missing changes

The timestamp column is not updated by the source, or checkcols excludes the changed column. Verify with step 2 or widen checkcols.

dbt snapshot check_cols all slow

List only meaningful columns (step 4). Full-row hashing on wide tables every run is the cost driver.

Why it happens

Snapshots implement type-2 slowly-changing dimensions: each change to a tracked row closes the old version and opens a new one. The strategy only decides how dbt detects "changed": by trusting a timestamp column, or by comparing values. Detection quality bounds everything downstream.

Edge cases

• Hard deletes in the source: snapshots record them via dbt_valid_to only if the row is still visible; true deletes need the invalidate_hard_deletes config.
• Schema changes in the source break snapshots; add new columns to check_cols deliberately.
• Snapshot runs are not incremental in the dbt sense; they scan the source each run, so schedule them sensibly.
• Multiple snapshots of the same source with different strategies double the source reads; consolidate where possible.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_HjY1jIKiX4rSgZh1etFNcQ