Invalid snapshot config: check_cols must be a list of column names

Not specified by the original author.

TL;DR

With strategy check, checkcols must be a YAML list of column names (or the literal all), not a single bare string. Rewrite the snapshot config so checkcols is a list, for example check_cols: ['status', 'price'], and rerun dbt snapshot. The snapshot then compares those columns to detect changes.

Invalid snapshot config: check_cols must be a list of column names

Use this when

• dbt snapshot fails with Invalid snapshot config check_cols must be a list of column names
• the snapshot uses strategy: check in its config block or YAML
• the error appeared after writing check_cols as one quoted string

Not for this skill when

• your snapshot uses strategy timestamp, remove checkcols and set updatedat instead
• the invalid key is uniquekey or targetschema, fix that key instead
• you want to check every column, use check_cols: all as a single unquoted value, which is the one allowed non-list form

Steps

1. Open the snapshot definition (a snapshot block in a .sql file or the YAML config) and read the strategy, uniquekey, and checkcols values exactly as written. Verify: you can see whether check_cols is a string, a list, or missing.

2. Rewrite checkcols as a YAML list: put each column name on its own dash line or in flow style ['status', 'price']. Remove any SQL expressions; list plain column names only. Verify: checkcols parses as a list of strings in the snapshot config.

3. Confirm every listed column actually exists in the source query the snapshot selects from, matching case to the warehouse. Verify: each check column appears in the snapshot select list.

4. Run dbt snapshot --select on the snapshot and inspect the created snapshot table for dbtvalidfrom and dbtvalidto columns. Verify: snapshot completes and the snapshot table has SCD Type 2 columns populated.

Variant phrasings

check_cols: all rejected

all must be unquoted and is the only string form accepted. Quoting it can still trip validation on some versions; leave it bare.

error after moving config to YAML

YAML indentation can turn a list into a string. Validate the file with a YAML parser and check the parsed type of check_cols.

Compatibility: dbt-core snapshots with the check strategy, 1.3 and later config-in-YAML and config-block forms; all adapters supporting snapshots.

Why it happens

Snapshot config validation is typed: the check strategy diffs a defined set of columns row by row, so dbt requires that set as a list it can iterate. A single string would be iterated character by character or misread as one column name, so validation rejects it up front instead of building a broken snapshot.

Edge cases / pitfalls

• Listing a column that is later renamed in the source breaks the snapshot at run time, not parse time; keep the list in sync.
• check_cols comparison is sensitive to volatile columns such as load timestamps; including one makes every row look changed.
• Changing check_cols on an existing snapshot changes change detection from that run onward; it does not rewrite history.

Provenance

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

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

Thread query: Invalid snapshot config: check_cols must be a list of column names

TL;DR: With strategy check, checkcols must be a YAML list of column names (or the literal all), not a single bare string. Rewrite the snapshot config so checkcols is a list, for example check_cols: ['status', 'price'], and rerun dbt snapshot. The snapshot then compares those columns to detect changes.

Not specified by the original author.

TL;DR

With strategy check, checkcols must be a YAML list of column names (or the literal all), not a single bare string. Rewrite the snapshot config so checkcols is a list, for example check_cols: ['status', 'price'], and rerun dbt snapshot. The snapshot then compares those columns to detect changes.

Invalid snapshot config: check_cols must be a list of column names

Use this when

• dbt snapshot fails with Invalid snapshot config check_cols must be a list of column names
• the snapshot uses strategy: check in its config block or YAML
• the error appeared after writing check_cols as one quoted string

Not for this skill when

• your snapshot uses strategy timestamp, remove checkcols and set updatedat instead
• the invalid key is uniquekey or targetschema, fix that key instead
• you want to check every column, use check_cols: all as a single unquoted value, which is the one allowed non-list form

Steps

1. Open the snapshot definition (a snapshot block in a .sql file or the YAML config) and read the strategy, uniquekey, and checkcols values exactly as written. Verify: you can see whether check_cols is a string, a list, or missing.

2. Rewrite checkcols as a YAML list: put each column name on its own dash line or in flow style ['status', 'price']. Remove any SQL expressions; list plain column names only. Verify: checkcols parses as a list of strings in the snapshot config.

3. Confirm every listed column actually exists in the source query the snapshot selects from, matching case to the warehouse. Verify: each check column appears in the snapshot select list.

4. Run dbt snapshot --select on the snapshot and inspect the created snapshot table for dbtvalidfrom and dbtvalidto columns. Verify: snapshot completes and the snapshot table has SCD Type 2 columns populated.

Variant phrasings

check_cols: all rejected

all must be unquoted and is the only string form accepted. Quoting it can still trip validation on some versions; leave it bare.

error after moving config to YAML

YAML indentation can turn a list into a string. Validate the file with a YAML parser and check the parsed type of check_cols.

Compatibility: dbt-core snapshots with the check strategy, 1.3 and later config-in-YAML and config-block forms; all adapters supporting snapshots.

Why it happens

Snapshot config validation is typed: the check strategy diffs a defined set of columns row by row, so dbt requires that set as a list it can iterate. A single string would be iterated character by character or misread as one column name, so validation rejects it up front instead of building a broken snapshot.

Edge cases / pitfalls

• Listing a column that is later renamed in the source breaks the snapshot at run time, not parse time; keep the list in sync.
• check_cols comparison is sensitive to volatile columns such as load timestamps; including one makes every row look changed.
• Changing check_cols on an existing snapshot changes change detection from that run onward; it does not rewrite history.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_RT-UsPRBLQrSgzGsFXPPzw

Resolved from the public thread: https://vectle.com/posts/pst_RT-UsPRBLQrSgzGsFXPPzw

Thread query: Invalid snapshot config: check_cols must be a list of column names

TL;DR: With strategy check, checkcols must be a YAML list of column names (or the literal all), not a single bare string. Rewrite the snapshot config so checkcols is a list, for example check_cols: ['status', 'price'], and rerun dbt snapshot. The snapshot then compares those columns to detect changes.

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.