Difficulty: Hard | Concepts: Delta Lake MERGE, Upsert, Schema Evolution, mergeSchema

"How do you handle a source system that adds a new column, and how do you keep a Lakehouse table updated without duplicating rows?" This two-part question is a classic Fabric Lakehouse interview scenario testing both schema drift handling and upsert (MERGE) logic.

Scenario

Your customers_silver Delta table is updated daily from a CRM export. Two things can happen: (1) an existing customer's address changes and must be updated, not duplicated, and (2) the CRM team adds a new column, LoyaltyTier, without warning.

Sample Incoming Data

-- Existing table: customers_silver
CustomerID  Name      City
C001        A. Singh  Mumbai

-- New daily file (address changed + new column)
CustomerID  Name      City      LoyaltyTier
C001        A. Singh  Bengaluru Gold
C002        R. Iyer   Chennai   Silver

Part 1: Upsert with Delta MERGE

from delta.tables import DeltaTable

target = DeltaTable.forName(spark, "customers_silver")
source_df = spark.read.format("delta").load("Files/bronze/customers_daily")

target.alias("t").merge(
    source_df.alias("s"),
    "t.CustomerID = s.CustomerID"
).whenMatchedUpdateAll() \
 .whenNotMatchedInsertAll() \
 .execute()

Part 2: Handling the New Column (Schema Evolution)

source_df.write.format("delta") \
    .mode("append") \
    .option("mergeSchema", "true") \
    .saveAsTable("customers_silver")

-- For MERGE specifically, enable it at the session level:
spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", "true")

Comparison of Options

Approach	Handles updates?	Handles new columns?
Plain overwrite	Yes, but loses history & is wasteful	Yes, but risks silently dropping old columns
MERGE without autoMerge	Yes	No — fails with a schema mismatch error
MERGE + schema.autoMerge.enabled	Yes	Yes — adds LoyaltyTier automatically, backfills old rows with NULL

Visual Representation

Incoming file has extra column: LoyaltyTier | autoMerge.enabled = true | MERGE ON CustomerID MATCHED -> UPDATE all columns (City changes, LoyaltyTier added) NOT MATCHED -> INSERT new row (C002) | Table schema evolves: existing rows get LoyaltyTier = NULL where unknown

Common Mistake to Avoid

Candidates often reach for a blind mode("overwrite") whenever schema changes, which silently destroys row-level history and can drop columns not present in the new batch if overwriteSchema is also set. Always prefer MERGE with autoMerge for incremental, evolving sources — reserve full overwrite for one-time backfills or truly static reference tables.

Conclusion

This question checks two things at once: whether you know Delta MERGE syntax for upserts, and whether you understand schema evolution flags well enough to avoid breaking downstream reports when a source system changes shape without notice — a very real, very common production incident.