Loading data into Iceberg: appends, overwrites and row-level SQL

• The four ways in
• Overwrite, and the one that surprises people
• Row-level SQL
  • MERGE INTO
  • Two ways to get MERGE wrong
• Batch loading patterns
• Controlling the files a write produces
• Idempotent batch loads
• Writing from outside Spark
• What to check after a load
• Writing well
• Common misconceptions
• A model worth keeping
• References
• Trademarks

TL;DR

• Four ways in: append, overwritePartitions, overwrite with a filter, and row-level SQL (UPDATE, DELETE, MERGE INTO).
• A single MERGE INTO applied inserts, updates and deletes together: a 1,000-row table plus a 1,000-row changelog produced init=500, upd=300, ins=500 — 1,300 rows from one statement.
• Each write records an operation in the snapshot log: append for added files, overwrite when files were replaced, delete when a delete file was written.
• Prefer writeTo(...).append() over write.format("iceberg").save(path) — the v2 API resolves the table through the catalog rather than a path.
• MERGE fails on duplicate keys in the source rather than picking one. Deduplicate before merging.

The four ways in

# 1. append: add data, touch nothing existing
df.writeTo("local.db.events").append()

# 2. dynamic partition overwrite: replace only the partitions present in df
df.writeTo("local.db.events").overwritePartitions()

# 3. filtered overwrite: replace exactly what the filter matches
df.writeTo("local.db.events").overwrite(F.col("event_ts") >= "2026-01-06")

# 4. row-level SQL
spark.sql("UPDATE local.db.events SET region = 'r9' WHERE id < 100")
spark.sql("DELETE FROM local.db.events WHERE region = 'test'")
spark.sql("MERGE INTO local.db.events t USING changes s ON t.id = s.id ...")

Use the writeTo API, not write.format("iceberg").save(path). The former resolves the table through the catalog, which is what makes the commit atomic and the identity stable. Writing by path bypasses the thing that makes an Iceberg table a table.

Each of these records what it did:

That column is the audit trail. When a table changed unexpectedly, the sequence of operations says what kind of change happened before you go looking at data.

Overwrite, and the one that surprises people

overwritePartitions() is dynamic: it replaces the partitions present in the incoming DataFrame and leaves every other partition alone. Reloading one day touches that day only.

The surprise is what happens when the DataFrame is empty. No partitions are present, so nothing is replaced — the statement succeeds and changes nothing. A pipeline whose upstream produced no rows silently keeps yesterday’s data, and the job is green.

overwrite(filter) is explicit: it replaces exactly what the filter matches, whether or not the incoming data covers it. An empty DataFrame with a filter for yesterday deletes yesterday, which is at least unambiguous.

Use the filtered form when correctness depends on the old data being gone, and the dynamic form when you are topping up partitions you know you have.

Row-level SQL

The statements that make a lakehouse table behave like a database table.

UPDATE local.db.events SET region = 'r9' WHERE id < 100;
DELETE FROM local.db.events WHERE region = 'test';

Both work on format v1 by rewriting files, and on v2 either by rewriting (copy-on-write) or by writing delete files (merge-on-read), governed by write.update.mode and write.delete.mode. The comparison of the two has both measured.

MERGE INTO

The statement that applies a changelog, doing all three operations in one pass:

MERGE INTO local.db.tgt t USING changes s ON t.id = s.id
WHEN MATCHED AND s.chg = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.v = s.v, t.op = 'upd'
WHEN NOT MATCHED THEN INSERT (id, v, op) VALUES (s.id, s.v, 'ins')

Against a 1,000-row table with a 1,000-row changelog overlapping half of it:

op=init  rows=500
op=ins   rows=500
op=upd   rows=300
total rows = 1300
snapshot ops: ['append', 'overwrite']

Five hundred untouched rows, 500 inserted, 300 updated, 200 deleted — and one snapshot. The whole changelog applied atomically: no reader saw the inserts without the deletes.

Two ways to get MERGE wrong

Duplicate keys in the source. If changes contains two rows for the same id, the statement fails rather than picking one, because the outcome would be arbitrary. Collapse to one row per key first, using the source’s sequence number or commit timestamp:

w = Window.partitionBy("id").orderBy(F.col("log_seq").desc())
latest = changes.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn")

Ordering by arrival time instead of the source’s own sequence is a common bug: out-of-order delivery then applies an older version over a newer one, and the table is quietly wrong.

A match condition that is not the real key. Matching on a column that is not unique in the target updates more rows than intended, or fails ambiguously. It usually passes testing, because the test data happens to be unique.

Batch loading patterns

Full reload: overwrite with a filter covering everything, or CREATE OR REPLACE TABLE ... AS SELECT. Simple, and rewrites everything every time.

Incremental append: the cheapest path, and the one that accumulates small files fastest. Compaction is the other half of this pattern.

Partition reload: overwritePartitions() with the partitions you regenerated. The normal shape for a daily pipeline that reprocesses late-arriving data.

Upsert: MERGE INTO, with the deduplication above.

Whichever you use, the file-count consequence is the same: every commit writes files, and nothing consolidates them until compaction runs. A pipeline appending hourly produces 24 files per partition per day.

Controlling the files a write produces

The most common ingestion problem is not correctness, it is file count, and it is decided before the write by how the DataFrame is partitioned.

A DataFrame with 200 Spark partitions writing into 30 table partitions can produce up to 6,000 files in one commit. write.distribution-mode makes Iceberg shuffle for you instead:

ALTER TABLE local.db.events SET TBLPROPERTIES (
  'write.distribution-mode' = 'hash',            -- shuffle by partition key
  'write.target-file-size-bytes' = '268435456'); -- 256 MiB

hash costs a shuffle and saves you from the small-file problem that shuffle was avoiding. On a partitioned table it is almost always the right default, and the exception is a pipeline that already repartitioned deliberately:

(df.repartition(24, "event_ts")
   .sortWithinPartitions("region")
   .writeTo("local.db.events").append())

Check what a write actually produced rather than assuming:

SELECT partition, count(*) AS files,
       round(avg(file_size_in_bytes) / 1048576, 1) AS avg_mb,
       sum(record_count) AS rows
FROM local.db.events.files
GROUP BY partition ORDER BY files DESC LIMIT 10;

Average file sizes in the low single-digit megabytes mean the next query against that partition pays for it.

Idempotent batch loads

A reload that runs twice should not double the data, and append gives you no help with that. Three patterns, in order of preference.

Partition overwrite, when the load is naturally partition-scoped:

(daily_df.writeTo("local.db.events").overwritePartitions())

Re-running replaces the same partitions. Safe to repeat — with the empty-DataFrame caveat from earlier, which is worth guarding explicitly:

if daily_df.head(1):
    daily_df.writeTo("local.db.events").overwritePartitions()
else:
    raise ValueError("refusing to run a load that produced no rows")

Filtered overwrite, when correctness requires the old rows gone whether or not new ones arrived:

(daily_df.writeTo("local.db.events")
         .overwrite(F.col("event_ts") == F.lit("2026-01-06").cast("date")))

MERGE on a key, when the load overlaps arbitrary existing rows. Naturally idempotent because it reconciles rather than adds.

Writing from outside Spark

Two ingestion routes that are not a Spark DataFrame, and both come up.

Adopting files that already exist — data written by another tool into the right layout, added to the table without copying:

CALL local.system.add_files(
  table => 'db.events',
  source_table => '`parquet`.`s3://landing/events/dt=2026-01-06`');

This reads the Parquet footers for statistics and writes manifests pointing at the files where they are. It does not validate that the schema matches, so check before and count after.

CREATE TABLE AS SELECT for the initial load of a derived table:

CREATE TABLE local.db.events_summary
USING iceberg
PARTITIONED BY (days(event_ts))
TBLPROPERTIES ('write.target-file-size-bytes' = '268435456')
AS SELECT event_ts, region, count(*) AS n, sum(amount_usd) AS total
   FROM local.db.events GROUP BY event_ts, region;

Set the table properties in the CREATE, because properties applied afterwards only affect subsequent writes — the initial load will already have produced its files.

What to check after a load

-- did the commit do what you expected?
SELECT operation, summary['added-data-files'] AS files,
       summary['added-records'] AS records, summary['deleted-records'] AS deleted
FROM local.db.events.snapshots ORDER BY committed_at DESC LIMIT 1;

deleted-records on a load you believed was an append is the signal that overwritePartitions replaced more than intended — the fastest way to catch a partition-scoping mistake, and it costs one query.

Writing well

Control the number of output files. A DataFrame with 200 partitions writes up to 200 files per target partition. repartition to something proportional to the data before writing, or set write.distribution-mode to hash so Iceberg shuffles by partition key for you.

Set the target file size at create time rather than discovering it later, and know that it shapes compaction more reliably than ingestion.

Do not commit more often than you need to. Commit frequency drives metadata growth — three metadata files per commit — and is the main lever on long-term table health.

Common misconceptions

“overwritePartitions replaces the table.” It replaces only the partitions present in the incoming data. With no rows, it replaces nothing.

“MERGE is several statements.” It is one commit. The measured merge produced one snapshot for inserts, updates and deletes together.

“save(path) is equivalent to writeTo.” Writing by path bypasses the catalog, which is where atomicity and identity live.

“Appending is free.” Each append is a commit: three metadata files plus data files, and small files until compacted.

“UPDATE needs merge-on-read.” It works on any version; the mode decides whether files are rewritten or delete files written.

A model worth keeping

Every write is a commit, and the snapshot log records which kind. Choose the statement by what should be true afterwards — add rows, replace these partitions, replace what matches this filter, or reconcile against a changelog — and let the mode properties decide how it is physically done.

Then remember that ingestion is half the job: the files you write are the files someone has to compact.

References

• Iceberg Spark writes for the write API and row-level SQL
• Iceberg configuration for distribution mode and file sizing
• Copy-on-write or merge-on-read for how row-level statements are executed
• Streaming into Iceberg for the continuous case
• CDC into Iceberg for MERGE applied repeatedly

Trademarks

Apache Iceberg, Apache Spark, Apache and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.