Incremental Overlap Window
also called Lookback Window, Trailing Reprocess Window
Re-reading a trailing slice of already-loaded data on each incremental run and merging by key, so late commits are caught and replays are harmless.
An orders pipeline loads rows where updated_at is newer than the last high-water mark. Every month finance finds a few dozen orders missing from the warehouse. The orders were committed late: a transaction that began at 23:58 stamped its rows with a timestamp earlier than the mark the pipeline had already advanced to. An overlap window makes each run start before the mark, by a margin chosen from measured lateness, and merge by primary key so reloading rows already present changes nothing.
Why it matters
A bare watermark assumes the timestamp column is monotonic with commit order, which holds in almost no operational database. Clock skew between app servers, long transactions and replica lag all break it, and the resulting loss is silent: row counts drift by a handful per day. The overlap window turns an exactness assumption into a tunable margin.
Implementation patterns
- Size the window from data. Measure the gap between the row's timestamp and its time of arrival at the warehouse; set the window to the 99.9th percentile gap plus headroom (an hour for a transactional database, days for mobile events with offline queues).
- Merge on a unique key so the same row arriving in two runs overwrites itself. This requires a trustworthy key and an idempotent write.
- Push the window into the partition filter so only partitions in the window are rescanned.
- Reconcile periodically: a weekly count and checksum comparison per partition, with a full refresh as the repair.
- Handle deletes separately: an overlap window does not see rows that vanish.
Industry example
dbt's documented incremental-model pattern filters on a timestamp and recommends a lookback buffer when data arrives late, merging on a unique_key. Stream processors reach the same idea through allowed lateness: Apache Flink and Apache Beam keep window state open beyond the watermark for a bounded period and fire updated results for late elements.
Failure scenarios
- Window shorter than real lateness: rows disappear in low numbers; detected only by reconciliation.
- No unique key: the overlap duplicates rows, and totals inflate by exactly the overlap fraction.
- Window too large: a 30-day window on a 2 TB-a-day table reprocesses 60 TB per run; cost jumps and the run overruns its slot.
- Clock skew larger than the window, after a bad deploy to one server.
Trade-offs
| Window | Gains | Pays |
|---|---|---|
| Small (hours) | Cheap runs | Misses very late rows |
| Large (days) | Catches more | Rescan cost grows linearly, and slower runs |
| Plus periodic full refresh | Bounded error | A scheduled expensive job |
When not to use it
Skip it where a log-based CDC feed gives ordered events by commit position; the stream has no timestamp problem to patch. For tables under about 20 GB, a full reload is simpler and has no state at all.
Interview question
Q: Rows are missing from a table loaded incrementally on updated_at. How do you find out why and fix it without a full reload?
What a strong answer covers: the commit-time versus timestamp gap; measuring lateness to size a window; merging by key for idempotency; handling deletes; reconciliation as the detection mechanism; and a full refresh as the occasional backstop.
Quick check
Quiz: The p99.9 lateness is 6 hours and the job runs hourly. What lookback do you set? About 8 hours, with a unique-key merge.
Flashcard: Why must an overlap window merge by key? — Otherwise rows seen twice are inserted twice.