dbt snapshot failed on unique key check_strategy

TL;DR

The check strategy needs a reliable unique_key and a populated updated_at column to detect changes. Nulls or duplicates in the unique key, or a missing updated_at, break the snapshot. Validate the source columns, fix the config, and rerun dbt snapshot.

Error

dbt snapshot failed on unique key check_strategy

Steps

1. Open the snapshot file and confirm the strategy block: strategy='check', unique_key, check_cols or updated_at. Expected: you see the full strategy configuration.
2. Query the source for nulls in the unique key column. Expected: zero nulls; any null breaks the check logic.
3. Query the source for duplicate unique key values. Expected: each key appears once; duplicates confuse change detection.
4. Confirm the updated_at column exists and is populated on every row (the check strategy needs it). Expected: no null timestamps.
5. Fix the config or the source query, then run dbt snapshot --select [SNAPSHOT NAME]. Expected: the snapshot succeeds.

When to use

• dbt snapshot fails with check_strategy='check' configured.
• You just changed the unique key or the source query.

When not to use

• The snapshot uses strategy='timestamp' (that needs updated_at but no check columns).
• The failure is "target database does not exist" (a different problem).

Tool compatibility

• dbt Core 1.0 and later. Snapshot strategies are adapter-independent.

Variant phrasings

Snapshot check strategy fails on updated_at

The timestamp column is missing or null; the strategy cannot order changes.

check_cols='all' snapshot failing

Every column is compared; a single unexpected null or type change can break it.

Why it happens

The check strategy compares current source rows against the snapshot table using the unique key, then records changes. Null keys cannot be matched, duplicate keys match ambiguously, and without updated_at the ordering breaks.

Edge cases

• Late-arriving data with old updated_at values can create confusing snapshot history; that is working as designed.
• Switching strategies on an existing snapshot table requires rebuilding it; configs do not migrate old rows.
• Very wide tables with check_cols='all' are slow; list only the columns that matter.

Provenance

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