Change Volume Ratio

Two pipelines move 12 million changes a day from the same database into the same lakehouse. One costs a few pounds a day and finishes in eight minutes. The other costs twenty times as much and is still running when the next batch arrives. The difference is not volume. One stream is 95% inserts and the other is 70% updates.

An insert is an append; an update is a read-modify-write of data you do not have in front of you. Landing an insert means writing a file. Landing an update means finding which existing file holds the row, rewriting that file without it, and writing a new one — or maintaining a merge-on-read structure and paying the cost at query time instead. The ratio between the two is the single most useful number for sizing a change-capture pipeline, and it is almost never asked for.

Why it matters

It determines the target table design before any tool is chosen: an append-only stream is happy in almost any format, while an update-heavy stream needs a merge strategy, a partitioning scheme that localises updates, and a compaction budget.

It determines stream volume, because update-heavy sources are the ones where full before-images matter, which roughly doubles the bytes on the wire.

And it determines whether latency targets are achievable at all. A merge into a 4 TB table that touches rows scattered across 900 partitions cannot run every five minutes, regardless of cluster size, because the rewrite is bound by files touched rather than rows changed.

Implementation patterns

• Measure it per table before designing anything, from the source's own statistics or a day of captured stream. The distribution across tables is usually extreme: three tables account for most of the updates.
• Partition the target so that updates cluster. If updates land overwhelmingly in the last 7 days, partition by date and the merge touches 7 partitions rather than 900.
• Choose merge-on-read for update-heavy tables and accept the read-side cost and the compaction job, or copy-on-write for append-heavy ones and keep reads simple.
• Set replica identity per table, so only the tables that need full before-images pay for them.
• Track the ratio as a monitored metric, not a one-off measurement. A product change that starts mutating a previously append-only table will move it, and the pipeline degrades weeks later for reasons nobody connects to the release.

Industry example

The published accounts of large Postgres-backed products moving analytics off packaged connectors and onto their own change-capture pipelines consistently cite the same driver: their change streams were dominated by updates rather than inserts, which made re-ingesting or rewriting large table regions the dominant cost. The general lesson those write-ups share is that the ratio, not the row count, is what forces a purpose-built ingestion path.

Failure scenarios

• A pipeline sized on row count alone that meets its latency target in test, where the seeded data was append-only, and misses it by 6x in production.
• A table that was append-only becoming mutable after a feature ships, with merge cost rising gradually and no metric attributing it to the change.
• Deletes ignored in the estimate, so the target table accumulates rows the source no longer has and reconciliation drifts by a growing margin.
• Enabling full before-images globally after one consumer asks for them, tripling the source's write-ahead log volume and causing the source's own replication to lag.

Trade-offs

A high ratio is not a problem to be fixed; it is a property of the workload. What it buys you is an honest design conversation early. Copy-on-write gives fast reads and pays at write time; merge-on-read gives fast writes and pays at read time and in compaction. Choosing before you know the ratio means choosing by fashion, and the bill arrives at the wrong end of the pipeline for whoever is on call.

When not to use it

Do not bother measuring it for a source under a few million rows that is reloaded in full each night. A full reload sidesteps the entire question, is far simpler to reason about, and is the correct answer for more tables than most platforms admit — typically anything that reloads inside the batch window.

It becomes worth measuring once a table is large enough that a full reload does not fit the window, or once freshness under an hour is required. That is the same threshold at which change capture is justified at all, so in practice the rule is: if you are considering change capture, measure this first; if you are not, you do not need it.

Interview question

Q: Two teams capture changes from the same database at the same row rate. One pipeline costs twenty times the other. Before looking at either implementation, what would you ask, and how would the answer change the design you would recommend?

What a strong answer covers: asking for the update-and-delete share per table rather than the aggregate row rate · why an update is a read-modify-write against data not in hand · partitioning so updates localise, with the 7-partitions-versus-900 contrast · copy-on-write against merge-on-read and where each pays · replica identity set per table rather than globally · treating the ratio as a monitored signal because a product change can move it · and the recognition that a nightly full reload removes the problem entirely for tables that fit the window.

Quick check

Quiz: Why can two change-capture pipelines with identical row rates differ in cost by an order of magnitude? Because updates force a read-modify-write of existing files while inserts are appends, so the update share rather than the row count drives the work.

Flashcard: What do you measure before designing a change-capture target table? The update-and-delete share per table, and where updates land in the partition space — both decide the merge strategy and whether the latency target is reachable.