The Watermark Handoff

One instant, chosen on purpose

The bootstrap loader is about to spend six hours extracting a 40-million-row Oracle table while the source system keeps running — rows will be inserted and updated during the extract. When the managed service takes over incremental syncs afterward, where exactly is the boundary between “the bulk load’s rows” and “the incremental’s rows”?

The naive answer — “whatever existed when the extract finished” — is not an instant at all. Six hours of drift, different per table, unknowable afterward. The pattern’s answer is surgical:

1. Stamp T0 = now into the control table -- BEFORE extraction begins
2. Bulk extract pulls WHERE watermark_col <= T0     (an upper bound)
3. Incremental later pulls WHERE watermark_col >  T0

The two sets meet at one explicitly chosen instant — an exact seam. T0 is not “when we finished”; it is a line drawn first, that both sides then respect.

Overlap over gaps

What about a row inserted at T0 + 10 minutes, while the bulk extract is still running? It fails the <= T0 predicate, so the bulk load skips it — and the first incremental run picks it up with > T0. No gap.

What about a row that existed before T0 but gets updated at T0 + 10 minutes? Depending on scan timing, the bulk extract may have already captured its old version — and the first incremental collects the new version too, because its modification timestamp is now past T0. That row arrives twice.

The design chooses this on purpose: prefer overlap to gap. A gap is invisible — a missing row announces nothing. Overlap is mechanical to handle, provided one stated assumption holds: downstream consumption is idempotent on the business key (a later version of a row supersedes an earlier one). Say this assumption out loud in the design doc, because it is load-bearing. If some consumer appends instead of upserting, overlap becomes duplication — and you want to discover that in review, not in the quarterly numbers.

One residual gap the seam alone does not close: a long-running transaction can commit after the extract’s scan has passed while carrying a modification timestamp at or below T0 — the bulk scan never saw the row, and > T0 never selects it. The guard is a lag: stamp T0 slightly behind wall-clock (or have the first incremental pull from T0 - delta, sized to the longest transaction the source allows), converting the residual gap into a little more overlap — which this section already taught you to prefer.

The honest failure mode — teach it as a pair

Now the scenario that makes this lesson matter. The bulk run stamps T0, starts extracting the big table, and dies four hours in — VM eviction, whatever. The control row says T0. The lake holds maybe 60% of the <= T0 rows. If anyone now lets the incremental service start, it happily syncs from > T0 — and the missing 40% are orphaned forever: too old for every future incremental, absent from the bulk set, invisible on every green dashboard afterward.

The stamp-first design creates this failure mode, and the same design names its containment:

Cutover is gated on reconciliation, not on “the job said success.” The seam is real the moment reconciliation (Lesson 3.3) proves the <= T0 set is complete in the lake — and not one minute before. The gate is the other half of the pattern; adopting the watermark without the gate is adopting the trap.

Never advance past a hole

Once the incremental service owns the feed, it manages the watermark forward on every run. The rule that keeps years of syncs trustworthy has three clauses — from a production sync engine, worth quoting as law:

After each incremental batch:
  ALL records succeeded  -> advance watermark to max processed timestamp
  ANY record failed      -> advance only to (earliest failure - 1 second)
  failures with unknowable timestamps -> do not advance at all

The watermark may never move past a record that has not landed. Clause 2 deliberately re-processes everything from the first failure onward (overlap again — same idempotency assumption). Clause 3 is the humble one: when you cannot even establish when the failed records were modified, freezing is the only move that cannot orphan data. A frozen watermark pages a human; an optimistically advanced one silently loses rows and pages nobody.

Design the incremental sync loop for a table with watermark column
LAST_MODIFIED. Requirements: per-batch failure accounting; watermark
advancement per the three-clause rule above (all-success / partial-failure /
unknowable-timestamp); overlap tolerated, gaps never; alert when the
watermark fails to advance for 3 consecutive runs.

That last requirement operationalizes the humility: a stuck watermark is not an error state to hide — it is the system correctly refusing to lie, and someone should look.

Key takeaway

Stamp the seam before you extract; let bulk take <= T0 and incremental take > T0; prefer overlap to gaps and state the idempotency assumption that makes overlap safe; recover by resuming against the same seam; and never, under any clause, advance a watermark past a hole. The gate that makes all of it trustworthy — reconciliation — is next.