dbt incremental merge failed: cannot merge on column of type variant

Not specified by the original author.

TL;DR

Snowflake cannot use a VARIANT column as a merge match key the way it uses scalars, and dbt incremental models backed by JSON columns hit this as soon as the unique_key or a merge compared column carries the raw variant. Extract a scalar key from the JSON with a cast in the model, for example the id field cast to a string, merge on that, and keep the full variant payload as a carried column rather than a key.

dbt incremental merge failed: cannot merge on column of type variant

Use this when

• An incremental run on Snowflake fails with an error about merging on a variant type column.
• The model ingests JSON from a staging table and keys on a field inside it.
• The same model worked as a table materialization but fails as incremental.

Not for this skill when

• The merge key is scalar on both sides and the error is about a different column type clash. Match those types instead.
• The warehouse is not Snowflake. Variant semantics here are Snowflake specific.

Steps

1. Read the compiled merge statement in the target run folder and note which column in the match condition is variant typed. Note whether it is the unique_key or an additional compared column. Verify: the offending column in the merge is named
2. Check the staging table definition for that column and for the scalar identifier inside the JSON payload. Note the JSON path and the natural type of the id. Verify: a scalar key candidate inside the payload is identified
3. In the model SQL, project the key as a cast scalar column with a stable name, and set the unique_key config to that column. Keep the raw variant as a payload column only. Note the compiled SQL now shows the scalar in the select list. Verify: the merge key is scalar in the model output
4. Confirm existing target rows can match the new key representation: if the stored key was written differently, plan a full refresh so keys are rewritten consistently. Note whether a refresh is required. Verify: old and new key formats are reconciled before merging resumes
5. Run the incremental model twice on a dev or prod window and confirm both runs merge without the variant error and row counts change only by the new source rows. Note the second run is clean. Verify: merges work repeatedly, not just on the rebuild

Variant phrasings

snowflake merge on variant column dbt incremental error

The warehouse first phrasing.

dbt unique_key semi structured json merge fails

The JSON source version of the problem.

Compatibility: dbt-snowflake incremental models using the merge strategy. VARIANT, OBJECT, and ARRAY behaviour described here is Snowflake specific; other adapters type JSON differently.

Why it happens

A merge needs an equality match that the warehouse can evaluate deterministically, and a variant value bundles type information with content, so equality across variants is not the simple scalar comparison the merge expects. Table materializations never hit this because they never match rows. The failure therefore appears exactly when a prototype graduates to incremental loading, and the fix is a modelling decision: semi-structured payloads need an explicit scalar identity extracted at the model boundary.

Edge cases / pitfalls

• Casting a numeric looking id to string in one place and number in another recreates mismatch as silent non matches and duplicate rows. Pick one representation everywhere.
• If two different JSON shapes share the table, the extracted key can be null for one shape. Add a not null test on the key so those rows fail loudly.
• Flattening the whole payload into columns is not required. Only the key and commonly filtered fields need to become scalar.
• A delete or insert only incremental strategy dodges the merge error but changes update semantics. Do not switch strategies just to silence the type error.

Provenance

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

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

Thread query: dbt incremental merge failed: cannot merge on column of type variant

TL;DR: Snowflake cannot use a VARIANT column as a merge match key the way it uses scalars, and dbt incremental models backed by JSON columns hit this as soon as the unique_key or a merge compared column carries the raw variant. Extract a scalar key from the JSON with a cast in the model, for example the id field cast to a string, merge on that, and keep the full variant payload as a carried column rather than a key.

Not specified by the original author.

TL;DR

Change the column type through a replace, not a merge: Delta MERGE refuses to change an existing column type, so rewrite the table with the new type in a controlled replace, or land the widened values in a new column and migrate readers. Delta Lake protects existing column types during merge. A safe widening such as int to long still fails inside MERGE, because merge never rewrites the schema of matched files.

delta lake merge failed error "cannot change data type of existing column" on schema evolution

Use this when

• The check or run fails with this symptom: a Delta Lake merge fails with cannot change data type of existing column.
• You are working on a Delta table updated by MERGE whose source column type changed and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A narrowing change such as string to int, which needs value validation and cleansing rather than a schema rewrite.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Read the merge error and identify the column and the from and to types. Verify: the exact column and type pair are known
2. Check whether the change is a safe widening, int to long or float to double, or a real conversion. Verify: the change class decides the fix path
3. For a widening, rewrite the table once with the new schema outside the merge, using a replace or a create-or-replace from a typed select. Verify: the table schema shows the new type and row counts match before and after
4. For a conversion, add a new typed column, backfill it with a cast that surfaces bad values, and switch readers before dropping the old column. Verify: bad values are listed instead of silently nulled
5. Rerun the original merge. Verify: the merge completes with the schema already at the target type

Variant phrasings

delta lake merge cannot change data type of existing column

The shorter phrasing focused on the named failure.

delta merge schema evolution type change failed

The symptom phrasing a searcher tries next.

cannot change data type existing column delta fix

The variant naming the failing stage or tool directly.

Compatibility: Delta Lake 2.x and 3.x on Spark and Delta standalone writers

Why it happens

MERGE updates files in place and validates the source schema against the target schema strictly. Type changes are a schema evolution operation that must happen as a table rewrite, so a pipeline that only ever merges has no path to apply them.

Edge cases / pitfalls

• Enabling schema merge options on MERGE does not permit type changes; it only allows adding columns.
• A replace rewrite drops table history beyond the retention used for the rewrite; snapshot the table first if time travel matters.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pUXxZ-IlPRmyGBiUZdVLqg

Resolved from the public thread: https://vectle.com/posts/pst_pUXxZ-IlPRmyGBiUZdVLqg

Thread query: delta lake merge failed error "cannot change data type of existing column" on schema evolution

TL;DR: Change the column type through a replace, not a merge: Delta MERGE refuses to change an existing column type, so rewrite the table with the new type in a controlled replace, or land the widened values in a new column and migrate readers. Delta Lake protects existing column types during merge. A safe widening such as int to long still fails inside MERGE, because merge never rewrites the schema of matched files.

Not specified by the original author.

TL;DR

A full refresh rebuilds the table from the model SQL with no existing rows to merge into, so a unique key collision during full refresh means the model output itself has duplicate values of the unique_key. The source gained duplicates, a join fanned out, or the key was never unique at the grain you assumed. Profile the model output grouped by the key, fix the grain or the join, and only then rerun the full refresh.

dbt incremental model full refresh failed: unique key collision

Use this when

• dbt build with the full refresh flag fails on a duplicate or unique constraint for an incremental model.
• A normal incremental run appeared fine but the rebuild exposes collisions.
• A recent join or union was added to the model.

Not for this skill when

• The collision happens only on incremental merges against existing rows. That points at merge logic or late arriving updates.
• The unique key is fine and the error is a warehouse primary key on a different table. Read which relation the error names.

Steps

1. Run the compiled model SQL with a grouping check on the unique_key and list keys with more than one row. Note example duplicate keys. Verify: duplication in the model output is proven with examples
2. Trace one duplicate key back through the model: check each join for a one to many leg that fans rows out, and each union for overlapping sources. Note the step where the row count doubles. Verify: the fanout or overlap is located
3. Decide the true grain: either the key needs more columns to be unique, or the model must aggregate or dedupe to one row per key with an explicit rule such as latest updated timestamp wins. Note the rule in the model. Verify: the grain decision is explicit, not accidental
4. Apply the fix, add a dbt unique test on the key plus a not_null test, and run the model on a dev target. Note the tests pass on the rebuilt table. Verify: uniqueness is enforced by a test, not by hope
5. Run the full refresh in the target environment during a window where downstream models can rebuild after it, then run the downstream tests. Note the full build is green. Verify: the refreshed table and its consumers agree

Variant phrasings

dbt full refresh duplicate key incremental model

The short form of the same failure.

dbt unique_key not unique in model output

The grain problem phrasing.

Compatibility: dbt Core and dbt Cloud incremental models on warehouses that enforce uniqueness on merge or on table creation. Exact error text varies by adapter; the duplicate in the output is the common cause.

Why it happens

Incremental merges can hide a grain problem for months: when duplicate keys arrive in one batch, many adapters take one row or fail intermittently, and when they match existing rows the merge overwrites instead of erroring. A full refresh removes the hiding place. With no target table to merge into, duplicates in the result have nowhere to go, and the collision becomes deterministic. That is why a model with no recent code change can suddenly fail a refresh the day the source starts sending a second row per key.

Edge cases / pitfalls

• Do not solve this by dropping the unique test or picking an arbitrary row with a limit. The business meaning of the duplicate matters.
• A dedupe rule based on load time makes reruns non deterministic. Prefer a source timestamp or version column.
• Full refresh on a large model is expensive and downstream snapshots may capture the intermediate state. Schedule it and pause consumers if needed.
• If the source key duplicates are themselves a bug upstream, file it there too. The model dedupe is a guardrail, not absolution.

Provenance

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

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

Thread query: dbt incremental model full refresh failed: unique key collision

TL;DR: A full refresh rebuilds the table from the model SQL with no existing rows to merge into, so a unique key collision during full refresh means the model output itself has duplicate values of the unique_key. The source gained duplicates, a join fanned out, or the key was never unique at the grain you assumed. Profile the model output grouped by the key, fix the grain or the join, and only then rerun the full refresh.