topic Re: Can't mergeSchema handle int and bigint? in Data Engineering

Can't mergeSchema handle int and bigint?

Dhruv-22 — Tue, 21 Oct 2025 11:25:23 GMT

I have a table which has a column of data type 'bigint'. While overwriting it with new data, given that I do full loads, I used 'mergeSchema' to handle schema changes. The new data's datatype was int. I thought mergeSchema can easily handle that, but it throws an error.

crm_retail_df.write.mode("overwrite").format("delta").option('mergeSchema', True).saveAsTable(silver_table)

-- Output
[DELTA_FAILED_TO_MERGE_FIELDS] Failed to merge fields 'Lob_Pk' and 'Lob_Pk' SQLSTATE: 22005

Shouldn't 'mergeSchema' automatically widen the incoming from int to bigint? If not, then does mergeSchema only handle cases when datatypes match between new and old data?

Re: Can't mergeSchema handle int and bigint?

K_Anudeep — Tue, 21 Oct 2025 14:06:55 GMT

Hello @Dhruv-22 ,

No—mergeSchema doesn’t auto-widen an incoming INT column to a table’s BIGINT (nor does it auto-cast). mergeSchema mainly helps add new columns (and historically only a tiny set of numeric upcasts), but it won’t change an existing column’s type or cast your DataFrame to match the table. That’s why you see [DELTA_FAILED_TO_MERGE_FIELDS]

Doc Link: https://www.databricks.com/blog/2019/09/24/diving-into-delta-lake-schema-enforcement-evolution.html

However, you can rewrite the schema and data using

df_with_new_types .write.format("delta") .mode("overwrite") .option("overwriteSchema", "true") .saveAsTable(table_name))

If you are using DBR 15.4 and above, you can try enabling type_widening

Doc Link : https://docs.databricks.com/aws/en/delta/type-widening#enable-type-widening

Re: Can't mergeSchema handle int and bigint?

Chiran-Gajula — Tue, 21 Oct 2025 14:02:18 GMT

Hi Dhruv,

Delta won't automatically upcast unless you explicitly handle it. Cast the column Lob_Pk to LongType (which maps to BIGINT in SQL/Delta). Try below snippet

from pyspark.sql.functions import col
from pyspark.sql.types import LongType

crm_retail_df = crm_retail_df.withColumn("Lob_Pk", col("Lob_Pk").cast(LongType()))
crm_retail_df.write \
.mode("overwrite") \
.format("delta") \
.option("mergeSchema", "true") \
.saveAsTable(silver_table)

Re: Can't mergeSchema handle int and bigint?

fehrin1 — Thu, 19 Feb 2026 17:20:46 GMT

Just out of curiosity: In my impression type_widening would only solve the opposite problem (writing bigint into int) and not the original one (writing int into bigint)?