Why can an incremental extract filtered on updated_at above the stored watermark still lose rows?

## The filter looks obviously correct `WHERE updated_at > :last_watermark` reads like a complete statement of "everything new." It is not, because the value in `updated_at` and the moment the row becomes visible to your extract are two different events, and the gap between them is where rows go missing. ## Failure one: commit lag In any transactional database a row written by an open transaction is invisible to other readers until that transaction commits. But `updated_at` is normally set by `DEFAULT current_timestamp`, an application assignment, or a trigger — all of which fire when the statement executes, not when it commits. On many engines `current_timestamp` is even fixed at transaction *start*. So consider a transaction that begins at 10:00:00, writes order 991 with `updated_at = 10:00:00`, does other work, and commits at 10:05:30. Your extract runs at 10:04:59, cannot see order 991 (it is uncommitted), reads the rows it can see, and stores watermark 10:05:00. At 11:00 the next run asks for `updated_at > 10:05:00`. Order 991 has a timestamp of 10:00:00. It is visible now, but it is below the watermark, so it will never be returned by any future run. The row is lost permanently, and nothing in the pipeline errors. This is the single most common correctness bug in hand-rolled incremental loads, and the reason interviewers ask the question. It scales badly too: the busier the source, the longer its transactions, the more rows fall through. ## Failure two: the boundary tie Timestamps are not unique. A bulk update can stamp thousands of rows with the same value, and low-resolution columns (`DATETIME` to the second, or a plain `DATE`) collide constantly. If you store `max(updated_at)` from the batch you just read and next time filter `updated_at > that value`, any row that shares the maximum timestamp but was written or committed after you read is dropped. If you instead filter `>=`, you re-read every row at the boundary — which is correct only if the write is keyed, and produces duplicates if the load blindly appends. The clean formulation is a **half-open window**: `updated_at >= :lo AND updated_at < :hi`, where the next run's `lo` is the previous run's `hi`. Half-open windows tile the timeline with no gap and no overlap, every run has explicit reproducible bounds, and a replay of the same window reads exactly the same rows. ## Failure three: clock skew If `updated_at` is assigned by application servers and the watermark is compared against a value produced by the orchestrator or by the extract host, you are comparing two clocks. Even well-synchronised fleets drift by tens or hundreds of milliseconds; a badly synchronised one drifts by minutes. A row stamped by a server whose clock runs slow lands below a watermark taken from a faster clock and is skipped. The mitigation is to take every timestamp involved from one authority — usually the source database's own clock, via the column and via a `SELECT current_timestamp` on the same connection — and never to use the orchestrator's wall clock as a watermark. ## The fixes, in the order they matter **Lookback window.** Deliberately re-read a trailing overlap: `updated_at >= :watermark - INTERVAL '2 hours'`. Sized wider than the longest plausible commit lag plus skew, it covers commit lag, ties and skew at once. This is the standard answer and the one interviewers want to hear. **Idempotent write.** A lookback is only free if re-reading a row is harmless, which means the load must be an upsert keyed on the row's identity, or a replacement of the affected partition. With a keyed write, over-reading costs a little compute and nothing else, so you can afford to err wide. **Half-open bounds passed in explicitly.** Compute `lo` and `hi` once per run, log them, and pass them as parameters. A run that says "everything since whenever" cannot be replayed; a run that says `[2026-08-20T00:00Z, 2026-08-21T00:00Z)` can be replayed identically forever. **Do not store now() as the watermark.** Storing the extract's own clock time moves the boundary past work that was in flight during the run. Store the maximum timestamp of the rows you actually loaded, or the `hi` bound you read up to, and only after the load committed. **Prefer a commit-ordered column when the source offers one.** A monotonically increasing sequence, a system version column, or a change-tracking version assigned at commit removes commit lag entirely because the ordering matches visibility. Where such a column exists, use it instead of a timestamp — a timestamp column is a proxy for commit order, and a leaky one. ## How to size the lookback honestly Measure rather than guess: sample the source for the longest transaction duration over a week (most engines expose this), add your worst observed clock skew, and add margin. A two-hour lookback on a source whose longest transaction is four minutes is comfortable; a five-minute lookback on a source that runs hour-long batch jobs is a data-loss incident waiting for month end.