Inside the incremental sync engine

Every data product eventually rediscovers the same truth: a full refresh is the only sync strategy that is obviously correct, and it is too slow and too expensive for anything larger than a toy. Everything else is a trade-off between freshness, cost and correctness. This post is a tour of the trade-offs we made.

Three strategies, picked per table

The engine supports three strategies and chooses one per table, not per source. A single Postgres database might have a small plans table on full refresh, an events table on cursor sync, and an orders table on change data capture.

Cursors, and why `updated_at` lies

Cursor sync is simple: remember the highest updated_at you have seen, ask for everything above it next time. In practice updated_at breaks in at least four ways we see every week:

• Not updated on every write — bulk UPDATE statements and ORM shortcuts skip the trigger.
• Clock skew — rows written by replicas or app servers with drifting clocks.
• Long transactions — a row with updated_at = 10:00:01 commits at 10:00:09, after we already read past 10:00:05.
• Precision — timestamps truncated to the second, with thousands of rows sharing one value.

Our cursor reader handles the last two with a lookback window: each run re-reads a configurable overlap (default 10 minutes) behind the cursor and deduplicates on primary key. The cost is a little extra read volume; the benefit is that long transactions stop silently dropping rows.

12345select *
from public.orders
where (updated_at, id) > (:cursor_ts - interval '10 minutes', :cursor_id)
order by updated_at, id
limit 50000;

Change data capture

For Postgres, MySQL and SQL Server we read the database’s own change log: logical replication slots, the binlog, or CDC tables respectively. CDC gives us inserts, updates and deletes in commit order, with seconds of lag, and puts almost no load on the source.

CDC has its own sharp edges. A replication slot that is not consumed will hold WAL on the source until the disk fills — the single most dangerous failure mode in this whole system. We monitor slot lag continuously and, past a threshold the customer sets, drop the slot and fall back to a cursor sync with a full reconciliation, rather than risk their production database.

Landing the changes

Changes land in an append-only staging table, then merge into the modelled table in micro-batches. Each batch is idempotent: replaying it produces the same result, which makes retries and backfills safe.

12345678910111213141516merge into model.orders as t
using (
    select distinct on (id) *
    from staging.orders_changes
    where batch_id = :batch_id
    order by id, source_lsn desc
) as s
on t.id = s.id
when matched and s.op = 'delete' then
    delete
when matched and s.source_lsn > t.source_lsn then
    update set amount = s.amount, status = s.status,
               updated_at = s.updated_at, source_lsn = s.source_lsn
when not matched and s.op <> 'delete' then
    insert (id, amount, status, updated_at, source_lsn)
    values (s.id, s.amount, s.status, s.updated_at, s.source_lsn);

The source_lsn > t.source_lsn guard is what makes out-of-order delivery harmless: an older change can never overwrite a newer one.

By the numbers

Next on the roadmap: CDC for Snowflake and BigQuery via their change streams, and per-table freshness SLAs that surface in the UI as the same badges you see on dashboard tiles. If this is the kind of problem you like, we are hiring.