Topic 15 of 28 · Expert
Delta Lake & MERGE
ACID tables, upserts, time travel and SCD Type 2.
Concept
Delta Lake adds a transaction log on top of Parquet, giving ACID writes, schema enforcement, time travel and efficient upserts.
MERGE INTO performs upserts: update matched rows, insert new ones — the core of incremental pipelines and SCD (slowly changing dimension) handling.
OPTIMIZE compacts small files and ZORDER clusters data for faster filters; VACUUM removes old files.
Code (PySpark 3.5)
from delta.tables import DeltaTable
target = DeltaTable.forPath(spark, "/lake/customers")
(target.alias("t")
.merge(updates.alias("s"), "t.customer_id = s.customer_id")
.whenMatchedUpdate(set={"city": "s.city", "updated_at": "s.updated_at"})
.whenNotMatchedInsertAll()
.execute())
old = spark.read.format("delta").option("versionAsOf", 3).load("/lake/customers")Remember
- Delta = Parquet + transaction log.
- MERGE for upserts and SCD.
- Time travel via versionAsOf / timestampAsOf.
Interview questions
How do you implement SCD Type 2?
MERGE on the business key: when attributes change, close the current row (end_date, is_current=false) and insert a new current row with the new values.
What problems does Delta Lake solve?
Partial/failed writes, no upserts, schema drift and no history in plain Parquet lakes.
Lesson test
Take an 8-question test on “Delta Lake & MERGE” — concepts, output prediction, bug spotting and interview questions.