Delta \"A schema mismatch detected when writing\" - handling schema evolution safely in appends, overwrites and MERGE

#The error

AnalysisException: A schema mismatch detected when writing to the Delta table.
To enable schema migration using DataFrameWriter or DataStreamWriter, please set:
'.option("mergeSchema", "true")'.
Table schema: ...
Data schema: ...

Related variants include Failed to merge fields 'amount' and 'amount', Cannot change data type, and [DELTA_FAILED_TO_MERGE_FIELDS].

This is Delta's schema enforcement doing its job. It stops an upstream change from silently corrupting the table.

#1. Compare the schemas

tbl = spark.table("main.sales.orders")
new = df   # the DataFrame you tried to write

t = {f.name: f.dataType.simpleString() for f in tbl.schema}
n = {f.name: f.dataType.simpleString() for f in new.schema}
print("new columns:", set(n) - set(t))
print("missing columns:", set(t) - set(n))
print("type changes:", {c: (t[c], n[c]) for c in set(t) & set(n) if t[c] != n[c]})

#2. New columns: mergeSchema

(df.write.format("delta")
   .mode("append")
   .option("mergeSchema", "true")
   .saveAsTable("main.sales.orders"))

New columns are added at the end, and existing rows get NULL. Some widening type changes (for example byte → short → int) are also allowed with mergeSchema. Narrowing or incompatible changes are not.

In SQL, INSERT doesn't evolve the schema. Add the column first:

ALTER TABLE main.sales.orders ADD COLUMNS (discount_code STRING);

#3. MERGE with new columns

MERGE WITH SCHEMA EVOLUTION INTO main.sales.orders t
USING updates s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

MERGE WITH SCHEMA EVOLUTION is available on recent runtimes. On older runtimes, set the session config instead:

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", "true")

Warning

The session-level autoMerge setting applies to every MERGE in the session, including ones you didn't mean to evolve. Prefer the per-statement syntax where your runtime supports it.

#4. Deliberate full rewrite: overwriteSchema

When you intend to replace the table's schema, for example in a rebuild:

(df.write.format("delta")
   .mode("overwrite")
   .option("overwriteSchema", "true")
   .saveAsTable("main.sales.orders"))

Caution

This replaces both the data and the schema. Downstream queries that use dropped or renamed columns will break. Agree it with consumers, and remember that time travel can recover the previous version only within the retention period.

#5. Type changes

Options, from simplest to most involved:

1. Cast in the pipeline to the existing table type: df.withColumn("amount", col("amount").cast("decimal(18,2)")).
2. Type widening (where supported by your runtime and table feature): ALTER TABLE ... SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true'), then ALTER TABLE ... ALTER COLUMN amount TYPE decimal(20,2). Check the supported type changes and reader compatibility before enabling it.
3. Rewrite: create a new column, backfill it, then drop and rename (needs column mapping), or rebuild the table with overwriteSchema.

Renaming or dropping columns needs column mapping enabled:

ALTER TABLE main.sales.orders SET TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name');
ALTER TABLE main.sales.orders RENAME COLUMN cust_id TO customer_id;

Enabling column mapping upgrades the table protocol. Check that every reader (other engines, Delta Sharing recipients, older runtimes) supports it first.

#6. Streaming and Auto Loader

• Auto Loader with cloudFiles.schemaEvolutionMode = addNewColumns (the default when no schema is given) stops the stream when it sees a new column, then picks it up on restart. Run the job with retries so it restarts on its own.
• Use rescue mode to keep unexpected fields in _rescued_data without failing.
• Streaming writes to Delta also need mergeSchema on the writer to add the new columns to the target.

(spark.readStream.format("cloudFiles")
   .option("cloudFiles.format", "json")
   .option("cloudFiles.schemaLocation", "/Volumes/main/landing/_schemas/orders")
   .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
   .load("/Volumes/main/landing/orders")
   .writeStream.option("checkpointLocation", "/Volumes/main/landing/_chk/orders")
   .option("mergeSchema", "true")
   .toTable("main.bronze.orders"))

#Prevent it next time

• Bronze: allow additive evolution (rescue data and mergeSchema).
• Silver/gold: explicit schemas and casts; changes go through review.
• Data contracts with producers for column changes.
• Alert on _rescued_data being non-null.

#Checklist

• Compared table and data schemas
• New columns allowed only where expected
• MERGE evolution per statement, not session-wide
• overwriteSchema only for agreed rebuilds
• Type changes via cast, widening or a planned migration
• Streams restart automatically after evolution