Choose how to capture changes
A downstream table must follow a source that keeps changing. There are several ways to detect a change, and they form a ladder: each rung down is cheaper and less coupled to the source, and loses a nameable class of change. Nothing recovers a class of change that the source never recorded. Which rung does this source and this consumer need?
You will end with the cheapest way to see the changes your consumer needs, what it costs the source, and the class of change it can still miss.
The questions go from cheapest to most expensive answer: stop at the first one that fits.
Decide
Is the share of rows that change per run below the reciprocal of the merge penalty, and is a full rebuild slow enough to be worth a pipeline?
With a 20× merge penalty the break-even is near 5% of rows; a 40 GB table that rebuilds in two minutes is not worth capture infrastructure.
Why
Incremental converts a cost proportional to state into a cost proportional to change plus a constant. That trade is only good when state is large, change is small, and the constant is spread over many runs. A table where a fifth of the rows change daily should probably just be rebuilt.
Does the data arrive append-only, partitioned by a key that follows arrival time?
Event data in date folders.
Why
Then "what changed?" collapses to "which partitions are new?": a directory listing compared against a stored high-watermark, with no capture infrastructure and a fixed cost close to zero. Most working incremental pipelines are this and nothing more.
Is the source a table in a table format that commits snapshots?
Does every write go through code you own, and does the consumer want the business event rather than row changes?
Can you read the database's log, and own the risk that a stopped reader fills the source's disk?
YesRead the log
NoGo to 6
Why
Log-based capture is complete (deletes, every intermediate state, commit order) and stays off the write transaction's path. Its price is that the source can no longer recycle log segments the reader has not read: the consumer's liveness becomes the source's availability problem.
Can the source afford extra latency on every write statement?
Why
Log-based pays in retention coupling, trigger-based pays in source latency. Both pay; neither escapes. Polling pays almost nothing and loses the most.
Where you can land
Rebuild in full
Use it when the change fraction is above the break-even, or the table is small.
What it costs: a full sequential scan every run, and zero extra systems: no capture, no offsets, no state, nobody on call for them.
Watermark over partitions
Use it when data is append-only and partitioned by arrival.
What it costs: nothing to the source. It misses late data that lands in a partition already processed, and restatements of old partitions.
Before you build it
Reprocess a window, and know its edge
Always reprocess the last N periods, overwriting idempotently: with N = 3 days, a batch that arrives up to 2 days late is picked up, at 3× the cost of a small daily partition. Any correction older than N is missed silently.
Diff snapshots
Use it when the table format already records which files each commit added and removed.
What it costs: metadata reads only. It gives changed files, not rows: a copy-on-write update shows the whole rewritten file as changed.
Before you build it
Keep the cleaner behind the slowest reader
Snapshot expiration deletes the history a consumer's cursor points into. If the cleaner expires snapshots older than 7 days and the consumer is down for 9, its cursor points at a snapshot that no longer exists. Retention is a negotiation between the maintenance job and the slowest reader.
Outbox: the writer announces it
Use it when you own every write path.
What it costs: one more row per event, written in the same transaction as the change. It misses any write that does not go through the code that fills the outbox: a correction script, a migration.
Read the log
Use it when the log is readable and you can bound its retention.
What it costs: more log volume and decoding CPU on the source, and a source that keeps every log segment the reader has not consumed. It misses only writes that bypass the log, such as some bulk loads.
Before you build it
Bound what a stopped reader can cost the source
Alert on the age of the oldest unconsumed log position, and cap how much log the source keeps for a reader. A reader that dies on Friday fills the primary's disk by Monday at 120 GB of log a day against 300 GB free. Decide in advance whether you would rather lose the reader (and take a new snapshot) than the database.
Triggers into a shadow table
Use it when there is no readable log and writes can afford it.
What it costs: typically 2–5× per write statement, worse for bulk operations, and a second writer inside every transaction. Capture is transactionally consistent for free. It misses writes that bypass triggers.
Poll a timestamp column
Use it when the consumer only needs the latest state.
What it costs: almost nothing. It cannot see hard deletes, aliases away every state that lasts less than a poll interval, and never sees a write that forgot to set the timestamp.
Before you build it
Re-read an overlap window
A transaction that starts before your watermark and commits after it writes a timestamp that is already in your past, and the next poll never selects it. Always re-read the last few minutes. That mitigates it; nothing eliminates it.
Whichever you land on
Write down the blind spot
Every method has a set of writes that bypass it: bulk loads, TRUNCATE, restores from backup, replica rebuilds. None can report the size of its own blind spot, so reconcile counts against the source on a schedule.