Incremental sync

After the backfill, the job is to stay current without re-reading everything. Three mechanics do it: a watermark, a reconciliation pass, and a store that does not care what order things arrive in.

Watermarks

A watermark is the high-water mark of what you have processed: the greatest modification timestamp you have seen for an entity. Each run reads only records changed since then.

since = watermark - overlap
read the entity, filtered on its modification timestamp >= since
upsert every row
watermark = max(modification timestamp seen)

Four rules keep a watermark honest:

• Overlap on purpose. Subtract a few minutes before you query. Clock differences, a record committed a moment after your last read, and in-flight writes all mean the boundary is fuzzy. Overlap plus upsert costs a few duplicate reads and prevents a silent hole.
• Advance it from what you read, not from the clock. Setting the watermark to "now" after a run that failed halfway loses everything in between.
• One watermark per entity. Products and orders do not move at the same rate, and a shared watermark is dragged around by whichever is busiest.
• Persist it transactionally with the rows. A watermark advanced before the rows landed is the classic way to lose a batch.

Which field to filter on is per endpoint — check the API Explorer for the modification timestamp and whether it is filterable and sortable. Where an entity exposes no such field, you cannot do a timestamp watermark: fall back to a full re-read on a slower schedule, and use events to catch the changes in between.

Reconciling a webhook gap against an API re-read

Events will be missed. An endpoint gets auto-disabled after a sustained outage, a deploy drops a few minutes, a receiver returns 500 for an hour. The recovery is an API re-read, and the order of operations matters:

1. Establish the gap. From the delivery log and your own arrival timestamps: the window between the last event you handled successfully and the first one after recovery.
2. Try redelivery first. The delivery log can redeliver past events, which is cheaper and preserves the payloads exactly.
3. Otherwise re-read the window from the API, widened generously on both sides, filtered on the modification timestamp.
4. Upsert. Every row you read overwrites what you had. This is only safe because you keyed on the platform's ids.
5. Compare the counts with what you expected, and record the reconciliation so the next person can see the gap was handled.

The reason this works is that a re-read is more authoritative than an event, not less. An event says what was true when it was emitted; a re-read says what is true now. When they disagree, the re-read wins.

Run the same pass on a schedule even when nothing broke — a nightly re-read of the last few days, compared against your store, with an alert on any difference. It is the only thing that tells you the pipeline drifted before somebody else notices.

Out-of-order arrivals

Ordering is best-effort. A redelivered event can arrive after a later one, so order.paid can land before order.created.

Three defences, in order of preference:

1. Last-write-wins on event time. Store the envelope's time alongside the row and ignore an update whose time is older than what you already have. This makes ordering irrelevant.
2. Commutative handling. If applying two events in either order gives the same end state, there is nothing left to get wrong.
3. Re-read on any surprise. An update for an entity you have never seen is not an error — it is a signal to read that entity from the API. Do that instead of dropping it or inventing a placeholder row.

What does not work is a handler that requires the creation event to have arrived first. It passes every test and fails on the first busy afternoon.

A late arrival that is genuinely late

Separate "arrived out of order" from "arrived hours later". A delivery retried across a long outage carries an old time, which is correct — and if you have already published a report for that window, your report is now stale.

Decide the policy before it happens:

• Restate. Recompute the affected window and let the number change. Right, and requires whoever reads it to accept that yesterday's figure can move.
• Freeze. Close a window after a defined lag and route anything later into an adjustment. More work, and what finance usually wants.

Either is defensible. Not having decided is not.

Where to go next

• Delivery guarantees — the transport properties this page defends against.
• Backfill — the first load, and the watermark it leaves behind.
• Modelling — where a restatement policy actually bites.