Delta Lake schema enforcement and schema evolution, with mergeSchema

A table is a promise to everyone who reads it: these columns, with these types. Sources break that promise all the time. A shop adds a sales channel to its orders, a team renames a field, a file arrives with an extra column. This is Schema DriftSchema DriftWhen the shape of incoming data changes without warning, such as a renamed or new column, and breaks or silently corrupts the pipelines that read it., and a Delta table meets it at the door.

A write with a new column fails

The shop's site starts sending a channel for each order. Save this as schema.py and run it after the scripts of parts 1 and 2:

from delta_session import delta_spark

spark = delta_spark()
web = spark.sql("""
    SELECT 1008L AS order_id, 'C03' AS customer_id, 'placed' AS status,
           CAST(15.00 AS DECIMAL(10,2)) AS amount,
           TIMESTAMP'2026-10-05 10:00:00' AS updated_at, 'web' AS channel
""")
try:
    web.write.format("delta").mode("append").saveAsTable("orders")
except Exception as error:
    print(str(error).splitlines()[0])

web.write.format("delta").mode("append").option("mergeSchema", "true").saveAsTable("orders")
spark.sql("SELECT order_id, status, channel FROM orders ORDER BY order_id").show()

[DELTA_METADATA_MISMATCH] A metadata mismatch was detected when writing to the Delta table.
+--------+---------+-------+
|order_id|   status|channel|
+--------+---------+-------+
|    1001|  shipped|   NULL|
|    1002|cancelled|   NULL|
|    1004| returned|   NULL|
|    1005|cancelled|   NULL|
|    1006|   placed|   NULL|
|    1007|  shipped|   NULL|
|    1008|   placed|    web|
+--------+---------+-------+

The first append fails, and nothing is committed. The full message prints the table's schema and the data's, side by side, and names the option that would let the change through. That failure is the feature: a change in the source becomes a decision for a person, not a column readers discover next month.

With mergeSchema the same append adds channel to the table in the same commit. The 7 orders already there read NULL in it; no file is rewritten. Use the option on the 1 write that needs it, not as a setting for the whole session, so the next surprise still stops the job.

When you know a column is coming, add it before the data arrives, on a table that does not have it yet, and the change has its own line in the history:

ALTER TABLE orders ADD COLUMNS (channel STRING)

Delta enforces 3 rules on a write: every column of the data exists in the table, the types are compatible, and no 2 names differ only by case. Columns of the table that the data does not have are simply filled with null, so a source that stops sending a column does not fail. Check for that one yourself.

To drop a column or change its type, rewrite the table with mode("overwrite") and .option("overwriteSchema", "true"). That rewrites every file, and a reader of the old column breaks, so it belongs in a planned change.

MERGE drops a new column without a word

MERGE does not follow the append's rule. Without schema evolution, UPDATE SET * and INSERT * use the columns the table has and ignore the rest of the source. The orders start arriving with a coupon. Save this as schema_merge.py:

from delta_session import delta_spark

spark = delta_spark()


def merge_change(order_id, customer_id, updated_at, evolve=""):
    """Merge 1 change, a delivery with a coupon code, a column the table does not have."""
    spark.sql(f"""
        CREATE OR REPLACE TEMP VIEW changes AS
        SELECT {order_id}L AS order_id, '{customer_id}' AS customer_id, 'delivered' AS status,
               CAST(19.90 AS DECIMAL(10,2)) AS amount,
               TIMESTAMP'{updated_at}' AS updated_at, 'web' AS channel, 'AUTUMN10' AS coupon
    """)
    spark.sql(f"""
        MERGE {evolve} INTO orders AS t
        USING changes AS s
        ON t.order_id = s.order_id
        WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
        WHEN NOT MATCHED THEN INSERT *
    """).select("num_updated_rows").show()


merge_change(1006, "C02", "2026-10-06 12:00:00")
print(spark.table("orders").columns)

merge_change(1007, "C03", "2026-10-06 15:00:00", evolve="WITH SCHEMA EVOLUTION")
spark.sql("SELECT order_id, status, channel, coupon FROM orders WHERE order_id >= 1006 ORDER BY order_id").show()

+----------------+
|num_updated_rows|
+----------------+
|               1|
+----------------+

['order_id', 'customer_id', 'status', 'amount', 'updated_at', 'channel']
+----------------+
|num_updated_rows|
+----------------+
|               1|
+----------------+

+--------+---------+-------+--------+
|order_id|   status|channel|  coupon|
+--------+---------+-------+--------+
|    1006|delivered|    web|    NULL|
|    1007|delivered|    web|AUTUMN10|
|    1008|   placed|    web|    NULL|
+--------+---------+-------+--------+

The first MERGE updated 1006 and reported success, and its coupon is gone: the table has no such column, so the value was never stored, and loading the same change again will not bring it back, because it is no longer newer than the row. MERGE WITH SCHEMA EVOLUTION INTO added the column and kept 1007's coupon. In Python, call .withSchemaEvolution() on the merge builder before execute().

So a job that loads with MERGE needs its own check: compare the columns of each batch with the table's, and stop when the batch has one the table lacks, unless you mean to add it. The module's build does that with the header of each file.

On Databricks, MERGE WITH SCHEMA EVOLUTION needs Databricks Runtime 15.4 LTS or later. Part 4 turns to the files under the table, and why a table that is written often gets slower.