Why do rows hard-deleted at the source never disappear from a watermark-based incremental extract?

## The mechanism, stated plainly An incremental extract is a query: `SELECT ... FROM orders WHERE updated_at >= :watermark`. Rows are returned because they exist and satisfy a predicate. A `DELETE` removes the row from the table; there is no remaining tuple with a timestamp to compare, no marker left behind, and no side effect the query can observe. The deletion is not "late" or "missed" — it is invisible by construction, and no amount of lookback, wider windows or more frequent runs will surface it. The consequence is asymmetric and quiet. Inserts and updates flow; deletions accumulate as phantom rows in the target. Counts drift upward, sums overstate, joins fan out against records the source no longer has, and any downstream user counting "active" anything gets a number that only ever goes up. Because the pipeline is doing precisely what its code says, monitoring on freshness, row counts loaded, or job success will all stay green. There is a compliance edge too: if a deletion at the source was an erasure request, the copy sitting in the warehouse is the thing that matters, and "our loader does not carry deletes" is not an answer. ## Remedy one: soft delete at the source The cleanest fix is to stop hard-deleting. The source marks the row — `is_deleted = true`, `deleted_at = now()` — and bumps the change column. The deletion now arrives through the ordinary incremental path as an update, and the load applies it like any other change. This is a source-schema and source-team decision, not something the pipeline can impose, and it has its own costs: the source table grows, application queries must all filter the flag, and eventually someone runs a purge job — which hard-deletes, reintroducing the original problem for the purged rows unless the purge is coordinated with the pipeline. When it is available, take it. It is the only remedy that is both timely and cheap. ## Remedy two: the periodic key reconcile The pragmatic default when the source will not change. Periodically read *only* the primary keys from the source — a narrow, index-friendly scan that is far cheaper than a full-row refresh — and diff them against the keys in the target. Keys present in the target and absent from the source are deletions; mark them (`is_deleted = true`, with a timestamp) rather than removing them, so downstream models can decide whether to filter or to keep the history. The details that matter: - **Consistency of the key snapshot.** Read the keys as of a single point in time and only reconcile against target rows that were loaded before that point, or a row inserted at the source between the key read and the diff will be flagged as deleted. - **Scope.** Reconciling a whole 500-million-row table nightly may be unaffordable. Scope it to a partition or a recent time range if deletions are only plausible there — but be explicit that anything outside the scope is unmonitored. - **Blast radius.** A reconcile that flags too much because of a bad snapshot can mark most of a table deleted in one statement. Add a guard: refuse to proceed if the diff exceeds a plausible fraction of the table, and alert instead. - **Cadence versus staleness.** The reconcile interval is exactly how long a deletion can survive in the warehouse. Choose it against what the consumers and any retention policy require, and write it down. ## Remedy three: a source-side record of deletions If the source application maintains an audit table, an event outbox, or a trigger-populated deletions log, deletions become rows again and the ordinary incremental machinery works on them — you extract the deletions table with its own watermark and apply the tombstones. The caveats are real: a trigger costs write throughput on the source and can be bypassed by bulk operations or by a `TRUNCATE`, and an application-maintained outbox only covers deletions the application performs. Anything that deletes out of band is still invisible, so this remedy narrows the gap rather than closing it. ## Remedy four: stop polling that table If timely deletions genuinely matter and the source is a database whose transaction log you can read, the answer is a different capture strategy entirely: log-based capture emits a delete event because the deletion was written to the log. That is a decision to leave the batch-extract world, with its own operational cost, and the detail belongs to the change-data-capture branch — the point for this leaf is knowing that no arrangement of watermarks will get you there. ## How to detect the drift you already have Whatever you choose, add a check that would notice the problem: compare the source row count against the target's non-deleted count on a schedule, and alert on a widening gap. It is a single cheap query per table, it catches missed deletions and several other classes of drift at once, and it converts a silent, permanent error into a ticket. ## What to say when asked Name the mechanism first (a deleted row cannot match a predicate), then the consequence (silent permanent drift, inflated aggregates, possible retention exposure), then the remedies with their tradeoffs, and finish with the detection check. Candidates who only say "use CDC" have skipped the part interviewers are testing: what you do when the source team will not soft-delete and you cannot get on the log this quarter.