`MERGE INTO` in Spark SQL

Description

This lesson teaches MERGE INTO's syntax on an Iceberg table, verified literally against Apache Iceberg's official documentation — the same reference this guide's DESIGN doc cites as this module's foundation. It doesn't apply the statement to P002's concrete case yet — that's lesson 5's job — this lesson stays with the general structure, the same one you're going to recognize, with small adjustments, in any modern SQL engine that supports MERGE.

Connection to the module. Lesson 3 left something pending, stated bluntly: this guide's environment can't really run MERGE INTO against the local catalog, because of the real incompatibility between iceberg-spark-runtime-4.0 and Spark 4.1 onward. This lesson respects that limitation — all the SQL here is marked as "What to expect (representative)", verified against the official documentation, not executed in this environment. Lesson 5 applies this same syntax to Kiosko's real case, with the same label.

An analogy: the counter's form, with its fixed fields

Lesson 1's bank counter has a form with a fixed structure, no matter what transaction you're doing: first you identify the account (ON), then you declare what to do if the account already exists (WHEN MATCHED), and finally what to do if it doesn't (WHEN NOT MATCHED). MERGE INTO is, precisely, that form: a three-part structure, always in the same order, that any modern SQL engine — DuckDB, Spark, Snowflake, BigQuery — implements with the same general shape, even though the fine details vary from engine to engine.

The syntax, quoted literally from Iceberg's official documentation

The official "Spark Writes" documentation describes MERGE INTO with this exact structure:

MERGE INTO prod.db.target t   -- a target table
USING (SELECT ...) s          -- the source updates
ON t.id = s.id                -- condition to find updates for target rows
WHEN ...                      -- updates

Three pieces, in fixed order. The target — the table you're going to modify — and the source — the query that brings in new or changed rows — joined by an ON condition that works exactly like a JOIN's condition: for each source row, it looks for a matching target row.

WHEN MATCHED — updating (or deleting) what already exists

WHEN MATCHED AND s.op = 'delete' THEN DELETE
WHEN MATCHED AND t.count IS NULL AND s.op = 'increment' THEN UPDATE SET t.count = 0
WHEN MATCHED AND s.op = 'increment' THEN UPDATE SET t.count = t.count + 1

Notice something important, quoted literally from the documentation: you can have several WHEN MATCHED clauses, each with its own additional condition, and "the first matching expression is used" — the order you write them in matters, exactly like a chain of if/elif in Python.

WHEN NOT MATCHED — inserting what didn't exist

WHEN NOT MATCHED THEN INSERT *

INSERT * inserts all of the source's columns, as is, for any row that found no match in the target. It also supports additional conditions and explicit column lists:

WHEN NOT MATCHED AND s.event_time > still_valid_threshold THEN INSERT (id, count) VALUES (s.id, 1)

WHEN NOT MATCHED BY SOURCE — what exists in the target but not in the source

Spark 3.5 added a third branch, which Kiosko doesn't need in this module, but it's worth naming because a real production catalog almost always needs it:

WHEN NOT MATCHED BY SOURCE THEN UPDATE SET status = 'invalid'

This branch solves the opposite case from WHEN NOT MATCHED: rows that were already in the target, but that this run's source doesn't mention at all — for example, if Kiosko discontinued P004 entirely and stopped including it in any future catalog export. This guide doesn't implement it in lesson 5, because Kiosko's catalog, throughout this whole module, keeps exactly the same four products — none gets added, none gets discontinued — so that branch would never fire with this exercise's data.

A hard rule, unlike overwrite(): only one match per row

The documentation is explicit about something worth marking in red: "Only one record in the source data can update any given row of the target table, or else an error will be thrown." If your source had, by mistake, two rows with the same product_id, MERGE INTO doesn't pick one at random or combine them — it fails, with an explicit error. This is the same grain discipline — one row per product — you already saw in module 3: dim_product should never have two candidates for the same product_id in a single run.

Compared with the DuckDB MERGE INTO you recalled in lesson 2

Notice something that changes, and something that doesn't, compared with DuckDB's MERGE INTO on dim_product_scd:

What doesn't change: the three-part structure — target/source/ON — and the WHEN MATCHED/WHEN NOT MATCHED vocabulary are practically identical between the two engines. If you know how to write a MERGE INTO in DuckDB, you already know how to read one in Spark.

What does change, and it's this module's central difference: DuckDB's MERGE, in data-modeling, needed a separate follow-up INSERT, because dim_product_scd preserves every historical version as a row — "changing P002" there meant closing one row and opening another, two actions a MERGE can't do for the same source row in a single statement. The MERGE INTO you're going to apply in lesson 5, on an Iceberg table with no history column at all, solves the whole change with a single branch: WHEN MATCHED THEN UPDATE, with no extra step. The history, in this case, doesn't live in new rows — it lives in the snapshots Iceberg archives automatically, outside the table, exactly the same principle all of module 3 already established.

Diagram: a MERGE INTO's anatomy

flowchart TD
    T["target\n(the table getting modified)"] --> ON{"ON t.id = s.id\n(like a JOIN's condition)"}
    S["source\n(the new or changed rows)"] --> ON
    ON -->|"matches"| WM["WHEN MATCHED ...\nUPDATE or DELETE\n(the first matching condition wins)"]
    ON -->|"no match, exists only in source"| WNM["WHEN NOT MATCHED ...\nINSERT"]
    ON -->|"no match, exists only in target\n(Spark 3.5+, not used in Kiosko)"| WNMBS["WHEN NOT MATCHED BY SOURCE ...\nUPDATE or DELETE"]

Why this lesson can't show executed output

Any real MERGE INTO on an Iceberg table produces a new snapshot when it runs successfully — the official documentation even specifies, from Spark 4.1 onward, a set of fields that snapshot's summary can include: spark.merge-into.num-target-rows-updated, spark.merge-into.num-target-rows-inserted, among others, precisely counting how many rows each branch touched. This guide can't show you those numbers as literal output, for the exact reason lesson 3 documented with evidence: this guide's environment (iceberg-spark-runtime-4.0_2.13 against pyspark==4.2.0) can't yet run any Iceberg catalog operation. All of this lesson's SQL is verified, line by line, against the current official documentation — but none of this lesson's output is a real run. Lesson 5 applies this syntax to P002's concrete case, with the same explicit label.

Common mistakes

Writing WHEN MATCHED THEN UPDATE SET * expecting it to update all columns, just like INSERT *. What happens: someone, familiar with INSERT * from the WHEN NOT MATCHED section, assumes an equivalent UPDATE SET * exists to update all columns without listing them. Why it happens: the symmetry between INSERT and UPDATE seems natural. How to spot it: if your MERGE INTO fails with a syntax error on UPDATE SET *, revisit the exact syntax quoted in this lesson — UPDATE SET always needs an explicit list of assignments (column = value), column by column. How to fix it: write every assignment explicitly — UPDATE SET t.category = s.category, t.unit_cost = s.unit_cost — exactly as you're going to see in lesson 5 for dim_product's case.

Assuming WHEN NOT MATCHED BY SOURCE is mandatory in any MERGE INTO. What happens: someone, after reading about the three possible branches, adds WHEN NOT MATCHED BY SOURCE to their MERGE "for completeness," without really needing it. Why it happens: seeing three documented options makes it feel like all three are equally necessary. How to spot it: if your MERGE includes a WHEN NOT MATCHED BY SOURCE branch that never fires — because your source always includes every relevant row from the target — that branch is dead code nobody's ever going to exercise. How to fix it: add WHEN NOT MATCHED BY SOURCE only when your real business case needs it — discontinued products, closed accounts; Kiosko's case in this module, with a fixed four-product catalog, doesn't need it, and lesson 5 doesn't include it.

Exercises

Exercise 1 — Rewrite, from memory, MERGE INTO's three-part structure. Without looking at this lesson, write the general skeleton — MERGE INTO ... USING ... ON ... WHEN ... — describing in your own words what goes in each part.

MERGE INTO <target_table> AS target
USING <change_source> AS source
ON <match_condition, like a JOIN>
WHEN MATCHED THEN UPDATE SET <column = value, ...>
WHEN NOT MATCHED THEN INSERT (<columns>) VALUES (<values>)

Exercise 2 — Explain why MERGE INTO on Iceberg, over dim_product, doesn't need a follow-up INSERT like DuckDB's did. In 2-3 sentences, using what you already know from module 3, explain the difference.

Exercise 3 — Prediction. With this lesson's syntax, write (without running it — there's no way to run it in this environment) the MERGE INTO that would apply P002's change on local.kiosko.dim_product, using local.kiosko.dim_product_staging as the source. Don't look at lesson 5 yet.

MERGE INTO local.kiosko.dim_product AS target
USING local.kiosko.dim_product_staging AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN UPDATE SET
    target.product_name = source.product_name,
    target.category = source.category,
    target.unit_cost = source.unit_cost
WHEN NOT MATCHED THEN INSERT (product_id, product_name, category, unit_cost)
VALUES (source.product_id, source.product_name, source.category, source.unit_cost)

Summary and next step

In this lesson you learned MERGE INTO's general syntax on Iceberg, quoted literally from the official documentation: the three-part structure (target/source/ON), the three possible branches (WHEN MATCHED, WHEN NOT MATCHED, WHEN NOT MATCHED BY SOURCE), and the hard rule that only one source row can match each target row. You contrasted this syntax with lesson 2's DuckDB MERGE, and saw why the Iceberg case needs no follow-up INSERT at all.

Before moving on you should be able to: write MERGE INTO's skeleton from memory; explain the difference between the three branches; and explain why this whole lesson is marked representative, not executed.

Lesson 5 applies exactly this syntax to Kiosko's concrete case: P002's real change, with local.kiosko.dim_product and a dedicated staging table.

Resources

• Apache Iceberg — official documentation, "Spark Writes," MERGE INTO section, literal source of all this lesson's syntax. iceberg.apache.org/docs/latest/spark-writes/#merge-into. In English.
• Apache Iceberg — official documentation, "Spark Writes," "Snapshot summary" section — the spark.merge-into.* fields available from Spark 4.1 onward. iceberg.apache.org/docs/latest/spark-writes. In English.
• data-modeling-for-analytics-guide, "Implementing SCD type 2 with MERGE INTO" lesson — the contrast with DuckDB's MERGE this lesson quotes. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• This same guide, previous lesson (module 6, lesson 3) — source of the real incompatibility that explains why this lesson is representative. 03-setting-up-spark-with-the-iceberg-runtime.md. In Spanish.
• This guide's DESIGN doc — the full map of the eight modules, including module 6's section. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.