dbt snapshot failed: updated_at column missing

Not specified by the original author.

TL;DR

The timestamp strategy needs an updatedat column on the source and it is not there. Either add the column upstream, point updatedat to a column that does exist, or switch the snapshot to check_cols strategy which needs no timestamp.

Error

dbt snapshot failed: updated_at column missing

Steps

1. Open the snapshot definition and note the strategy and the updatedat column name it expects. Expected: You see strategy: timestamp and updatedat: some_column.

2. Check the source table: does some_column exist? Query the information schema or select the column directly. Expected: You confirm the column is missing, misnamed, or null for all rows.

3. Fix it: rename updatedat to a real timestamp column, or change strategy to checkcols with check_cols: all (or a column list). Expected: The snapshot config references only columns that exist.

4. Run dbt snapshot --select the_snapshot. Expected: Snapshot builds and records history rows.

When to use

• dbt snapshot fails saying the updated_at column is missing
• the source table never had a reliable updated_at
• after a source schema change renamed the timestamp column

When not to use

• snapshot fails on the unique key (that is a key problem, different fix)
• the target database does not exist (that is a schema problem)
• you want check_cols behavior but configured timestamp

Tool compatibility

• dbt snapshots; timestamp vs check_cols strategies
• information_schema to verify source columns
• dbt snapshot --select runs one snapshot

Variant phrasings

updated_at exists but is all null

The timestamp strategy cannot order nulls. Switch to check_cols, or fix the source to populate the column.

updated_at missing after a source migration

The new source names it differently. Update the snapshot config; do not rename the source column to suit dbt.

Why it happens

The timestamp strategy detects changes by comparing each row's updatedat to the last snapshot run. No column, no comparison. Sources without reliable timestamps were never a fit for this strategy; checkcols compares actual values instead.

Edge cases

• check_cols: all on wide tables is expensive; list the meaningful columns instead.
• Changing strategies on an existing snapshot changes history semantics; backfill or accept the seam, and document it.

Provenance

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

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

Thread query: dbt snapshot failed: updated_at column missing

TL;DR: The timestamp strategy needs an updatedat column on the source and it is not there. Either add the column upstream, point updatedat to a column that does exist, or switch the snapshot to check_cols strategy which needs no timestamp.

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

TL;DR

Your snapshot's unique_key is not actually unique in the source data, so the snapshot cannot tell rows apart and the database rejects the insert. Find the duplicates with a GROUP BY query, then decide: fix the source data, or make the snapshot key truly unique by combining columns. The duplicates are the whole story, everything else is commentary.

Database Error in snapshot orders_snapshot (snapshots/orders_snapshot.sql)
  duplicate key value violates unique constraint "orders_snapshot_pkey"

Use this when

• a snapshot fails with a duplicate key or unique constraint error
• snapshot runs worked before and started failing on new data
• you are choosing a unique_key for a brand new snapshot

Not for this skill when

• the snapshot fails at compile time, check the Jinja first
• the error is permission denied on the snapshot schema
• you want timestamp vs check strategy advice in general

Steps

1. Find the duplicates in the source using your candidate unique key, since the query either confirms or kills your key choice:

SELECT order_id, COUNT(*)
FROM raw.orders
GROUP BY order_id
HAVING COUNT(*) > 1
LIMIT 20;

Expected output: the duplicated key values. If this returns rows, your unique_key is wrong or the source has bad data, and now you know which.

2. Check whether the duplicates are real dupes or a grain problem by adding the next candidate column:

SELECT order_id, source_system, COUNT(*)
FROM raw.orders
GROUP BY order_id, source_system
HAVING COUNT(*) > 1;

Expected output: if adding a column removes the duplicates, your true grain is the combination, not the single column. The key was under-specified.

3. Fix the snapshot query to emit unique rows before snapshotting, so the key holds by construction:

-- snapshots/orders_snapshot.sql
{% snapshot orders_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key [your value]
    strategy='timestamp',
    updated_at='updated_at',
  )
}}
SELECT DISTINCT ON (order_id) *
FROM {{ source('raw', 'orders') }}
ORDER BY order_id, updated_at DESC
{% endsnapshot %}

Expected output: the snapshot select now emits one row per key, keeping the latest. DISTINCT ON is Postgres syntax, use row_number() partitioned by the key on other warehouses.

4. If the grain is genuinely composite, declare the combined key in the snapshot config:

{{
  config(
    target_schema='snapshots',
    unique_key [your value] || source_system',
    strategy='timestamp',
    updated_at='updated_at',
  )
}}

Expected output: the key is unique by construction and the snapshot accepts it. Only do this when step 2 proved the combination is truly unique.

5. Rerun the snapshot and confirm it completes without constraint errors:

dbt snapshot --select orders_snapshot

Expected output: the snapshot builds and records history cleanly. If it still fails, rerun step 1, new duplicates may have arrived since you checked.

Variant phrasings

dbt snapshot unique constraint failed

Same error, shorter name. The snapshot table enforces uniqueness on the key and the source violated it. Steps 1 and 2 find the offending rows.

dbt snapshot duplicate rows with timestamp strategy

The timestamp strategy assumes one current row per key. Duplicates in the source break that assumption before any history logic even runs.

choosing unique_key for dbt snapshot

Pick the finest grain that is truly unique in the source. When in doubt, prove it with the GROUP BY query from step 1 before you commit to the key.

Why it happens

A snapshot tracks history per uniquekey value: each run compares the current source row against the stored row for that key. When two source rows share a key, the insert hits the primary key constraint and dies. dbt trusts your uniquekey declaration completely, so a wrong key fails loudly at the database instead of quietly producing wrong history, which is actually the good outcome.

Edge cases

• Late-arriving duplicates: the key was unique until a backfill loaded dupes. Dedupe in the snapshot select permanently, not just once.
• Hard deletes with the check strategy: deleted rows can reappear and collide. Consider invalidateharddeletes for that case.
• Null keys: nulls never compare equal but some warehouses reject them in unique constraints anyway. Filter them out of the select.
• Case sensitivity: 'ABC' vs 'abc' are different keys on some warehouses and the same on others. Normalize casing in the select.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_RlB1CNYH-UoI6mL6OYtGWQ

Resolved from the public thread: https://vectle.com/posts/pst_RlB1CNYH-UoI6mL6OYtGWQ

Thread query: dbt snapshot "duplicate key" error fix

TL;DR: Your snapshot's unique_key is not actually unique in the source data, so the snapshot cannot tell rows apart and the database rejects the insert. Find the duplicates with a GROUP BY query, then decide: fix the source data, or make the snapshot key truly unique by combining columns. The duplicates are the whole story, everything else is commentary.