Testing a sync pipeline that must never lose a row

The redfly Sync Service has one job: every row that is added, changed or deleted in the source database must show up, correctly, in the copy. The copy might be Redis sitting next to the database, or a central store on the far side of a slow public internet link. Either way, a missed row is not a minor bug. It is a wrong answer on a report that someone will act on.

Testing that promise is different from testing ordinary application code. The interesting failures do not happen when everything goes smoothly. They happen when the process dies halfway through a batch, when the network drops for an hour, when a row is edited twice in the time it takes to send it once. So our tests are built around those moments.

The design we are testing

The tests only make sense against the design, so here it is in brief. Changes are picked up from the source using the mechanism each engine offers: change tracking read on a short interval on SQL Server, logical replication streamed on PostgreSQL, change streams streamed on MongoDB. Each change is queued, and each table remembers its own position in that queue.

Delivery is at-least-once: a change can arrive at the copy more than once, but never zero times. On arrival, the copy reconciles: it applies the change if it is new, and recognises it if it has already been applied. We do not claim, and do not test for, a guarantee that each change arrives one time only. We test that nothing is lost and that duplicates do no harm.

Deterministic replay of change sets

The foundation of the suite is a library of recorded change sets: sequences of inserts, updates and deletes against known tables, with the expected end state of each table written down beside them. Some are small and pointed, such as insert a row, update it, delete it, then insert a row with the same key again. Others are long and noisy, generated from a seed (a starting number that makes a random sequence repeatable) so they can be reproduced exactly.

Because the same change set can be replayed any number of times, we can run it through the pipeline under different conditions and compare results. The end state should be identical whether the run was smooth or interrupted six times. If it is not, the interruption exposed something, and the seed lets us reproduce it on demand.

Killing the process mid-batch

The first hostile condition is a crash. A test program runs the sync service against a replayed change set and terminates the process at a chosen point: after a batch is read from the source but before it is sent, after it is sent but before the position is saved, in the middle of writing to the copy. The service is then restarted and allowed to finish.

The check afterwards is simple. Every table's saved position must be at or before the last change actually applied to the copy, never after it. If the position ran ahead of the data, a restart would skip rows, and that is exactly the loss we are guarding against. Positions that lag behind are fine; they just mean some changes are re-sent, and the reconciliation on arrival absorbs them.

Dropping the network

The second condition is a lost link. For the remote case, where changes leave a site and travel over the public internet, we run the pipeline with a network layer we can cut and restore on command. Tests cut it at awkward moments: mid-transfer, during the acknowledgement (the copy's confirmation that a batch arrived), repeatedly in quick succession.

While the link is down, changes must keep queueing on the source side, compressed and encrypted, and the source database must not be read any harder than usual. When the link returns, each table must resume from its own saved position, not from the beginning and not from where some other table happened to be. A site that was offline for a day should catch up on its own once the link is back, and the tests check that it does.

Comparing source and copy row by row

Every scenario ends the same way. Once the pipeline reports that it has caught up, a separate checker reads every row from the source and every row from the copy and compares them. Not counts; rows. A count can match while two different rows are wrong. The checker reports each row that is missing from the copy, each row present in the copy that no longer exists in the source, and each row whose fields differ.

This checker is deliberately independent of the sync code. It shares no logic with it, so a bug in how the pipeline understands a change cannot also hide in how the checker verifies it. It is slow, and that is acceptable; it runs in the test suite, not in production.

A count that matches proves nothing; only the rows do.

What this buys in production

The same at-least-once design that the tests exercise is what lets the service run over links that cannot be trusted. Drift detection and backfill in production are the checker's slower cousins: they look for the same kinds of disagreement between source and copy and repair them. When the tests pass, we know the repair paths work, because the tests forced them to run.

This is the sync service that sits behind redfly, keeping Redis in step with a database on a local link and carrying warehouse data to a central store over a remote one.