Comparison
High-water markvsIncremental load
High-water mark
the job stores the largest updated_at it has seen and asks for everything greater than that next time.
The stored position an incremental load resumes from — usually a timestamp or a monotonically increasing id. Its correctness rests on an assumption about the source that nobody wrote down: that the column only ever moves forwards, and that a row is visible to your reader by the time its timestamp says it exists. Both are routinely false in a source with long transactions, which is where the missing-rows bug comes from.
Full entry →Incremental load
each run asks the source only for rows changed since the last run, and one row updated in the same second as the cutoff is now missing forever.
Reading only what has changed since the previous run, tracked by a cursor of some kind. It is the standard way to make a big table affordable, and it introduces a whole class of quiet bug: rows changed exactly on the boundary, rows updated without their timestamp moving, and hard deletes, which by definition leave nothing to find. The failures are silent because a missing row does not error, it simply lowers a total.
Full entry →