Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 36 ยท Delta Lake

MERGE INTO and Upserts in Delta Lake

Master Delta Lake MERGE INTO: upsert new and changed rows, delete matched rows, handle duplicates in the source, use the Python DeltaTable API and apply slowly changing dimensions.

Data pipelines rarely receive a complete fresh copy of a table. More often they get a batch of changes: some rows are new, some are updates to existing rows and some mean "delete this". Applying such a batch is called an upsert (update or insert). In Delta Lake the tool for it is MERGE INTO, which compares a source (the changes) with a target (your table) on a key and applies different actions to matched and unmatched rows, all in one atomic transaction.

Basic upsert

Read-only Databricks SQL:

SQL
MERGE INTO main.sales.customers AS t
USING main.staging.customer_updates AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET t.email = s.email, t.city = s.city, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, city, updated_at)
  VALUES (s.customer_id, s.email, s.city, s.updated_at);

Reading it: for each source row, look for a target row with the same customer_id. If found, update it; if not, insert it. If your column names match, shorthand exists: UPDATE SET * and INSERT *.

Because the merge is one commit, readers see the table before the merge or after it, never in the middle, and a failure leaves the table untouched.

More clauses

You can add conditions and more actions:

SQL
MERGE INTO main.sales.customers AS t
USING main.staging.customer_changes AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'DELETE' THEN DELETE
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'DELETE' THEN INSERT *
WHEN NOT MATCHED BY SOURCE AND t.is_active = true THEN
  UPDATE SET t.is_active = false;
  • WHEN MATCHED can have several clauses with conditions; the first whose condition is true wins.
  • WHEN NOT MATCHED BY SOURCE handles target rows that have no source row (useful when the source is a full snapshot and missing means "gone").
  • A guard such as s.updated_at > t.updated_at prevents old data from overwriting newer data when batches arrive out of order.

The duplicate-source trap

If two source rows match the same target row, Delta cannot know which one to apply and the merge fails with an error about multiple source rows matching. Always deduplicate the source first, keeping the latest row per key:

Python
from pyspark.sql import functions as F
from pyspark.sql.window import Window

w = Window.partitionBy("customer_id").orderBy(F.col("updated_at").desc())
latest = (updates
          .withColumn("rn", F.row_number().over(w))
          .filter("rn = 1")
          .drop("rn"))

(See Joins, aggregations and window functions for row_number.)

The Python API

Python
from delta.tables import DeltaTable

target = DeltaTable.forName(spark, "main.sales.customers")

(target.alias("t")
   .merge(latest.alias("s"), "t.customer_id = s.customer_id")
   .whenMatchedUpdateAll(condition="s.updated_at > t.updated_at")
   .whenNotMatchedInsertAll()
   .execute())

DeltaTable also has .update(), .delete(), .history() and .optimize() for programmatic use.

Performance notes

MERGE must find which files contain matching rows, then rewrite those files. To keep it fast:

  • Put a selective condition in the ON clause (for example the date partition or a clustered column) so Delta can skip files.
  • Keep the source small relative to the target.
  • Compact small files regularly (see OPTIMIZE and schema evolution).
  • Use deletion vectors, which let Delta mark rows rather than rewrite whole files for small changes.

Slowly changing dimension type 2

Sometimes you must keep history: when a customer moves city, you keep the old row with an end date and add a new current row. Merge can do it in two steps: close the old row, then insert the new one.

Stand-in: MERGE on dictionaries

The stand-in implements upsert, conditional delete and the duplicate check on a table keyed by id. It is plain Python, not Delta.

Python Stand-in: upsert and delete by key

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second stand-in applies the SCD type 2 idea with SQLite: close the current row and insert a new current row.

Python Stand-in (SQLite): history rows with end dates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Merging a source that has duplicate keys.
  • Forgetting a recency guard, so a late, older batch overwrites newer data.
  • Using a ON condition with no selective predicate on a very large table, forcing a full scan.
  • Treating NULL keys as matching; NULL = NULL is not true in SQL, so null keys always count as "not matched".

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does MERGE INTO do?

    MERGE compares source and target on a key and applies different actions to matched and unmatched rows atomically.
  2. 2. What happens if two source rows match the same target row?

    Delta cannot decide which source row applies, so the statement fails. Deduplicate the source first.
  3. 3. Which clause handles target rows that have no row in the source?

    NOT MATCHED BY SOURCE targets rows that exist only in the target.
  4. 4. Why add 's.updated_at > t.updated_at' to a MATCHED clause?

    Batches can arrive out of order. The guard keeps the newest version of each row.

Summary

  • MERGE INTO upserts and deletes in a single atomic commit.
  • Deduplicate the source, and guard with timestamps to handle out-of-order data.
  • The Python DeltaTable.merge API mirrors the SQL; selective ON conditions keep it fast.
  • SCD type 2 closes the old row and inserts a new current one.

Next: OPTIMIZE, VACUUM and Schema Evolution