Choosing between MERGE in SQL and upsert in Python

Description

This module has given you, so far, three ways to write a partial change to an Iceberg table — table.overwrite() with the complete state (module 3), MERGE INTO via Spark (lessons 4 and 5), PyIceberg's table.upsert() (lesson 6) — and recalled two more, outside Iceberg (DuckDB, dbt), in lesson 2. This lesson doesn't add any new technique — it gives the criterion for choosing among the ones you already have, with concrete evidence from this very guide, not a generic "use whatever feels more comfortable" preference.

Connection to the module. Every previous lesson in this module showed one technique in isolation. This lesson puts all three of Iceberg's side by side, with the real differences you already saw run — or tried to run — in lessons 3 through 6.

An analogy: three ways to pay, each with its place

Think of three ways to pay a bill. Paying the full amount, all at once — like table.overwrite() — makes sense when you already know exactly what the total is and writing it in full is just as easy as writing only the difference: fast, no ambiguity, but it requires having the complete number on hand. Asking the bank teller to process an update with a formal form — like MERGE INTO — makes sense when the transaction needs to be documented with clear, auditable syntax, and when you're already standing in front of that teller for another reason (you already have a Spark process running). Using the bank's app to transfer without going to any window — like table.upsert() — makes sense when you want to resolve everything from your own code, without depending on the bank (Spark) being open. None of the three is "the correct one" in the abstract — each one better solves a different situation.

The five criteria, with evidence from this module

1 — Do you have the complete state, or only the delta?

table.overwrite() (module 3) expects the complete state — you gave it all four V2 rows, with three of them identical to V1, because overwrite() replaces 100% with no exception. MERGE INTO (lesson 5) and table.upsert() (lesson 6) are designed for the opposite case: a staging with a single row — only P002 — was enough for lesson 5; lesson 6's upsert() accepted the complete state, but didn't need it — it would have worked the same with a single-row pa_table. If your data source naturally delivers a small delta — a change feed, an incremental export — MERGE INTO or upsert() keep you from rebuilding what didn't change. If your source always gives you a small table's complete state — like Kiosko's four fixed products in Python — overwrite() remains the simplest option, with no real cost.

2 — Do you need to compare values, or just key matching?

Here's the subtlest difference, and the one that most caught expectations off guard in this module. MERGE INTO, as you wrote it in lesson 5, updates any matching row, without checking whether any value actually changed — that lesson's Exercise 3 confirmed it: running it a second time with the same data still reports one row "updated." For MERGE INTO to be selective, you have to add the condition by hand, like data-modeling did with DuckDB: WHEN MATCHED AND (target.unit_cost <> source.unit_cost OR ...). table.upsert(), instead, does that comparison for you, internally, column by column — you confirmed it with real evidence in lesson 6: a second run with the same data reports rows_updated=0, with nobody writing any condition.

3 — Do you need to delete rows, not just update and insert?

MERGE INTO, in its full syntax (lesson 4), supports a WHEN MATCHED ... THEN DELETE branch — you can delete rows from the target as part of the same statement. PyIceberg's table.upsert() has no delete branch at all — its only two possible actions are when_matched_update_all and when_not_matched_insert_all (check the docstring you quoted in lesson 6); there's no when_matched_delete. If your use case needs to, in the same operation, delete products that no longer exist in the source — the WHEN NOT MATCHED BY SOURCE THEN DELETE scenario lesson 4 named without implementing — MERGE INTO in SQL can solve it in a single statement; upsert() can't, and you'd need a separate table.delete() call.

4 — Do you already have a Spark process running, or would you rather not depend on the JVM?

This is the difference this module's lesson 3 made clearer than any other: MERGE INTO needs a live SparkSession, with the Iceberg runtime correctly matched to your exact Spark version — and you already saw, with real evidence, that this version combination can fail to match, and that when it doesn't, nothing runs. table.upsert() doesn't depend on either of those two things: it ran with no friction in lesson 6, in the same environment where MERGE INTO failed. If your organization already has Spark pipelines running daily — the typical case for a high-volume data platform — MERGE INTO fits naturally there. If your case is a Python script, a microservice, or an environment where standing up Spark would be new complexity, upsert() solves the same problem without that dependency.

5 — At what real scale are you going to operate?

MERGE INTO via Spark distributes the work across several executors — designed, precisely, for a staging or target with millions of rows, the same territory spark-and-distributed-processing-guide already covered with fact_orders_at_scale. PyIceberg's table.upsert() runs in a single Python process, over PyArrow — perfectly comfortable for this guide's four-row dim_product, or for dimensions of thousands or even millions of rows on a machine with enough memory, but without a Spark cluster's distributed parallelism. For a fact table (fact_orders_at_scale) with hundreds of millions of rows and a delta that's also large, MERGE INTO via Spark is the tool that scales; for a moderately sized dimension, upsert() is simpler, with no correctness sacrificed.

Comparison table: Iceberg's three techniques, side by side

Criterion	table.overwrite() (M3)	MERGE INTO via Spark (M6 L4-L5)	table.upsert() (M6 L6)
Expected input	Complete state	Delta or complete state	Delta or complete state
Compares values automatically	Not applicable (replaces everything)	No — has to be written in WHEN MATCHED AND (...)	Yes, internally
Supports DELETE in the same operation	No (uses a separate table.delete())	Yes, WHEN MATCHED THEN DELETE	No — that branch doesn't exist
Needs Spark / JVM	No	Yes	No
Scales to millions of distributed rows	No (single process)	Yes	No (single process)
Snapshots it produces (verified)	1-2 (delete+append if replacing everything)	1 (overwrite, verified in the official doc)	2 (partial overwrite + append, verified in L6)
Verified in this guide	Executed, module 3	Representative, lessons 4-5	Executed, lesson 6

Diagram: the decision tree

flowchart TD
    A["You need to write a change\nto an Iceberg table"] --> B{"Do you have the COMPLETE\nstate in memory,\nand is the table small?"}
    B -->|"Yes"| C["table.overwrite()\n-- simple, compares nothing"]
    B -->|"No, it's a delta"| D{"Do you already have\na Spark process running,\nor do you need distributed scale?"}
    D -->|"Yes"| E{"Do you need to delete rows\nin the same operation?"}
    E -->|"Yes"| F["MERGE INTO in SQL\n-- WHEN MATCHED THEN DELETE"]
    E -->|"No"| F
    D -->|"No, pure Python is enough"| G["table.upsert()\n-- compares values only, no SQL, no JVM"]

What none of the three replaces: the boundary already stated in module 3

It's worth closing the loop with something module 3 already made explicit, and that's still true here: none of these three techniques — overwrite(), MERGE INTO, upsert() — solves the general case of a dimension that changes several times, with facts spread across both sides of each change, when you need to preserve all historical versions visible as rows queryable with plain SQL (not time travel). That general case is still SCD type 2 territory at the row level — by hand in data-modeling, automated in dbt — exactly the boundary module 3's lesson 7 already drew. What this module added isn't a way around that boundary — it's three different ways to apply a change efficiently and correctly to the current table, letting Iceberg archive the rest in its own snapshots.

Common mistakes

Choosing the "newest" or "most impressive" technique instead of the one that fits the case. What happens: someone, after seeing MERGE INTO and upsert() work, assumes they're "better" than table.overwrite() in any situation, and starts using them even for trivial cases where overwrite() was already enough. Why it happens: the most recent techniques you learned instinctively feel like a universal improvement over the earlier ones. How to spot it: if you're writing a full MERGE INTO with staging and ON to replace four fixed rows you already have in memory, in Python, with no need for Spark at all, you're using a heavier tool than the problem calls for. How to fix it: go back to this lesson's comparison table — the right question is never "which technique is more advanced?", it's "what do I have available (delta or complete state), and what infrastructure do I already have running?"

Assuming upsert() can delete rows, because "update and insert" sounds like it should also be able to delete. What happens: someone looks for an argument like when_not_matched_by_source_delete=True in table.upsert(), expecting it to exist, symmetric to when_matched_update_all/when_not_matched_insert_all. Why it happens: MERGE INTO does support DELETE, and it's natural to expect the same capability in upsert(). How to spot it: check the docstring quoted in lesson 6 — the only two documented actions are updating matches and inserting non-matches, never deleting. How to fix it: if your case needs to delete rows that no longer show up in the source, upsert() isn't the tool — you need MERGE INTO with WHEN NOT MATCHED BY SOURCE THEN DELETE, or a separate table.delete() call with the right filter.

Exercises

Exercise 1 — Fill in the comparison table from memory. Without looking at this lesson, for each of Iceberg's three techniques (overwrite, MERGE INTO, upsert), answer: does it need Spark? does it compare values automatically? does it support DELETE?

See solution

overwrite(): doesn't need Spark; doesn't compare values (replaces everything, no exception); has no DELETE of its own (uses a separate table.delete(), outside this module's scope). MERGE INTO: does need Spark (and the Iceberg runtime correctly matched); doesn't compare values automatically, it has to be written by hand in WHEN MATCHED AND (...); does support DELETE in the same operation. upsert(): doesn't need Spark; does compare values automatically, column by column; doesn't support DELETE at all.

Exercise 2 — Apply the decision tree to a new case. Kiosko starts receiving, every day, a 50,000-row CSV file with price changes from external suppliers (not Kiosko itself, but a much larger reference catalog). Which of the three techniques would you choose, and why, using this lesson's decision tree?

See solution

With 50,000 rows of daily delta, table.overwrite() is ruled out from the start — it isn't a small complete state in memory, and replacing the whole reference table with a 50,000-row file would also be incorrect if the complete table has more rows than that. Between MERGE INTO and upsert(), the answer depends on the infrastructure: if Kiosko already has a Spark pipeline running daily for other loads (like spark-and-distributed-processing-guide's), MERGE INTO takes advantage of that already-existing infrastructure and scales with no friction to larger volumes in the future. If the team maintaining this specific pipeline works in pure Python, with no Spark process running for this task, table.upsert() solves the same problem — 50,000 rows is a perfectly reasonable volume for a single process with PyArrow — without the added complexity of standing up Spark just for this.

Exercise 3 — Explain why this lesson insists that none of the three techniques replaces module 3, lesson 7's work. In 2-3 sentences, in your own words, explain what problem this whole module still leaves unsolved.

See solution

This module's three techniques — overwrite(), MERGE INTO, upsert() — solve how to apply a change to a table's current version, correctly and efficiently. None solves the case where you need to query with plain SQL, with no AS OF, all of a row's historical versions at once — for example, a report that joins "each product's cost at the exact moment of each past sale," with whoever writes the query never needing to know any snapshot_id. That general case still needs row-level SCD type 2, with valid_from/valid_to directly queryable, the same boundary module 3, lesson 7 already drew with evidence.

Summary and next step

In this lesson you compared this module's three Iceberg techniques — overwrite(), MERGE INTO, upsert() — against five concrete criteria, all backed by evidence you already generated in previous lessons: what input they expect, whether they compare values, whether they support DELETE, whether they need Spark, and at what scale they operate well. None replaces the others — each better solves a different entry point, and none solves the general case of queryable SCD type 2 with plain SQL, which is still data-modeling and dbt's territory.

Before moving on you should be able to: fill in the comparison table from memory; apply the decision tree to a new case; and explain what this whole module still leaves unsolved, even after learning all five techniques.

Lesson 8 integrates this module's executable result into a single project: table.upsert() end to end, with automated asserts, and Spark's MERGE INTO documented as a representative reference alongside it.

Resources

• Apache Iceberg — official documentation, "Spark Writes," MERGE INTO section, source of the DELETE branch upsert() doesn't have. iceberg.apache.org/docs/latest/spark-writes/#merge-into. In English.
• PyIceberg — API reference, table.upsert(), source of the docstring with the four combinations of when_matched_update_all/when_not_matched_insert_all, with no DELETE option at all. py.iceberg.apache.org/api. In English.
• This same guide, module 3, lesson 7, "What time travel does NOT replace" — the boundary this lesson confirms is still in effect. 07-what-time-travel-does-not-replace.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.