delta lake merge failed error cannot change data type of existing column

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

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

Inference sees a value like 2026-01-05 and, if the file or API sample carries a midnight time or a format hint, may call it a timestamp. The column then lands as timestamp, the downstream partition defined on a date no longer lines up, and date equality filters silently miss. Stop inferring this column: declare it a date in the loader or model, cast existing data in a controlled rebuild, and check every downstream partition and filter that assumed a date.

agent's schema inference picked timestamp instead of date, broke downstream partition

Use this when

• A downstream partition or pruning error appears after a load or model was regenerated.
• The warehouse shows a column typed timestamp where the business concept is a calendar date.
• Filters comparing the column to a date return no rows, while a range over the day returns them.

Not for this skill when

• The events genuinely have meaningful times of day. Model them as timestamps on purpose and fix the partition design instead.
• The breakage is a timezone shift on true timestamps. That is a different correction.

Steps

1. Confirm the type change: compare the column type in the warehouse now against a previous table snapshot, an older model, or the documented schema. Note when the type flipped and what load change coincided. Verify: the inference change is tied to a specific load version
2. Inspect the raw source values for the column across several files or days, looking for midnight times, mixed formats, or a sample row that misleads inference. Note the misleading shape if present. Verify: you know why inference guessed timestamp
3. Declare the schema explicitly in the loader, the file reader config, or the staging model: this column is a date, parsed with a stated format. Note the declaration location so regeneration keeps it. Verify: the type no longer depends on the sample of the day
4. Rebuild the affected table or partition set with the date type, casting the existing timestamps by truncating, and verify no rows fall outside expected dates. Note row counts by date before and after. Verify: the corrected table matches the old counts per date
5. Find downstream models and queries that compare or partition on the column, align them to the date type, and rerun their tests. Note the partition filter returns rows again. Verify: downstream date logic works on the restored type

Variant phrasings

date column inferred as timestamp partition filter returns no rows

The symptom phrasing.

schema inference date vs timestamp loader fix explicit schema

The explicit schema answer searched directly.

Compatibility: Any inference based loader or generated staging model feeding Snowflake, BigQuery, or another warehouse with distinct date and timestamp types. Partition definitions are warehouse specific; the type discipline is not.

Why it happens

Date and timestamp look interchangeable in a sample and behave differently everywhere that matters: equality, grouping, partition membership, and joins to a calendar table. Inference optimises for parsing the sample in front of it, and a single row with a time component, often midnight from a system that stores dates as datetimes, tips the guess. Because every individual value still looks right after the load, the breakage surfaces downstream as empty partitions and silent filter misses rather than a load error anyone connects to a type.

Edge cases / pitfalls

• Truncating timestamps to dates in a different timezone than the business date moves some rows across midnight. State the timezone of the truncation.
• Other inferred columns in the same load deserve the same audit. Inference rarely gets exactly one column wrong.
• Pin the whole schema for production loads, not just this column. A schema file in version control is the durable fix.
• Calendar or date dimension joins fail silently to nulls on a type mismatch in some warehouses. Check join hit rates after the fix.

Provenance

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

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

Thread query: agent's schema inference picked timestamp instead of date, broke downstream partition

TL;DR: Inference sees a value like 2026-01-05 and, if the file or API sample carries a midnight time or a format hint, may call it a timestamp. The column then lands as timestamp, the downstream partition defined on a date no longer lines up, and date equality filters silently miss. Stop inferring this column: declare it a date in the loader or model, cast existing data in a controlled rebuild, and check every downstream partition and filter that assumed a date.