Schema Evolution with mergeSchema and overwriteSchema

Part 1: Schema Enforcement: Delta Lake's Default Guardrail

Before we can evolve a schema, we must first understand why it's necessary. By default, Delta Lake uses Schema Enforcement to protect the integrity of your tables. It acts as a strict guard, ensuring that any new data written to a table conforms to the existing schema.

This is a feature, not a bug. It prevents data corruption that can occur from accidentally writing data with mismatched columns or incorrect data types.

To get a clear picture of what schema enforcement does and the specific rules it applies, let's watch a segment from a Databricks tech talk.

As the video explains, schema enforcement on write will reject a transaction if the new data:

• Contains any columns that are not in the target table's schema.
• Has columns with data types that don't match the target table's schema.
• Has column names that differ only by case (e.g., UserID vs userid).

However, it will allow a write if the new data is missing columns (as long as those columns in the table are nullable). The missing fields will simply be written as null.

Let's see this in action. The next video clip demonstrates what happens when you try to append data with a new schema to a Delta table without any evolution options.

This failed write is exactly what we want to control. Next, we'll learn how to tell Delta Lake to accept and adapt to these changes.

Part 2: Additive Changes with mergeSchema

The most common type of schema change is the addition of new columns. For this, Delta Lake provides the mergeSchema option. It allows you to perform read-compatible schema changes. This means the schema can evolve in a way that all the old data can still be read correctly with the new schema.

mergeSchema is perfect for:

• Adding one or more new columns.
• Changing a NOT NULL column to be nullable.
• Upcasting data types (e.g., Byte -> Short -> Integer).

Hands-On Lab: Using mergeSchema

Let's apply this. Open a new Databricks notebook and follow these steps.

Step 1: Create a Base Table
First, create a simple Delta table with a few records.

# Define initial data
initial_data = [
    (1, "Jian", "Yang", "Engineer"),
    (2, "Laurie", "Bream", "Manager")
]
columns = ["id", "first_name", "last_name", "role"]
initial_df = spark.createDataFrame(initial_data, columns)




# Write to a Delta table
table_name = "employee_data"
initial_df.write.format("delta").mode("overwrite").saveAsTable(table_name)

print("Initial table created:")
display(spark.table(table_name))

Step 2: Create New Data with an Evolved Schema
Now, let's imagine our source system has started providing a new location column.

# Define new data with an additional column
new_data = [
    (3, "Gavin", "Belson", "CEO", "Palo Alto"),
    (4, "Richard", "Hendricks", "Engineer", "Palo Alto")
]
new_columns = ["id", "first_name", "last_name", "role", "location"]
new_df = spark.createDataFrame(new_data, new_columns)

print("New DataFrame with an extra 'location' column:")
display(new_df)

Step 3: Attempt to Append (and Fail)
If you try to append this new DataFrame directly, schema enforcement will kick in and throw an error.

# This will fail as expected
try:
    new_df.write.format("delta").mode("append").saveAsTable(table_name)
except Exception as e:
    print("Write failed as expected due to schema mismatch:")
    print(e)

Step 4: Append with mergeSchema
Now, let's enable schema evolution using the mergeSchema option.

# This will succeed
new_df.write.option("mergeSchema", "true").mode("append").saveAsTable(table_name)

print("Append successful with mergeSchema!")

Step 5: Verify the Result
Query the table to see the result. The schema has been updated to include the location column, and the original records now have null values for that column.

final_df = spark.table(table_name)
print("Final table with evolved schema:")
display(final_df)

print("\nFinal table schema:")
final_df.printSchema()

This hands-on exercise mirrors the demonstration in the following video clip.

Part 3: Overwriting Schemas with overwriteSchema

What if you need to make more drastic changes that aren't read-compatible? For example:

• Changing a column's data type (e.g., string to integer).
• Renaming a column.
• Dropping a column.

For these scenarios, mergeSchema won't work because the existing data would be unreadable under the new schema. This is where overwriteSchema comes in. It is used in conjunction with mode("overwrite") to completely replace the table's schema and data with the schema and data from the new DataFrame.

Let's see this in a practical context.

Hands-On Lab: Using overwriteSchema

Let's continue with our employee_data table. Suppose we need to refactor our schema: we want to drop the role column and rename first_name and last_name to a single full_name column.

Step 1: Prepare the New DataFrame
Create a DataFrame reflecting the desired new structure.

from pyspark.sql.functions import concat_ws




# Let's read the current table and transform it to the new schema
current_table_df = spark.table(table_name)




# Create the new schema: combine names, drop role and location
# For this example, we'll just create a completely new set of data
# to simulate a full overwrite from a new source.
overwrite_data = [
    (1, "Jian Yang"),
    (5, "Erlich Bachman"),
    (6, "Dinesh Chugtai")
]
overwrite_columns = ["id", "full_name"]
overwrite_df = spark.createDataFrame(overwrite_data, overwrite_columns)

print("DataFrame with the new, incompatible schema:")
display(overwrite_df)

Step 2: Overwrite the Table and Schema
Now, write this DataFrame using both mode("overwrite") and option("overwriteSchema", "true").

overwrite_df.write \
    .option("overwriteSchema", "true") \
    .mode("overwrite") \
    .saveAsTable(table_name)

print("Table and schema overwritten successfully!")

Step 3: Verify the Result
The table now contains only the new data and has the completely new schema.

print("Final table after overwriting schema:")
final_df = spark.table(table_name)
display(final_df)

print("\nFinal table schema:")
final_df.printSchema()

Part 4: Choosing the Right Strategy

You now have two tools for schema evolution. Here's a simple guide for when to use each:

The MERGE use case is extremely common in professional data engineering. The Databricks documentation page "Update table schema" (resource LINK) has an excellent table in the section "Example operations of merge with schema evolution" that compares the behavior of MERGE with and without schema evolution. I highly recommend bookmarking that for future reference.

Conclusion

In this lesson, you've learned how to manage one of the most common challenges in data engineering: evolving data schemas. By understanding and using Delta Lake's schema evolution features, you can build more resilient and adaptable data pipelines.

Key Takeaways:

• Schema Enforcement is Delta Lake's default behavior, protecting your tables from writes that don't match the schema.
• mergeSchema is used for read-compatible, additive changes like adding new columns. It's typically used with append or merge operations.
• overwriteSchema is used for non-read-compatible, destructive changes like changing a data type or dropping a column. It must be used with mode("overwrite") and replaces the entire table schema and data.
• Choosing the correct option is critical for maintaining data integrity while allowing your data architecture to evolve.

Preview of the Next Lesson:
Schema evolution is especially powerful when dealing with incremental data ingestion. In our next lesson, we will dive into Auto Loader, a Databricks feature designed to efficiently and incrementally ingest files from cloud storage. You will see how the schema evolution techniques you learned today can be configured directly within an Auto Loader pipeline to handle schema drift automatically as new files arrive.