Choosing a change data capture strategy

Change data capture is usually chosen by default. A team picks whatever the ingestion tool makes easiest, the pipeline works against the sample data, and the consequences show up months later as a slow divergence between the source system and the warehouse.

The choice deserves a few hours of thought at the start, and one question settles most of it. Ask what happens in your warehouse when a row is deleted at source.

The three approaches

Full refresh truncates the target and reloads everything on each run. It is the simplest option and it is correct by construction, because the target cannot drift from a source it is entirely rebuilt from. It stops being viable when the table is large enough that the reload does not fit the batch window, or when the source cannot tolerate being read in full that often.

Query-based capture selects rows where a modification timestamp or an incrementing key is greater than the highest value seen on the previous run. It is straightforward to implement against almost any source. A connector configured this way needs only read access on the table and one column it can watermark.

Log-based capture reads the database transaction log, the write-ahead log on Postgres or the transaction log on SQL Server, and turns it into a stream of inserts, updates and deletes. It has the lowest impact on the source and the highest fidelity. It also needs privileged access, cooperation from whoever administers the source database, and more operational care.

What deletes do to each of them

A query-based pipeline has no way to detect a delete. The row is no longer returned by the query, and a filter on a modification timestamp has nothing to match. The deleted row stays in the warehouse indefinitely and no error is raised, so nothing in the run history shows that the two systems have diverged.

The effect compounds. If the source deletes canceled orders, the warehouse keeps reporting revenue that includes them, and every model built on that table inherits the error. Duplicate customer records removed after a merge leave the customer count permanently high. The job still reports success and no test fails, because the pipeline is doing exactly what it was configured to do.

Two workarounds are available. Where the source performs soft deletes, setting a flag instead of removing the row, query-based capture works, provided the flag also updates the modification timestamp. Where the table is small enough, a periodic comparison of primary keys against the source will find the orphaned rows so they can be removed. Either is reasonable. Both require someone to decide which applies, and to record that the pipeline does not handle deletes on its own.

Log-based capture handles deletes correctly, because the delete appears in the log as an event of its own. That is the main reason to accept the extra operational cost.

The other questions worth asking

Deletes narrow the choice fastest. Four further checks usually settle it.

Timestamp reliability. Query-based capture depends entirely on the modification timestamp moving on every change. Applications that write through an ORM sometimes update a row without touching that column, and triggers that maintain it are often disabled during bulk loads. Where the timestamp is unreliable, the pipeline will lose changes and still report success.

Late-arriving data. A record inserted today carrying last week's business date is captured correctly by a watermark on the modification timestamp, because that column moves when the row is written. A watermark on the business date misses the row entirely. We see this often enough that it is worth confirming which column the watermark actually reads before anything else in the pipeline is reviewed.

Bulk operations. A month-end job that touches every row produces a change set the size of the table. A pipeline sized for an average day fails on that run, and it will fail on the same date every month until someone sizes it for the peak.

Latency. Log-based capture is often chosen for a latency requirement that nobody has stated. Ask who reads the data and when. Where the reports are opened each morning, a batch that completes overnight is enough, and the simpler pipeline is the better engineering decision until someone can describe what they would do differently with data an hour fresher.

Reading a watermark correctly

Whichever approach is chosen, the watermark itself deserves care. Two properties matter.

Store it per source table. A watermark held at the level of the pipeline run means one slow table holds back the rest, and a single failure forces a full reload of everything in that run.

Give it an overlap. Selecting rows strictly greater than the last watermark loses any row committed at exactly that instant, and it loses rows written by a transaction that opened before the watermark and committed after it. A small overlap window, five or ten minutes on most sources, removes both cases and costs very little.

The overlap only helps if the merge in the target is genuinely idempotent. A model that appends will duplicate rows on every overlap, which turns a safety measure into a data quality problem. Reprocessing an overlapping window should produce exactly the same result as a clean run. Assert that in CI.

A reasonable default

For most tables in most estates, query-based capture with an overlapping watermark and an idempotent merge is a sound choice, provided the source does not hard delete. Where it does hard delete, either move that table to log-based capture or add a periodic key comparison and accept the window during which the warehouse will be wrong.

Full refresh remains a good answer for small dimension tables, and teams sometimes talk themselves out of it because it feels unsophisticated. A dimension table that reloads in ninety seconds does not need a change data capture design at all.

Whatever is chosen, write down what the source guarantees and what the pipeline assumes about it, and keep that note with the model code. When we are asked to diagnose a pipeline defect, the answer is usually at that boundary, where nobody had recorded which side was responsible for deletes.