# Delta Lake schema enforcement and schema evolution, with mergeSchema

A Delta table checks the schema of every write against its own: an append with a column the table lacks fails with DELTA_METADATA_MISMATCH, so a changed source cannot quietly alter the table. To add the column on purpose, write with .option("mergeSchema", "true") or run ALTER TABLE ... ADD COLUMNS, and existing rows get null in it. MERGE is the exception: with UPDATE SET * and INSERT * it ignores source columns the table lacks, with no error, unless you write MERGE WITH SCHEMA EVOLUTION.

- Page: https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/schema
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 4: Tables: Parquet, Delta Lake and Iceberg, Schema enforcement and evolution
- Parts of this module: The transaction log and time travel (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg); MERGE (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/merge); Schema enforcement and evolution (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/schema); OPTIMIZE, clustering and VACUUM (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/optimize-and-vacuum); Iceberg and UniForm (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/iceberg-and-uniform); Build it: an incremental load (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/build-it)

At the end of this part you can:

- Say what Delta's schema enforcement checks on a write, and why it fails on purpose
- Add a column to a Delta table with mergeSchema, ALTER TABLE or MERGE WITH SCHEMA EVOLUTION
- Spot the new column a MERGE drops without an error

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 Drift](https://tecyfy.com/concepts/schema-drift), 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:

```python
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()
```

```text
[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:

```sql
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`:

```python
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()
```

```text
+----------------+
|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](/data-engineering/course/delta-lake-and-iceberg/build-it) does that with the header of each file.

On Databricks, `MERGE WITH SCHEMA EVOLUTION` needs Databricks Runtime 15.4 LTS or later. [Part 4](/data-engineering/course/delta-lake-and-iceberg/optimize-and-vacuum) turns to the files under the table, and why a table that is written often gets slower.

## Check yourself

1. The source system adds a coupon column. Your nightly job loads it with a MERGE using UPDATE SET * and INSERT *, with no schema evolution. What happens?

   A. The MERGE fails with DELTA_METADATA_MISMATCH, like an append
   B. The MERGE runs, and the coupon values are not stored, because MERGE ignores source columns the table does not have
   C. Delta adds the coupon column to the table, because UPDATE SET * copies every column
   D. The MERGE runs, and stores the coupon in a column called _rescued_data

2. Why is it good that a plain append fails when the data has a column the table does not?

   A. Because Delta tables cannot have more than a fixed number of columns
   B. Because extra columns make Parquet files slower to read
   C. Because a change in the source should be a decision someone makes, not something every reader finds out about later, so the job stops and says what differs
   D. Because Delta cannot store null values in old rows

### Answers

1. B. An append with an extra column fails, but MERGE without schema evolution ignores source columns the table does not have. The job succeeds and the coupons are lost, with no error to tell you. Write MERGE WITH SCHEMA EVOLUTION, or .withSchemaEvolution() in the Python API, to add the column, or add it first with ALTER TABLE.
2. C. Schema enforcement turns a surprise in the data into a failed job with a message that lists the table's schema and the data's. Someone then decides whether the column belongs in the table, and adds it on purpose with mergeSchema or ALTER TABLE. Without the check, a renamed or misspelled column could add a stray column that readers start to depend on.

## Questions

### What is schema enforcement in Delta Lake?

It is the check Delta makes on every write. Every column of the data must exist in the table with a compatible type, and names may not differ only by case. A write that breaks a rule fails before anything is committed, so the table keeps its schema. Columns of the table that the data does not have are filled with null.

### How do I add a new column to a Delta table?

Either write the data with .option("mergeSchema", "true"), which adds the data's new columns to the table as part of that write, or run ALTER TABLE name ADD COLUMNS (coupon STRING) first. In both cases the rows already in the table read null in the new column, and no files are rewritten.

### What is the difference between mergeSchema and overwriteSchema?

mergeSchema adds new columns to the table's schema and keeps the rows it has. overwriteSchema, used with mode("overwrite"), replaces the table's schema with the data's, so it can drop a column or change a type, and rewrites the table. Neither is a session-wide setting you should leave on.

### Why did my MERGE not add a new column?

Without schema evolution, MERGE with UPDATE SET * and INSERT * uses only the columns the table has and ignores the source's other columns, with no error. Write MERGE WITH SCHEMA EVOLUTION INTO, or call .withSchemaEvolution() on the Python merge builder, or set spark.databricks.delta.schema.autoMerge.enabled for the session.

## Sources

- [Table batch reads and writes, schema validation and schema updates](https://docs.delta.io/latest/delta-batch.html)
- [Table deletes, updates, and merges, schema evolution](https://docs.delta.io/latest/delta-update.html)
- [Update table schema](https://docs.databricks.com/aws/en/delta/update-schema)
- [Schema enforcement](https://docs.databricks.com/aws/en/tables/schema-enforcement)