Comparison
Hard delete detectionvsHigh-water mark
Hard delete detection
the row is gone from the source and still in the warehouse, because your incremental load can only see rows that exist.
Working out what the source deleted, when your extraction method can only observe what is present. Query-based extraction cannot do it directly, so teams fall back to a periodic key-set comparison, a soft-delete flag negotiated with the source team, or moving to log-based capture. It is worth naming because it is nearly always discovered late, by an auditor or a privacy request rather than by a test.
Full entry →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 →