Delta Lake Schema Evolution Explained with Real Examples

>GLB_ // tools · Delta Lake

Upstream adds a column on a Friday. Your Delta write fails with AnalysisException: A schema mismatch detected when writing to the Delta table. That’s not a bug. That’s schema enforcement doing its job.

The question is what you want to happen instead. Delta gives you several tools, and they behave very differently. This post walks through each one with a reproducible example.

Setup

from pyspark.sql import Row

path = "/tmp/delta/customers"

spark.createDataFrame([
    Row(id=1, name="Ana", country="AR"),
    Row(id=2, name="Ben", country="DE"),
]).write.format("delta").mode("overwrite").save(path)

Schema: id BIGINT, name STRING, country STRING.

1. Schema enforcement (the default)

new = spark.createDataFrame([Row(id=3, name="Cai", country="BR", tier="gold")])
new.write.format("delta").mode("append").save(path)
# AnalysisException: A schema mismatch detected when writing to the Delta table

Rules on write, by default:

• Extra columns in the DataFrame → rejected.
• Missing columns in the DataFrame → accepted, filled with NULL.
• Incompatible types (e.g. STRING into INT) → rejected.
• Column names that differ only by case → rejected (Delta preserves case but is case-insensitive on names).

2. Adding columns: mergeSchema

(new.write.format("delta")
    .mode("append")
    .option("mergeSchema", "true")
    .save(path))

Result: tier STRING is appended to the end of the schema. Existing rows return NULL for tier. No files are rewritten. Only the metadata in the log changes.

Session-wide equivalent (affects every write, use with care):

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

mergeSchema also handles nested additions: a new field inside a struct is merged in.

What it does not do: change existing column types (except the safe upcasts covered below), rename, or drop columns.

3. Schema evolution in MERGE

The typical CDC upsert:

from delta.tables import DeltaTable

target = DeltaTable.forPath(spark, path)
updates = spark.createDataFrame([
    Row(id=2, name="Ben", country="DE", tier="silver", email="ben@x.io"),
])

(target.alias("t")
    .merge(updates.alias("s"), "t.id = s.id")
    .withSchemaEvolution()          # Delta 3.2+
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute())

SQL:

MERGE WITH SCHEMA EVOLUTION INTO customers t
USING updates s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

email gets added. Important detail: evolution only triggers through UPDATE SET * / INSERT * (or explicit assignments to the new column). A MERGE that only assigns existing columns won’t add anything, even with evolution enabled.

On older versions, the autoMerge session conf above is how you enable it for MERGE.

4. Replacing the schema: overwriteSchema

When the change is incompatible (type change, dropping columns) and you’re willing to rewrite:

from pyspark.sql.functions import col

(spark.read.format("delta").load(path)
    .withColumn("id", col("id").cast("string"))
    .write.format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .save(path))

This rewrites all data and replaces the schema. Consequences:

• Streaming readers of the table break (non-additive schema change).
• Time travel still works for old versions, but those versions have the old schema.
• Cost scales with table size.

Use it deliberately, not as the default fix for mismatch errors.

5. Rename and drop without rewriting: column mapping

By default, Delta identifies columns in Parquet files by name. Renaming would orphan the data. Column mapping switches to logical names mapped to physical IDs:

ALTER TABLE customers SET TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name',
  'delta.minReaderVersion' = '2',
  'delta.minWriterVersion' = '5'
);

ALTER TABLE customers RENAME COLUMN country TO country_code;
ALTER TABLE customers DROP COLUMN tier;

Both are metadata-only. Trade-offs:

• Protocol upgrade. Readers must support column mapping. Older Spark/Delta versions, some external engines and some connectors will fail to read the table. Check every consumer before enabling it.
• Streaming. Renames/drops are non-additive changes. Streaming readers need extra handling (see schemaTrackingLocation in the streaming docs).
• DROP COLUMN doesn’t delete the bytes; the data stays in the Parquet files until they’re rewritten.

6. Changing types: type widening

Delta 4.0 adds type widening as a table feature: widen types without rewriting data. (Example on an orders table with amount DECIMAL(10,2) and qty INT.)

ALTER TABLE orders SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true');

ALTER TABLE orders ALTER COLUMN amount TYPE DECIMAL(18, 2);  -- from DECIMAL(10,2)
ALTER TABLE orders ALTER COLUMN qty TYPE BIGINT;             -- from INT

Supported changes are widening only (e.g. BYTE → SHORT → INT → BIGINT, FLOAT → DOUBLE, DATE → TIMESTAMP_NTZ, larger decimal precision/scale; the exact matrix is in the docs). Narrowing or STRING → INT still requires a rewrite.

Without type widening, the only path is overwriteSchema (rewrite) or a new column + backfill.

Decision table

What I’d recommend in a pipeline

• Keep enforcement on for curated (silver/gold) tables. A failed write is cheaper than silently propagating a renamed column as NULLs.
• Allow mergeSchema at the bronze layer, where capturing everything matters more than strictness.
• Don’t set autoMerge globally in shared clusters. Enable evolution per write.
• Before enabling column mapping or type widening, inventory every reader of the table. Protocol upgrades are the change that breaks people who aren’t in the room.
• Version your expected schema in code and diff it against DeltaTable.forPath(...).toDF().schema in CI.

References

• Delta Lake — Schema enforcement and evolution (batch writes): https://docs.delta.io/latest/delta-batch.html#schema-validation
• Delta Lake — Update table schema (mergeSchema, overwriteSchema): https://docs.delta.io/latest/delta-batch.html#update-table-schema
• Delta Lake — Merge with schema evolution: https://docs.delta.io/latest/delta-update.html#automatic-schema-evolution
• Delta Lake — Column mapping: https://docs.delta.io/latest/delta-column-mapping.html
• Delta Lake — Type widening: https://docs.delta.io/latest/delta-type-widening.html
• Delta Lake — Table protocol versioning and features: https://docs.delta.io/latest/versioning.html
• Delta Lake — Streaming with column mapping (schemaTrackingLocation): https://docs.delta.io/latest/delta-streaming.html