Schema enforcement & evolution
Databricks · Delta Lake
Theory
Delta tables have a schema stored in the transaction log, and schema enforcement checks every write against it. If a DataFrame has a column the table does not have, or a column with an incompatible type, the write fails with a schema mismatch error instead of silently corrupting the table. Missing nullable columns are filled with NULL, and safe type widening (for example INT to BIGINT, where supported) can be allowed.
Schema evolution is the opt-in way to change the schema on purpose. With .option("mergeSchema", "true") on a write, new columns in the DataFrame are added to the table. MERGE WITH SCHEMA EVOLUTION does the same for merges, ALTER TABLE ... ADD COLUMNS adds them explicitly, and .option("overwriteSchema", "true") with mode overwrite replaces the schema completely. For example, if a new feed adds a channel column to orders, the append fails until you add mergeSchema or ALTER the table.
Why we use it
Upstream systems change: columns get added, renamed or retyped. Enforcement stops bad data from breaking downstream reports, and evolution lets you accept good changes deliberately instead of rebuilding tables.
When to use it
Keep enforcement on (it always is) for curated silver and gold tables, and evolve explicitly with ALTER TABLE or mergeSchema when a change is approved. For raw bronze ingestion, Auto Loader's schema evolution modes handle new columns automatically.
How it works
- A DataFrame is written to a Delta table
- Delta compares its schema with the table schema
- Do columns and types match? Yes: Write succeeds. No: Check whether evolution was requested.
- Is mergeSchema or WITH SCHEMA EVOLUTION set? Yes: Add new columns and write. No: Fail with a schema mismatch error.
- Table stays consistent either way
Syntax
df.write.mode("append").option("mergeSchema", "true").saveAsTable(t)
df.write.mode("overwrite").option("overwriteSchema", "true").saveAsTable(t)
ALTER TABLE t ADD COLUMNS (channel STRING)
MERGE WITH SCHEMA EVOLUTION INTO t USING s ON ...Example
new_orders = spark.createDataFrame(
[(106, 4, "South", 700.0, "2026-01-18", "App")],
"order_id INT, customer_id INT, region STRING, amount DOUBLE, order_date STRING, channel STRING")
# Fails: channel is not in main.sales.orders (schema enforcement)
# new_orders.write.mode("append").saveAsTable("main.sales.orders")
# Works: the channel column is added (schema evolution)
(new_orders.write.mode("append")
.option("mergeSchema", "true")
.saveAsTable("main.sales.orders"))
-- Or evolve explicitly first
ALTER TABLE main.sales.orders ADD COLUMNS (channel STRING);Key points
- Enforcement rejects writes with unknown columns or incompatible types
- mergeSchema adds new columns on append; old rows get NULL for them
- overwriteSchema replaces the whole schema; use only with mode overwrite
- Renaming or dropping columns needs column mapping (ALTER TABLE ... RENAME/DROP COLUMN)
- Schema changes are recorded as new versions in DESCRIBE HISTORY