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

  1. A DataFrame is written to a Delta table
  2. Delta compares its schema with the table schema
  3. Do columns and types match? Yes: Write succeeds. No: Check whether evolution was requested.
  4. Is mergeSchema or WITH SCHEMA EVOLUTION set? Yes: Add new columns and write. No: Fail with a schema mismatch error.
  5. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic