Module overview: `MERGE INTO` and native upserts

Why this module exists

This guide's module 3 solved P002's change with a technique no previous module in the ecosystem had available: table.overwrite() on kiosko.dim_product — no valid_from, no valid_to, no is_current — and table.scan(snapshot_id=snap_v1) to recover the previous state. It worked, with executed evidence: P002's correct margin (10.8) came out exactly the same as in data-modeling-for-analytics-guide and dbt-analytics-engineering-guide, with nobody designing a single history column.

But notice something module 3 deliberately left unsolved: table.overwrite() replaces the whole table. It works perfectly when you have, in Python, the complete state of all four products — DIM_PRODUCT_V2, hand-written, with all four complete dicts. But in a real pipeline, what normally arrives isn't "the dimension's complete state," but a change: a row from a CDC feed, a line from a file Kiosko's catalog system exported today, with a single product's new price. If your only tool is overwrite(), you have to rebuild all four complete rows every time — including the three that didn't change — just to be able to write the one that did. That is, precisely, the problem this module solves: applying a partial change, without rebuilding the entire table, and without losing the one-row-per-product grain.

This isn't a new problem for the ecosystem. You already solved it, twice, with two completely different tools: data-modeling-for-analytics-guide (module 4) hand-wrote a MERGE INTO on DuckDB, comparing a staging table against dim_product_scd in a single statement. dbt-analytics-engineering-guide (module 5) automated that exact same idea with dbt snapshot, with nobody writing an UPDATE or an INSERT. And this very guide, in its module 3, solved Kiosko's favorable case with a third technique still: pure time travel, zero columns. This module puts all three side by side, with literally quoted code — without rerunning any of them — and adds a fourth: MERGE INTO, now native to the table format itself, run with real SQL over Iceberg via Spark. And a fifth, sibling to the fourth: PyIceberg's table.upsert(), the same idea without a single line of SQL.

The case that runs through the module: P002's same change, a fourth and a fifth time

This module doesn't invent a new case. It's, again, P002 Energy Bar: category='snacks', unit_cost=0.60 becomes category='health-snacks', unit_cost=0.68, in effect since 2026-08-15 — the same change, with the same two numbers (margin=10.8 correct, margin=9.36 broken) you've already seen three times in this ecosystem. What changes here is the vehicle: instead of rewriting the whole table (module 3) or a manual UPDATE/INSERT on DuckDB (data-modeling) or a declarative dbt command (dbt), this module applies the change as what it really is in any production pipeline — a delta, a single row with P002's new value, merged against the existing state in a single transactional command.

Two new pieces of vocabulary, both in English, join the Iceberg lexicon you already know: MERGE INTO, the SQL statement Spark inherits almost verbatim from any modern relational engine, adapted to write onto an Iceberg table; and upsert, the generic name — from the whole industry, not just Iceberg — for the "update if it exists, insert if it doesn't" operation, which PyIceberg exposes as a single Python method: table.upsert().

An analogy: the bank counter that runs a single transaction

Imagine you have to update a piece of data on your bank account — say, your phone number — and you don't know whether the bank already has a record of you or whether you're a new customer. The old way of handling this, at a badly designed bank, is two separate windows: one for "open new account" and another for "update existing account," and you have to know, in advance, which one is yours — and if you pick the wrong window, the transaction fails or, worse, creates a duplicate account.

MERGE INTO and upsert are the redesigned counter: a single window. You hand over your ID — your product_id, in Kiosko's case — and the system decides on its own, in the same transaction, whether it has to update a record that already exists or create a new one. You never declare which of the two cases is yours; the counter figures it out by comparing what you handed over against what it already has on file. WHEN MATCHED is "you're already a customer, I'll update your record"; WHEN NOT MATCHED is "you're new, I'll sign you up" — the two branches of the same transaction, never two separate procedures someone has to sequence by hand.

Diagram: five vehicles, one destination

flowchart TB
    P["P002's change:\nsnacks/0.60 -> health-snacks/0.68"]

    P --> A["1. data-modeling (M4):\nhand-written MERGE INTO on DuckDB\nvalid_from/valid_to/is_current"]
    P --> B["2. dbt (M5):\ndbt snapshot\ndbt_valid_from/dbt_valid_to/dbt_scd_id"]
    P --> C["3. this guide (M3):\ntable.overwrite() + time travel\nZERO history columns"]
    P --> D["4. this guide (M6, Spark):\nIceberg's native MERGE INTO\nreal SQL via iceberg-spark-runtime"]
    P --> E["5. this guide (M6, PyIceberg):\ntable.upsert()\npure Python, no SQL"]

    A -.->|"recalled, NOT rerun"| L2["This module's lesson 2"]
    B -.->|"recalled, NOT rerun"| L2
    C -.->|"recalled, NOT rerun"| L2
    D -.->|"executed / representative -- L3 to L5"| L2
    E -.->|"really executed -- L6"| L2

The map of this module

Lesson    What it solves
────────  ──────────────────────────────────────────────────────────────
L1        (this one) Why this module exists, and the five ways to
          solve the same change, side by side
L2        The three ways Kiosko ALREADY solved this -- DuckDB MERGE, dbt
          snapshot, time travel -- recalled with literally quoted code,
          without rerunning any of them
L3        Installing Spark with the Iceberg runtime -- this whole
          guide's only JVM dependency, stated that explicitly
L4        MERGE INTO in Spark SQL -- the general syntax, verified
          against the official documentation
L5        Running P002's change as a MERGE -- Kiosko's concrete
          case, on Spark's local catalog
L6        PyIceberg's table.upsert() -- the 100% Python alternative,
          really executed, UpsertResult with rows_updated=1
L7        Choosing between MERGE in SQL and upsert in Python -- the
          criterion, not the fashion
L8        Project: Kiosko's native upsert, end to end

Lessons 2 through 7 follow a deliberate progression: first the past (lesson 2, without running anything new), then the infrastructure this module needs and no other module in this guide requires (lesson 3), then MERGE INTO's general syntax (lesson 4) before applying it to P002's concrete case (lesson 5), then the pure-Python alternative (lesson 6), and finally the criterion for choosing between the two (lesson 7). Lesson 8 integrates this module's executable result into a single project.

A technical warning, stated from here

This is the only one of this guide's eight modules that needs the JVM (Java Virtual Machine). Modules 1, 2, 3, 4, 5, 7, and 8 run entirely on pure PyIceberg — no Java, no Spark, no external process at all. This module reuses the PySpark 4.2.0 + Java 17 that spark-and-distributed-processing-guide already left installed in its module 1, lesson 4 — the same JAVA_HOME pointing at the same JDK — specifically to run MERGE INTO with real SQL against an Iceberg table. If you don't have that environment set up, this module's lesson 3 shows you, step by step, how to verify it and what to do if it's missing.

And a second warning, just as explicit: the catalog Spark uses in this module — named local, following the official Iceberg documentation's literal convention — is a catalog physically distinct from the kiosko catalog PyIceberg used in modules 1 through 5. The two catalogs live in different directories, with different registration mechanisms (hadoop for one, sql/SQLite for the other), and this guide never claims both engines read or write the same physical table — that would require actually verifying it, and this guide doesn't. You're going to see P002's change reproduced twice, in two independent catalogs, each with its own kiosko.dim_product — not a table shared between Spark and PyIceberg.

The boundary: what does NOT belong in this module

This module doesn't orchestrate anything: lesson 5's MERGE INTO gets run by hand, from a terminal or a script — triggering that same command from a scheduled DAG, with managed retries, is airflow-and-declarative-orchestration-guide's territory, named here without being implemented. It isn't a distributed computing lesson either: Spark shows up in this module solely as the SQL client that knows how to speak MERGE INTO against Iceberg — partitioning, shuffle, Catalyst, all of that lives in spark-and-distributed-processing-guide, and this module doesn't touch it. And P002's change still arrives, in this module, as a fixed value declared in Python or a hand-built staging table — not as a real event from a transactional system: the exact moment that should become real CDC is named in module 8, lesson 7, and belongs to streaming-with-kafka-and-flink-guide.

Common mistakes

Thinking this module "undoes" module 3's work, because now there's a "better" way to solve P002's change. What happens: someone, on seeing this module adds two new techniques for the same change, assumes module 3's table.overwrite() + time travel became obsolete, or that it should have been done this way from the start. Why it happens: it's natural to assume the most recent technique you learn is the one that replaces the earlier ones. How to spot it: if you finish this module thinking table.overwrite() no longer makes sense for anything, revisit lesson 7 — the criterion for choosing among Iceberg's four techniques (full overwrite, time travel, MERGE INTO, upsert) depends on how the change arrives, not on which one "sounds" more advanced. How to fix it: table.overwrite() is still the right tool when you already have, in memory, a small table's complete state — exactly module 3's case; MERGE INTO/upsert are the right tool when what arrives is a partial delta, the most common case in a real production pipeline. Neither replaces the other — each solves a different entry point.

Assuming that, because MERGE INTO and upsert don't need history columns, they also don't need to think about row identity. What happens: someone tries to run a MERGE INTO or an upsert without clearly defining which column uniquely identifies each row of the target — the equivalent of a primary key — and discovers, with an error or an incorrect result, that the operation needed that information. Why it happens: since Iceberg doesn't force you to declare a formal primary key in the table's schema — unlike a classic relational database — it's easy to assume row identity is optional. How to spot it: this module's lesson 6 shows you, with a real error, exactly this case: table.upsert() without indicating the join column fails with an explicit message. How to fix it: both MERGE INTO's ON and upsert()'s join_cols always need a column (or combination of columns) that uniquely identifies each row of the target — in Kiosko, product_id, the same natural key you already used in the ecosystem's three earlier techniques.

Exercises

Exercise 1 — Name, from memory, the three ways Kiosko already solved P002's change before this module. Without looking back, name the three techniques, with the guide and the module each one came from.

See solution

1. Hand-written MERGE INTO on DuckDB, with valid_from/valid_to/is_current columns — data-modeling-for-analytics-guide, module 4. 2. dbt snapshot, with dbt_valid_from/dbt_valid_to/dbt_scd_id columns generated automatically — dbt-analytics-engineering-guide, module 5. 3. table.overwrite() + time travel (table.scan(snapshot_id=...)), with no history column at all — this very guide, module 3. All three reach the same business result (margin=10.8 correct), with completely different infrastructure and discipline.

Exercise 2 — Trace the analogy yourself. In your own words, using this lesson's bank counter analogy, explain what WHEN MATCHED and WHEN NOT MATCHED represent in MERGE INTO.

See solution

WHEN MATCHED is the "you're already a customer of this bank" case: the product_id arriving from the source already exists in the target table, so the correct action is to update the existing record with the new values — never create a duplicate. WHEN NOT MATCHED is the "you're a new customer" case: the source's product_id has no prior record in the target, so the correct action is to sign it up with an INSERT. The key to the analogy is that both branches coexist in the same transaction — the same MERGE INTO statement — with nobody having to decide in advance which of the two applies: the engine itself resolves it by comparing the source against the target.

Exercise 3 — Prediction. Before reading lesson 3: why do you think this guide chooses to reuse spark-and-distributed-processing-guide's PySpark instead of installing a lighter SQL engine — like DuckDB, which you already know from data-modeling — to run MERGE INTO against an Iceberg table?

See solution

There's no single correct answer — it's a prediction exercise — but the real reason, which lesson 3 confirms: Iceberg's native MERGE INTO — the one that understands snapshots, manifest files, and the rest of the anatomy this guide's module 2 taught — needs a compute engine with an official integration with the Iceberg table format. Spark has that integration in a mature, well-documented way (iceberg-spark-runtime, maintained by the Apache Iceberg project itself); DuckDB has its own Iceberg support, but much more limited in 2026, and it isn't this guide's focus. The choice isn't "Spark is better than DuckDB in general" — it's that this specific module needs the engine the industry uses, today, for transactional MERGE INTO against real Iceberg tables.

Summary and next step

In this lesson you saw why this module exists: module 3 solved P002's change with a full overwrite(), but a real pipeline almost never receives "the dimension's complete state" — it receives a delta. You walked through the map of the five ways Kiosko already solved, or is going to solve, that same change, and this module's two central technical warnings: it's the only one that needs the JVM, and it uses two physically distinct catalogs, stated that unambiguously.

Before moving on you should be able to: explain why MERGE INTO/upsert don't replace module 3's table.overwrite(), but solve a different entry point; and name this module's five techniques, in order, with each one's corresponding vehicle.

Lesson 2 doesn't install anything yet — it walks, with literally quoted code from data-modeling and dbt, through the three ways Kiosko already used to solve this same change, without rerunning any of them.

Resources

• Apache Iceberg — official documentation, "Spark Writes," MERGE INTO section — the formal foundation for the syntax this module is going to use in lessons 4 and 5. iceberg.apache.org/docs/latest/spark-writes. In English.
• PyIceberg — API reference, table.upsert() — lesson 6's foundation. py.iceberg.apache.org/api. In English.
• data-modeling-for-analytics-guide DESIGN doc — source of the DuckDB MERGE INTO lesson 2 recalls. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• dbt-analytics-engineering-guide DESIGN doc — source of the dbt snapshot lesson 2 recalls. src/guides/dbt-analytics-engineering-guide/DISENO.md. In Spanish.
• spark-and-distributed-processing-guide DESIGN doc — source of the PySpark 4.2.0 + Java 17 environment lesson 3 reuses. src/guides/spark-and-distributed-processing-guide/DISENO.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.