Module 6: Merge Into And Native Upserts
Three ways Kiosko already solved this
Description
Before installing anything new, this lesson pauses on something worth keeping fresh, with real code and not a vague summary: Kiosko already solved P002's change three times, with three tools that don't resemble each other at all. This lesson doesn't run any script — it quotes, literally, the exact code and output each previous guide already produced, and puts all three side by side so lesson 3 onward has, precisely, something to compare the fourth and fifth technique against.
Connection to the module. Lesson 1 promised this comparison. This lesson delivers it, with evidence quoted — not invented — from data-modeling-for-analytics-guide, dbt-analytics-engineering-guide, and this very guide's own module 3. None of the three techniques get rerun here: the numbers and output you see below are exactly what those guides already verified by running the real code.
Technique 1 — Hand-written MERGE INTO, on DuckDB (data-modeling-for-analytics-guide, module 4)
data-modeling-for-analytics-guide built dim_product_scd, a table with explicit history columns — valid_from, valid_to, is_current — and used DuckDB's native MERGE INTO to close P002's old version and open the new one. The statement's core, quoted literally from that guide:
def merge_scd(change_date):
result = con.sql(f"""
MERGE INTO dim_product_scd AS target
USING staging_product AS source
ON target.product_id = source.product_id AND target.is_current = true
WHEN MATCHED AND (
target.unit_cost <> source.unit_cost OR
target.category <> source.category
) THEN UPDATE SET
valid_to = DATE '{change_date}' - INTERVAL 1 DAY,
is_current = false
RETURNING merge_action, product_id, category, unit_cost, valid_to, is_current
""")
...
# Second statement, mandatory: opens the new row for the product_ids
# the MERGE above just closed in THIS run.Notice the ON condition: target.product_id = source.product_id alone isn't enough — you need AND target.is_current = true too, because once P002 has more than one version, a MERGE without that filter would find two candidate rows for the same source row. And notice the second statement's comment: MERGE INTO, in DuckDB, only closes rows — it never opens a new one in the same statement — so opening P002's current version with category='health-snacks' needed a follow-up INSERT, run immediately after, precisely filtered by the product_ids the MERGE above had just closed.
That guide's real output, from running the MERGE with P002's real change (quoted literally):
--- MERGE INTO dim_product_scd (change_date = 2026-08-15) ---
┌──────────────┬────────────┬──────────┬───────────┬────────────┬────────────┐
│ merge_action │ product_id │ category │ unit_cost │ valid_to │ is_current │
├──────────────┼────────────┼──────────┼───────────┼────────────┼────────────┤
│ UPDATE │ P002 │ snacks │ 0.6 │ 2026-08-14 │ false │
└──────────────┴────────────┴──────────┴───────────┴────────────┴────────────┘
One row, merge_action='UPDATE' — the row that got closed, not the one that got opened (RETURNING reflects the target's state after the UPDATE, which only touched valid_to/is_current). The final result, after the follow-up INSERT: dim_product_scd with five rows, P002 with two versions (product_key=2 closed, product_key=5 current) — the same pair of numbers (margin=10.8 correct, margin=9.36 broken) you're going to see in the other two techniques.
Technique 2 — dbt snapshot, automated (dbt-analytics-engineering-guide, module 5)
dbt-analytics-engineering-guide solved the exact same problem — close the old version, open the new one — with nobody writing an UPDATE or an INSERT. One declarative configuration file (dim_product_snapshot.sql, with strategy: timestamp, comparing product_updated_at), and a single command:
dbt snapshotThe real output, quoted literally, from running that command a second time — with products_v2.csv already pointed to as the source, the file carrying P002's real change:
Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 1 snapshot, 15 data tests, 4 sources, 501 macros
1 of 1 START snapshot main.dim_product_snapshot ................................ [RUN]
[WARNING]: Data type of snapshot table timestamp columns (TIMESTAMP) doesn't match derived column 'updated_at' (DATE). Please update snapshot config 'updated_at'.
1 of 1 OK snapshotted main.dim_product_snapshot ................................ [OK in 0.12s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
Notice something the dbt guide itself flagged as an important difference: this output doesn't say how many rows changed. Unlike DuckDB's RETURNING, dbt snapshot reports success or failure at the level of the whole resource — to confirm the real effect, that guide had to query the table directly:
┌────────────┬───────────────┬───────────┬─────────────────────┬─────────────────┬───────────────┐
│ product_id │ category │ unit_cost │ product_updated_at │ dbt_valid_from │ dbt_valid_to │
├────────────┼───────────────┼───────────┼─────────────────────┼─────────────────┼───────────────┤
│ P001 │ beverages │ 0.4 │ 2026-08-01 │ 2026-08-01 │ NULL │
│ P002 │ snacks │ 0.6 │ 2026-08-01 │ 2026-08-01 │ 2026-08-15 │
│ P002 │ health-snacks │ 0.68 │ 2026-08-15 │ 2026-08-15 │ NULL │
│ P003 │ beverages │ 0.35 │ 2026-08-01 │ 2026-08-01 │ NULL │
│ P004 │ electronics │ 2.1 │ 2026-08-01 │ 2026-08-01 │ NULL │
└────────────┴───────────────┴───────────┴─────────────────────┴─────────────────┴───────────────┘
Five rows, P002 with two versions — the same result, number for number, that Technique 1 produced with hand-written MERGE INTO. The real difference isn't in the result: it's that nobody wrote a single UPDATE or INSERT — dbt snapshot, with the configuration declared once, decides on its own when to close and when to open, comparing product_updated_at against what's already on file.
Technique 3 — table.overwrite() + time travel, zero columns (this guide, module 3)
This very guide already solved the same change, with a third technique neither of the previous two uses: with no history column at all. kiosko.dim_product, created in module 3 with four columns (product_id, product_name, category, unit_cost, nothing else), first loaded with V1 (P002=snacks/0.60), with that load's snapshot_id captured in snap_v1:
table.append(pa_table_v1)
snap_v1 = table.current_snapshot().snapshot_idAnd then overwritten with V2 (P002=health-snacks/0.68):
table.overwrite(pa_table_v2)No UPDATE, no follow-up INSERT, no declarative configuration at all: two Python calls, and the previous state stays available, intact, with table.scan(snapshot_id=snap_v1).to_arrow(). Module 3 verified, with assert, exactly the same two numbers as the previous two techniques: margin=10.8 (correct, via time travel) and margin=9.36 (broken, without time travel).
Diagram: three techniques, one result
What the engineer has to declare Rows per product correct margin
────────────────────────────────────────────────────────────────────────────────────────────────
1. MERGE (DuckDB) valid_from, valid_to, is_current, 2 (P002 x2) 10.8
the ON with is_current=true,
the follow-up INSERT
────────────────────────────────────────────────────────────────────────────────────────────────
2. dbt snapshot strategy, updated_at column 2 (P002 x2) 10.8
-- not one UPDATE or INSERT written
────────────────────────────────────────────────────────────────────────────────────────────────
3. overwrite + nothing -- 4 business columns, 1 (P002 x1, 10.8
time travel zero history columns recoverable via
snapshot_id)
Notice this table's last column: margin=10.8 repeats three times, without exception — this isn't a coincidence, it's proof that the three techniques solve, through completely different paths, the same business problem. And notice the second-to-last one: Techniques 1 and 2 end up with two P002 rows (one closed, one current) — the history lives inside the table, as additional rows. Technique 3 ends up with a single P002 row — the history lives outside the table, in the snapshots Iceberg archives automatically. Neither way is "more correct" in the abstract — this module's lesson 7, and this guide's whole module 7, come back to this distinction in more detail.
Why this module adds two more techniques, if the problem is already solved three times
It's a fair question: if module 3's table.overwrite() already gives you the correct result, why learn MERGE INTO and upsert? The answer is in how the change arrives, not in the final result. This lesson's three techniques assume you — the engineer — already have, somewhere, the new table's complete, correct state (DIM_PRODUCT_V2 in Python, products_v2.csv in dbt): all four complete rows, three of them identical to before. In a real pipeline, what normally arrives isn't that — it's a delta: a single row from a change feed, a new line in a file some external system exports today, with a single product's updated price. If your only tool is overwrite(), you have to rebuild all four complete rows — including the three that didn't change — before you can write even one. MERGE INTO and upsert solve exactly that case: you hand them only what changed, and they decide, row by row, whether to update or insert — with no need for you to rebuild anything that was already fine.
Common mistakes
Thinking this lesson's three techniques gave different results, and that only one of the three is "the correct one." What happens: someone, on seeing three different tools — DuckDB, dbt, PyIceberg — assumes they must have produced, in some detail, slightly different results, and looks for which of the three numbers is "the real one." Why it happens: it's intuitive to assume different tools give different results, especially if you never saw the three numbers side by side. How to spot it: if you finish this lesson unable to say, from memory, that all three techniques gave margin=10.8, revisit this lesson's comparison table again. How to fix it: the three techniques solve the same business problem — Kiosko has a single real P002 change, with a single effective date — so the correct result is, by definition, the same regardless of the tool. What varies among the three is the internal mechanics — how many rows are left, which columns have to be declared — never the final business number.
Skipping this lesson because "I already used DuckDB and dbt in previous guides, I don't need it repeated to me." What happens: someone familiar with data-modeling and dbt decides to jump straight to lesson 3 without reading this comparison. Why it happens: each individual technique already felt solved at the time, so revisiting them again feels redundant. How to spot it: if you can't explain, without looking back, why Technique 1 needed a follow-up INSERT and Technique 3 needed none, you're missing this lesson — it isn't decorative repetition, it's the basis of comparison lesson 7 is going to use to give a real criterion. How to fix it: this lesson doesn't rerun anything — it quotes, literally, each previous guide's exact code. That contrast is what makes MERGE INTO and upsert, in the lessons that follow, feel like a fourth and fifth piece of the same puzzle, not unconnected new tools.
Exercises
Exercise 1 — Fill in the comparison table yourself. Without looking at this lesson's table, write from memory: how many P002 rows does each of the three techniques leave at the end, and what did the engineer have to declare in each case?
See solution
Technique 1 (DuckDB MERGE): 2 P002 rows (one closed, one current); had to declare valid_from/valid_to/is_current in the schema, the is_current=true condition in the ON, and a follow-up INSERT. Technique 2 (dbt snapshot): 2 P002 rows; had to declare strategy: timestamp and the updated_at column, with no UPDATE/INSERT written. Technique 3 (overwrite + time travel): 1 P002 row; didn't have to declare any history column — the history lives in the snapshots, not in additional rows.
Exercise 2 — Explain, in your own words, why DuckDB's MERGE INTO needed a follow-up INSERT, and why that's going to be different in Iceberg's MERGE INTO, which you're going to see in lessons 4 and 5. Hint: think about how many rows per product each table ends up with.
See solution
DuckDB's MERGE INTO, in data-modeling, operates on a table that preserves every historical version as a separate row (dim_product_scd, SCD type 2) — so "changing P002" precisely means "close the current row AND create a new row," two actions DuckDB's MERGE INTO can't do for the same source row in the same statement (a source row triggers, at most, one branch). The MERGE INTO you're going to use on Iceberg, instead, is going to operate on dim_product with no history column at all — so "changing P002" simply means "update the single row that exists for that product," a single action (WHEN MATCHED THEN UPDATE), with no need for any follow-up INSERT. The difference isn't in MERGE INTO as a tool — it's in whether the target table keeps history as rows (needs the extra step) or doesn't keep it at all (doesn't need it).
Exercise 3 — Prediction. With what you already know from this lesson's three techniques, predict: what do you expect PyIceberg's table.upsert() — the fifth technique, which you're going to really run in lesson 6 — to report about how many rows it updated and how many it inserted, for P002's real change?
See solution
Since P002 already exists in the target table (it's a known product, only its category and cost change, no new product shows up), the expectation is that upsert() reports exactly one row updated and zero rows inserted — the same "a single row changes" pattern you already saw in this lesson's Technique 3. Lesson 6 confirms this with real executed evidence: UpsertResult(rows_updated=1, rows_inserted=0).
Summary and next step
In this lesson you walked through, with literally quoted code — without running anything new — the three ways Kiosko already solved P002's change: hand-written MERGE INTO on DuckDB (data-modeling, with a follow-up INSERT), automated dbt snapshot (dbt, with no explicit UPDATE/INSERT), and table.overwrite() + time travel (this guide, module 3, with no history column at all). All three reach the same business result, margin=10.8. And you saw why this module adds two more techniques: not because the result is wrong, but because this lesson's three techniques assume you already have the table's complete state — MERGE INTO/upsert solve the far more common production case of a partial delta.
Before moving on you should be able to: name the three techniques, with each one's source guide; explain why Technique 1 needed a follow-up INSERT and the other two didn't; and explain, in your own words, the difference between "having the complete state" and "having a delta."
Lesson 3 leaves theory behind for the rest of the module: it really installs the Iceberg runtime for Spark, the only piece of new infrastructure this module needs.
Resources
data-modeling-for-analytics-guide, "Implementing SCD type 2 with MERGE INTO" lesson (module 4) — literal source of Technique 1's code and output.src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.dbt-analytics-engineering-guide, "Changing the source and snapshotting again" lesson (module 5) — literal source of Technique 2's code and output.src/guides/dbt-analytics-engineering-guide/DISENO.md. In Spanish.- This same guide, module 3, lessons 2 and 3 — source of Technique 3 (
table.overwrite()+snap_v1).src/guides/lakehouse-and-iceberg-guide/workbook/module-03-snapshots-and-time-travel/. In Spanish. - DuckDB — official documentation for the
MERGE INTOstatement, the syntax reference Technique 1 implements. duckdb.org/docs/lts/sql/statements/merge_into. In English. - dbt Developer Hub — "Add snapshots to your DAG," the
dbt snapshotreference Technique 2 implements. docs.getdbt.com/docs/build/snapshots. In English. - 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.