Row count checks tell you when records go missing. They don't tell you when a record that exists on both sides has quietly picked up the wrong value in a single field. For that, you need a checksum comparison, and adding one to a pipeline that's already running is less disruptive than it sounds.
What a checksum comparison actually does
For each record, you compute a hash of the fields that matter (a concatenation of the relevant column values, run through a standard hash function) on both the source and the destination. If the hashes match, the record is identical where it counts. If they don't, something about that record's content has diverged, even though the record itself still exists in both places.
This catches the failure mode row counts miss entirely: a price that got rounded differently, a status field that didn't propagate on the last update, a timestamp stored in the wrong timezone. All present, all technically "synced," all quietly wrong.
Step 1: Decide which fields belong in the hash
Don't hash every column. Fields that are expected to differ between systems, like an internal ID that gets regenerated on the destination side, will produce a permanent false positive if included. Pick the fields that represent the actual business meaning of the record: price, status, quantity, whatever the downstream consumers of this data actually rely on being correct.
Step 2: Compute the hash consistently on both sides
The hash function itself matters less than consistency in how you build the input string. Concatenate fields in the same order, with the same null handling, on both source and destination. A record where the source stores an empty string and the destination stores null will hash differently even if every human would consider them equivalent, so decide on a normalization rule up front and apply it identically in both places.
Step 3: Store hashes alongside the data, not just at comparison time
Computing the source hash fresh on every comparison run is more expensive than it needs to be. Storing a content_hash column on the source record, updated whenever the record changes, means the comparison job only has to compute the destination hash and do a lookup, not recompute both sides from scratch every run.
Step 4: Run the comparison as its own job, on a sample first
Don't try to hash-compare every record in a large table on day one. Start with a random sample, get the false positive rate down to something trustworthy by fixing normalization issues, then expand coverage. A comparison job that's noisy on day one erodes trust in the whole system before it's had a chance to prove useful.
Step 5: Log which fields actually caused the mismatch
A hash mismatch on its own only tells you two records disagree, not why. If you can afford it, store the individual field values alongside the hash so a mismatch investigation doesn't require re-querying both systems from scratch. That single design choice is usually the difference between a five-minute investigation and a half-day one.
Handling scale without hashing everything on every run
For large tables, recomputing and comparing hashes for every row on every run gets expensive fast. A practical middle ground is to only recompute the source hash when a record's updated_at timestamp changes, and only pull the destination hash for records where the source hash has changed since the last comparison run. This turns a full-table scan into an incremental check that scales with how much actually changed, not with the total size of the table, which matters a lot once you're dealing with tables in the tens of millions of rows.
Handling nullable and optional fields correctly
Nullable fields cause more checksum false positives than any other single issue. A field that's NULL on one side and an empty string on the other will produce different hashes even though many applications treat those as functionally equivalent. Before computing any hash, normalize null handling explicitly: pick one canonical representation (empty string, a sentinel value, or consistent null) and apply the same transformation on both source and destination before hashing. Skipping this step is the single most common reason a newly built checksum comparison reports a wall of false positives in its first week.
Deciding how often to run the comparison
Running checksum comparison on every single write is usually overkill and adds latency to your write path for marginal benefit. A scheduled batch comparison, running every few hours or nightly depending on how quickly you need to detect drift, catches the same issues without coupling the comparison job's performance to your application's write path. Reserve real-time, per-write comparison for the small number of tables where even a few hours of undetected drift would be genuinely costly.
Where this fits with existing test coverage
Unit and integration tests validate that your pipeline's transformation logic does what it's supposed to do against known inputs. A checksum comparison validates something different: that the logic is still producing correct results against the actual, messy state of production data right now. Tools built for data quality validation, like Great Expectations, can wrap this pattern into a reusable framework instead of a one-off script per table, which matters once you're maintaining this across more than a handful of tables.
If your pipeline already uses Apache Kafka or a similar event stream for propagation, computing the hash at write time as part of the same event handler avoids a separate batch job entirely and keeps the hash always current.
Handling records that get deleted rather than updated
Checksum comparison naturally covers records that exist on both sides but disagree on content. It doesn't automatically catch a record that got deleted from the source and never removed from the destination, since there's nothing on the source side left to compare against. Pair the checksum job with a separate check for records present in the destination but absent from the source, or extend your comparison to explicitly look for orphaned destination records within the same job. Skipping this half of the picture leaves a real gap even after checksum comparison is fully working for everything else.
A quick sanity check before you ship it
Before trusting the comparison job's output, deliberately introduce a known mismatch (change one field on the destination without updating the source) and confirm the job actually flags it. It sounds obvious, but a comparison job with a normalization bug can silently report zero mismatches indefinitely, which looks identical to a healthy pipeline right up until someone finds a real discrepancy the job should have caught.
Further reading
This is one piece of a broader approach to catching silent pipeline failures. 137Foundry's data integration team wrote a longer breakdown of why passing tests don't catch this class of bug in the first place: Why Data Sync Jobs Pass Every Test But Still Drift From the Source.
For the hashing and comparison mechanics themselves, PostgreSQL's documentation covers built-in hashing functions well, and dbt has become a common place teams implement this kind of transformation-layer validation as part of their existing modeling workflow. For teams working with document stores rather than relational tables, MongoDB's aggregation pipeline documentation covers equivalent hashing and comparison patterns for that data model.
Top comments (0)