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.

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