dbt snapshot failed error "snapshot strategy timestamp 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

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.

2. 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.

3. 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.

4. 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.

5. 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

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

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