Running P002's change as a MERGE

Description

This lesson applies lesson 4's MERGE INTO syntax to the concrete case running through this whole guide: P002 Energy Bar changes from category='snacks', unit_cost=0.60 to category='health-snacks', unit_cost=0.68. You're going to create local.kiosko.dim_product from scratch, in Spark's local catalog — a new table, independent of the kiosko.dim_product PyIceberg used in modules 1 through 5 — load it with the V1 state, and apply the change with a single MERGE INTO, using a staging table that contains only the row that changed.

Connection to the module. Lesson 3 documented, with real evidence, that this environment can't run Iceberg catalog operations against Spark 4.2.0. This lesson explicitly inherits that limitation — all the SQL here is marked "What to expect (representative)" — the exact syntax you'd run, verified against the official documentation, with output whose structure and values are what that MERGE would really produce, but not a run captured in this environment.

The material: a new table, a staging with only the delta

Unlike lesson 2's three techniques — which receive the new table's complete state — this lesson uses a staging table that contains only the row that changed. That's, deliberately, the most common pattern in a real production pipeline: a change feed, or a daily export from Kiosko's catalog system, almost never brings "all four products again" — it brings, precisely, what changed since yesterday.

Step 1 — Create local.kiosko.dim_product, and load it with V1

-- What to expect (representative): syntax verified against Iceberg's
-- official documentation ("Getting Started" / "Spark DDL"), not executed in this environment.

CREATE NAMESPACE IF NOT EXISTS local.kiosko;

CREATE TABLE local.kiosko.dim_product (
    product_id   STRING,
    product_name STRING,
    category     STRING,
    unit_cost    DOUBLE
) USING iceberg;

INSERT INTO local.kiosko.dim_product VALUES
    ('P001', 'Bottled Water 600ml',   'beverages',   0.40),
    ('P002', 'Energy Bar',            'snacks',      0.60),
    ('P003', 'Instant Coffee Sachet', 'beverages',   0.35),
    ('P004', 'Phone Charger Cable',   'electronics', 2.10);

Notice two things. First, local.kiosko.dim_product is a new table, created from scratch in the local catalog — never the same physical table as PyIceberg's kiosko.dim_product, even though the last part of the name matches. Second, it starts at V1 — P002 still snacks/0.60 — the exact same starting point module 3's lesson 2 used for kiosko.dim_product, so the MERGE that follows has something real to change.

Step 2 — Create the staging, with only the delta

CREATE TABLE local.kiosko.dim_product_staging (
    product_id   STRING,
    product_name STRING,
    category     STRING,
    unit_cost    DOUBLE
) USING iceberg;

-- Only P002: the row that really changed. P001, P003, P004 don't show up
-- here -- they didn't change, and a real pipeline would almost never export them again.
INSERT INTO local.kiosko.dim_product_staging VALUES
    ('P002', 'Energy Bar', 'health-snacks', 0.68);

A single row. This is, deliberately, different from the staging_product table you saw in DuckDB's MERGE (lesson 2), which did bring all four complete products — because that lesson wanted to demonstrate the MERGE's idempotence against unchanged rows. Here the point is different: showing the "real delta" pattern, the one you're going to encounter more often in a production pipeline.

Step 3 — The MERGE INTO

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);

What to expect (representative). With staging containing only P002, and P002 already existing in the target, the only branch that fires is WHEN MATCHED — once. WHEN NOT MATCHED never gets exercised, because there's no source row without a match in the target. Verified against Iceberg's official documentation (Spark 4.1 onward), the snapshot summary this MERGE would produce would include, with these logical values for this concrete case — not a captured run:

spark.merge-into.num-target-rows-copied            = 3   -- P001, P003, P004: unchanged
spark.merge-into.num-target-rows-updated            = 1   -- P002: the only row that changed
spark.merge-into.num-target-rows-inserted           = 0   -- no new row
spark.merge-into.num-target-rows-deleted            = 0   -- no DELETE in this MERGE
spark.merge-into.num-target-rows-matched-updated    = 1   -- confirms: the 1 above came from WHEN MATCHED

And the business result, querying the table after the MERGE:

SELECT * FROM local.kiosko.dim_product ORDER BY product_id;

What to expect (representative):

product_id | product_name           | category      | unit_cost
-----------+------------------------+---------------+----------
P001       | Bottled Water 600ml    | beverages     | 0.40
P002       | Energy Bar             | health-snacks | 0.68
P003       | Instant Coffee Sachet  | beverages     | 0.35
P004       | Phone Charger Cable    | electronics   | 2.10

Four rows, P002 with the new value, P001/P003/P004 intact — exactly the same business result lesson 2's three techniques already produced, with one central mechanical difference: nobody had to rebuild the three unchanged rows in Python or a CSV — the staging only brought the delta, and MERGE INTO took care of identifying that the target's other three rows had no match to update, leaving them exactly as they were.

Diagram: one row comes in, one row changes

flowchart LR
    subgraph staging["local.kiosko.dim_product_staging"]
        S1["P002 health-snacks 0.68\n(the only row)"]
    end
    subgraph target["local.kiosko.dim_product BEFORE"]
        T1["P001 beverages 0.40"]
        T2["P002 snacks 0.60"]
        T3["P003 beverages 0.35"]
        T4["P004 electronics 2.10"]
    end
    S1 -->|"ON product_id matches"| T2
    T2 -->|"WHEN MATCHED\nUPDATE"| R2["P002 health-snacks 0.68"]
    T1 -.->|"no match in source\nstays the same"| R1["P001 beverages 0.40"]
    T3 -.->|"no match in source\nstays the same"| R3["P003 beverages 0.35"]
    T4 -.->|"no match in source\nstays the same"| R4["P004 electronics 2.10"]

Going deeper: why a single-product staging is more realistic than the complete state

It's worth pausing on something that separates this lesson from lesson 2's three techniques: data-modeling and dbt each received a source that brought all four products, with three of them identical to the previous version — that pattern works well when the complete source is small and cheap to regenerate (a CSV file, a Python dict), but it doesn't scale when the dimension has thousands or millions of rows. In that case, exporting "the whole catalog, again" on every run would be enormously more expensive than exporting just what changed. The single-row staging this lesson uses is the pattern that does scale: any system that produces a change feed — real CDC, an event queue, an incremental export — naturally delivers only the deltas, and MERGE INTO, with its WHEN MATCHED/WHEN NOT MATCHED, is designed exactly to consume that kind of source without anyone having to rebuild the rest of the table.

Common mistakes

Including the three unchanged rows in the staging, "just in case." What happens: someone, used to lesson 2's data-modeling/dbt pattern, includes all four complete rows in dim_product_staging, instead of just P002. Why it happens: it seems safer to "bring everything" than to trust the MERGE to correctly handle a partial source. How to spot it: it isn't an error that breaks anything — MERGE INTO handles a staging with all four rows perfectly fine, it would simply find three matches with no real value change and "update" them with the same values they already had — but it's a missed opportunity to show the pattern that does scale. How to fix it: if your real change source only brings what changed — the most common case in production — let the staging faithfully reflect that; don't artificially rebuild unchanged rows just out of habit.

Forgetting WHEN NOT MATCHED never fires in this concrete case, and expecting to see num-target-rows-inserted > 0. What happens: someone, familiar with lesson 4's general MERGE INTO structure — which includes WHEN NOT MATCHED THEN INSERT — expects to see some row inserted in this specific example, even though P002 already existed in the target. Why it happens: seeing a WHEN NOT MATCHED clause in the SQL makes it feel like it should get exercised somewhere. How to spot it: if you expect num-target-rows-inserted > 0 for this concrete case, review Step 2's staging — it contains only P002, a product that already exists in target, so every source row finds a match, and WHEN NOT MATCHED never fires. How to fix it: the WHEN NOT MATCHED clause is there for the general case — a new, never-before-seen product — not because this specific case needs it; to see that branch in action, you'd have to add a product_id to the staging that doesn't yet exist in target.

Exercises

Exercise 1 — Rewrite the staging to include a hypothetical new product, and predict the result. Add a row ('P005', 'Reusable Tote Bag', 'accessories', 1.20) to this lesson's staging, alongside P002's. Without running it, predict: what values would num-target-rows-updated and num-target-rows-inserted have now?

See solution

num-target-rows-updated = 1 (still only P002, the only row that matches and changes value) and num-target-rows-inserted = 1 (P005's row, which has no match at all in target, fires WHEN NOT MATCHED THEN INSERT). The final result would have five rows in local.kiosko.dim_product, not four. Note: this P005 is purely hypothetical, for this exercise — Kiosko's real catalog, throughout this whole guide, keeps exactly four products.

Exercise 2 — Explain why this lesson uses ON target.product_id = source.product_id, with no additional condition, unlike DuckDB's ON in lesson 2. In 2-3 sentences, explain the difference.

See solution

DuckDB's ON in data-modeling needed AND target.is_current = true, because dim_product_scd can have several rows for the same product_id — one per historical version — and without that additional filter, the MERGE would find more than one match for the same source row (an error, per lesson 4's hard rule). local.kiosko.dim_product, in this lesson, never has more than one row per product_id — it has no history columns — so target.product_id = source.product_id is, on its own, already a condition that guarantees at most one match. No additional filter is needed because there's no ambiguity to resolve.

Exercise 3 — Prediction: what would happen if you ran this same MERGE INTO a second time, without changing the staging? Without running it, predict: if you run Step 3's MERGE twice in a row, with the same staging (only P002, health-snacks/0.68), what do you expect to see in num-target-rows-updated the second time?

See solution

With this lesson's exact syntax — WHEN MATCHED THEN UPDATE SET with no additional condition comparing values — the second run would keep reporting num-target-rows-updated = 1, even though the values are already identical: the WHEN MATCHED clause, as written, doesn't check whether anything really changed, it only checks that there's a match. This is different from DuckDB's MERGE in lesson 2, which did include an explicit condition (AND (target.unit_cost <> source.unit_cost OR ...)) to avoid "updating" rows with no real change — and also different from PyIceberg's table.upsert(), which you're going to see in lesson 6, which does that comparison internally. If you wanted this MERGE INTO to be just as selective, you'd have to add the same explicit condition to WHEN MATCHED that you already saw in DuckDB's MERGE.

Summary and next step

In this lesson you applied MERGE INTO's syntax to P002's real case: a new local.kiosko.dim_product table, loaded with V1, and a staging that brings only the delta — a single row, P002 in its new state. The MERGE updates that single matching row and leaves the other three intact, with no follow-up INSERT, with no history column at all. All of this lesson's output is marked as representative, verified against Iceberg's official documentation, not executed in this environment, because of the real incompatibility lesson 3 documented.

Before moving on you should be able to: write the complete MERGE INTO for P002's case from memory; explain why a single-row staging is more realistic than the table's complete state; and explain the difference between this MERGE (which updates without checking whether the value changed) and DuckDB's (which does check).

Lesson 6 shows the 100% Python alternative: PyIceberg's table.upsert(), with no SQL at all, really run in this environment — without the Spark incompatibility that limited these two lessons.

Resources

• Apache Iceberg — official documentation, "Spark Writes," MERGE INTO section, source of the exact syntax this lesson applies to Kiosko's case. iceberg.apache.org/docs/latest/spark-writes/#merge-into. In English.
• Apache Iceberg — official documentation, "Spark DDL," CREATE TABLE ... USING iceberg syntax. iceberg.apache.org/docs/latest/spark-ddl. In English.
• This same guide, 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.