driftwatch: trust, but verify

It started with the same book. Again. Two of the last things I put here, a rich-text CRDT and a first pull request to Automerge, both came out of Martin Kleppmann's Designing Data-Intensive Applications and the collaborative-editing rabbit hole it dropped me into. This one comes from the same book, but the far end of it: the final chapter, where Kleppmann (and, in the second edition, Chris Riccomini) stops describing how data systems work and starts arguing about how they ought to. One of those arguments wouldn't leave me alone. He calls it trust, but verify.

The argument is simple. A grown-up data system should not assume its data is correct; it should keep checking. We copy our databases everywhere, a warehouse for analytics, a search index, a read replica, a cache, and those copies quietly drift out of sync. A dropped change-data-capture event, an off-by-one in a materialized view, a backfill that half-ran. Usually a customer or an auditor finds it before you do. Kleppmann's point is that auditing your own data ought to be a thing you build on purpose, not a thing you wish you had after the incident review.

The odd part is that for the warehouse-and-pipelines world, nobody really built it. There's a good open-source tool that got archived, a proprietary one that runs on a schedule, a continuous one that only speaks MySQL. The advice you actually find in every CDC tutorial is the same shrug: write your own reconciliation job. So I wrote the one I would want to use, once, properly. It's called driftwatch.

What it does

driftwatch compares a derived table against its source of truth and tells you which rows are different. You point it at a Postgres and a Snowflake, or at a local DuckDB file if you just want to try it, give it a primary key, and run it. The exit code is the whole contract: 0 in sync, 1 drift, 2 error, 3 bad config. Drop it in CI or a cron and the exit code does the rest.

driftwatch: orders - DRIFT
  drift keys: 3 total (missing=1, extra=1, changed=1)
    [changed] 250
    [missing] 500
    [extra]   999

Two ideas do all of the work, and they are the only two that matter.

It only reads rows that might be wrong. Both sides hash ranges of rows and compare the hashes, not the rows. Ranges whose hashes match are skipped without reading a single row; driftwatch only descends into the ranges that disagree. A clean ten-million-row table gets verified by reading zero rows. Finding a handful of bad rows in ten million reads about fifteen thousand of them, 0.15% of the table. That number is measured, not hoped for, and it is the difference between a check you can run constantly and one you run once a quarter and dread.

It tells lag apart from drift. A naive diff reports every row the copy has not caught up on yet, which is noise, and noise is how you teach people to ignore an alert. driftwatch takes a cutoff time at the start of a run and only compares rows old enough to have synced, then rechecks the suspects a moment later and drops any that have since reconciled. It stays quiet until something is actually wrong. This is the part a plain diff gets wrong, and it is the whole difference between a tool you keep and a tool you mute after a week.

The part I didn't trust

Here's the bit I'm actually proud of, and it isn't the code. I didn't trust my own demo. A thousand rows passing proves nothing about scale or correctness, so I ran the thing against real Postgres and real DuckDB at a hundred thousand, a million, ten million rows, and then I ran a whole matrix of nastier scenarios on top: every density of drift from one row to the whole table, integer and composite and UUID keys, a wide table of every column type, high-churn lag, four engine pairings, and a fifty-trial fuzz that invents a random dataset and checks the answer is exactly right.

The matrix did the one thing a test suite is for. It found things wrong.

It found that floats compared at the default precision could disagree between Postgres and DuckDB on roughly one value in two thousand, a latent false positive sitting in the tool the whole time. It found that composite and string keys were correct but quietly read the entire table instead of pruning. It found that an in-place update that hadn't synced yet would get reported as a phantom extra row, inside the grace window that was supposed to forgive exactly that. None of these show up in a happy-path screenshot. All of them would have shown up for a user eventually, which is the worse way to find out.

So I fixed them. The float renderer now rebuilds the exact value from a double's bits and rounds it the same way C does, so Postgres, DuckDB and Python agree to the last digit and a float comparison is genuinely exact. Composite and string keys now prune like integer keys. Updates inside the grace window stop crying wolf. And the scenarios that caught the bugs are committed in the repo and run in CI on every push, against a real Postgres, so the next regression turns the badge red instead of shipping quietly. Making the float comparison stricter was only safe because the cross-engine check now reruns on every commit. The tests are what let me be bold.

What I decided

Connectors are plugins, not core. Postgres, Snowflake and DuckDB ship in the box, but a new database is a separate package that registers itself through an entry point. Adding ClickHouse or BigQuery never has to touch the engine, which is the only way an open-source tool like this grows a long tail of connectors without the maintainer becoming the bottleneck.

The limitations are written down. Drift scattered thinly across the whole table costs a full scan, because to check changed rows you have to read them, and no cleverness gets around that. Composite keys prune now, but they run a bit slower than integer keys. The Snowflake connector is written to the same contract as the others, but I have no account to verify it against a live warehouse, so I say so. I would rather state the edges plainly than pretend the tool doesn't have any.

It runs locally in a minute. make demo boots a throwaway Postgres, loads a thousand orders into it and into a DuckDB warehouse, breaks the copy three different ways, and shows you driftwatch catching all three. No cloud account, no signup, just the thing working in front of you. Installing it for real is one line:

pip install "driftwatch[postgres,duckdb] @ git+https://github.com/catancs/driftwatch"
driftwatch run -c driftwatch.yaml

There's the symmetry again. The same book that made me accidentally reinvent one of Kleppmann's algorithms, and then go and open a pull request against his CRDT library, has now handed me a third project, this time straight out of its closing argument. Three posts, one book, three different chapters, and this is the one that feels most like simply doing what the book told me to.

The whole tool is four words from a paragraph near the end: trust, but verify. The idea is Kleppmann and Riccomini's. The code is mine, and it's on GitHub.