CDC into Iceberg: the delete files nobody counted

• The shape of a CDC pipeline
• What three merges cost
• What compaction does, and does not, fix
• Choosing the write mode for a CDC table
• What to monitor
• Getting the merge right
• Common misconceptions
• A model worth keeping
• References
• Trademarks

TL;DR

• Three MERGE INTO statements against a merge-on-read table left 5 data files holding 5,960 records and 3 delete files holding 1,120 — for a table that returns 4,840 rows. Over a fifth of what is stored is dead weight the reader filters out.
• Every merge adds a delete file. The count grows with the number of merges, not the number of changed rows, so a frequent CDC pipeline accumulates them fastest.
• rewrite_data_files collapsed the data side to 1 file of 4,840 records — the live rows exactly — but 2 delete files survived. Compaction of data is not compaction of deletes.
• The files metadata table with its content column is the monitor. content = 1 growing over time is the early warning.
• A CDC table without scheduled maintenance does not fail. It gets slower every hour, with no change in row count to explain it.

The shape of a CDC pipeline

Change data capture lands a stream of row-level events — inserts, updates, deletes — from a source database into a table. The usual path is Debezium reading the database log, Kafka carrying the changes, and a Spark job applying them.

flowchart LR
    DB[(source DB)] -->|"log"| DBZ[Debezium]
    DBZ --> K[Kafka]
    K --> S["Spark micro-batch"]
    S -->|"MERGE INTO"| I[(Iceberg table)]
    I -.->|"accumulates delete files"| M["compaction<br/>+ expiry"]

The interesting part is the last arrow, and it is the one most pipelines omit.

MERGE INTO is the statement that applies a changelog, because it expresses all three operations against a key in one pass:

MERGE INTO local.db.cdc t USING chg s ON t.id = s.id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.v = s.v, t.updated_at = s.updated_at
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT (id, v, updated_at) VALUES (s.id, s.v, s.updated_at)

That statement is correct, idempotent when keyed properly, and the reason CDC on Iceberg is straightforward to build. What it does to the files underneath is the part worth measuring.

What three merges cost

A merge-on-read table loaded with 5,000 rows, then three changelog batches applied, each touching 400 rows with a fifth of them deletes. Reading the files metadata table after each step, where content is 0 for data and 1 for position deletes:

Read the last row carefully. The table answers queries with 4,840 rows. To do that it stores 5,960 records across five data files, and applies 1,120 delete records across three delete files to filter them down. Roughly 19% of the stored records are dead, and every scan pays to exclude them.

Two patterns are visible in that table and both matter.

Data records only ever go up. An update in merge-on-read is a delete plus an insert: the new version is appended, the old one is marked deleted. Nothing shrinks at write time.

Delete files grow per merge, not per row. Merge #1 wrote one delete file, merge #2 a second, merge #3 a third. A pipeline running a merge every minute accumulates 1,440 delete files a day regardless of whether each batch changed ten rows or ten thousand.

That second point is the one that makes CDC different from occasional updates. The cost tracks your trigger interval, not your data volume.

What compaction does, and does not, fix

Running the standard data compaction:

CALL local.system.rewrite_data_files(
  table => 'db.cdc',
  options => map('min-input-files','2'))

after rewrite_data_files: data=1f/4840r   pos-del=2f/960r   rows=4840

The data side is now exactly right: one file holding 4,840 records, which is precisely the live row count. The rewrite read the data files, applied the delete files, and wrote out only surviving rows. The 1,120 dead records are gone from the data.

But two delete files remain, holding 960 records. Data compaction did not clear the delete side. Iceberg has a separate procedure for that:

CALL local.system.rewrite_position_delete_files(table => 'db.cdc')

The distinction is easy to miss and expensive to miss for a long time. A pipeline that runs rewrite_data_files nightly and calls it maintenance still accumulates delete files, and the read path still applies them.

Add the third job, the one that actually reclaims storage:

CALL local.system.expire_snapshots(table => 'db.cdc', retain_last => 10)

Because none of the above deletes anything from disk. Compaction writes new files and leaves the old ones referenced by older snapshots. That accounting is the subject of table maintenance for Iceberg and Hudi.

Choosing the write mode for a CDC table

The table above is merge-on-read, which is the usual choice for CDC and worth justifying rather than assuming.

Under copy-on-write, each merge rewrites every data file containing an affected row. A changelog touching 400 scattered keys could rewrite most of the table, every minute. That does not keep up, and it is why CDC pipelines choose merge-on-read.

The trade is the one measured above: writes stay cheap, reads carry the delete files until compaction runs. Set it deliberately, per operation:

CREATE TABLE local.db.cdc (...) USING iceberg
TBLPROPERTIES (
  'write.merge.mode'  = 'merge-on-read',
  'write.delete.mode' = 'merge-on-read',
  'write.update.mode' = 'merge-on-read')

The full comparison, with the same DELETE measured under both modes, is in copy-on-write or merge-on-read.

One caveat for Flink-based CDC. Spark writes position deletes, which name a file and row positions. Flink CDC writers commonly produce equality deletes, which are predicates on key columns. Equality deletes are more expensive to apply, because the reader evaluates them against every data file in scope rather than one named file. A pipeline mixing engines accumulates both kinds, and the read cost is not the same.

What to monitor

One query, run on a schedule, answers whether the pipeline is healthy:

SELECT content, count(*) AS files, sum(record_count) AS records
FROM local.db.cdc.files
GROUP BY content;

content = 0 is data, 1 is position deletes, 2 is equality deletes.

Watch the ratio, not the absolute numbers. Delete records as a fraction of data records is the fraction of read work that is wasted. On the measured table that ratio reached 19% after three merges. A steady-state pipeline with maintenance keeps it low; one without maintenance climbs until queries hurt.

Two more signals worth alerting on. Delete file count climbing across days means compaction is not keeping pace with the merge frequency, and the fix is usually to compact more often rather than to merge less often. Query planning time growing while row count is flat points at metadata rather than data, which is snapshot and manifest accumulation, not delete files.

Getting the merge right

Two correctness issues matter more than the performance ones.

Deduplicate the changelog before merging. If a batch contains two events for the same key, MERGE INTO raises an error rather than picking one, because the result would be ambiguous. Collapse to the latest event per key first, typically with a window function on the source’s log sequence number or timestamp. Using wall-clock arrival time here is a common bug: out-of-order delivery then applies an older version over a newer one.

Make the match condition the real key. Matching on a column that is not unique in the target produces the same ambiguity error, or worse, silently updates more rows than intended when it happens to be unique in the sample you tested with.

Common misconceptions

“MERGE INTO updates rows in place.” In merge-on-read it appends new versions and writes delete records for the old ones. Storage grows on every merge.

“Compaction cleans up deletes.” rewrite_data_files applies them to the data it rewrites; delete files can survive it. rewrite_position_delete_files is the dedicated procedure.

“The row count tells me the table is healthy.” The measured table returned 4,840 rows while storing 5,960 data records and 1,120 delete records. Row count is exactly the number that hides this.

“More frequent merges are strictly better for freshness.” They are also strictly worse for delete-file accumulation, because the file count follows the merge count.

“Copy-on-write would avoid all this.” It avoids delete files by rewriting data files instead, which for a scattered changelog can mean rewriting most of the table per batch.

A model worth keeping

A CDC pipeline on Iceberg is a debt arrangement. Every merge borrows read performance to keep write latency low, and compaction is the repayment.

The borrowing is automatic and the repayment is not, which is why the failure mode is a table that works perfectly for a month and then gets slow for no visible reason. Schedule rewrite_data_files, rewrite_position_delete_files and expire_snapshots when you build the pipeline, and monitor the delete-to-data ratio so you find out before your users do.

References

• Iceberg maintenance procedures for rewrite_data_files, rewrite_position_delete_files and expire_snapshots
• Iceberg writes with Spark for MERGE INTO semantics and its restrictions
• Iceberg spec: delete formats for position versus equality deletes
• Copy-on-write or merge-on-read for the mode this pipeline depends on
• Streaming into Iceberg for the trigger that decides how often merges happen

Trademarks

Apache Iceberg, Apache Spark, Apache Flink, Apache Kafka, Apache and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries. Debezium is a trademark of Red Hat, Inc.