Forward Deployed Engineering Tutorial · Integration Engineering · Lesson 12 of 24

Incremental Sync and Backfills

Keep data in sync with cursors and watermarks, run backfills safely, and make loads idempotent with upserts.

Create a free account to save this page, take notes and track your progress. Sign in

Full loads versus incremental sync

Reloading everything every night is simple but slow and hard on the source. Incremental sync fetches only what changed since the last run, using a watermark (for example the highest updated_at seen) or a change feed.

Watermark sync with an overlap windowTry it yourself

source = {1: ("ana", 100), 2: ("ben", 105), 3: ("cho", 110)}       # id -> (name, updated_at)
target, watermark = {}, 0

def sync(overlap=5):
    global watermark
    changed = {k: v for k, v in source.items() if v[1] > watermark - overlap}
    for k, v in changed.items():
        target[k] = v                                               # upsert by id
    watermark = max([v[1] for v in source.values()] + [watermark])
    return len(changed)

print("run 1 read", sync(), "rows; watermark", watermark)
source[2] = ("benjamin", 112)                                      # update
source[4] = ("dan", 109)                                           # late commit with an older timestamp
print("run 2 read", sync(), "rows; watermark", watermark)
print("target:", target)

Output

run 1 read 3 rows; watermark 110
run 2 read 3 rows; watermark 112
target: {1: ('ana', 100), 2: ('benjamin', 112), 3: ('cho', 110), 4: ('dan', 109)}

Make loads idempotent

Use upserts keyed by the source ID, so re-running a sync (after a crash, or with an overlap window) gives the same result. Re-read a small overlap before the watermark to catch late commits and clock skew.

Backfills

The first historical load can be millions of records. Throttle it, run it in chunks with checkpoints so it can resume, schedule it off-peak, and agree it with the customer.

Test yourself

Why re-read a small overlap window before the watermark?

To catch rows committed late with older timestamps
To make syncs slower
To skip duplicates
It's required by OAuth

Show answer

Answer: To catch rows committed late with older timestamps

With idempotent upserts, re-reading a few minutes is harmless and prevents gaps.

Was this page helpful?