How comparison works

The checksum, the bisection, the segmentation strategies, and what each one reveals

You do not need this page to use reconciliation. Read it when you want to know what the queries actually do — because you are reviewing them, because you are reasoning about cost, or because you need to tell a security reviewer exactly what leaves the database.

The row checksum

Each row is reduced to a single 48-bit signed integer:

row_checksum = int(first 12 hex digits of MD5(col1 || '|' || col2 || ...)) - 2^47

Three details matter:

• Columns are cast to text and joined with | before hashing, in the order the comparison resolved them. NULL becomes the sentinel <NULL>, so a null and an empty string are distinguishable.
• Only 48 of the 128 bits are kept. That leaves room to sum millions of row checksums inside a 64-bit integer without overflowing, which is the whole trick — the aggregate is computed by the database, not by us.
• Subtracting 2^47 centres the values around zero, so a sum over many rows drifts towards zero rather than towards the top of the range.

A segment’s checksum is then simply:

segment_checksum = SUM(row_checksum)

Every dialect gets its own expression for the same arithmetic — HASHBYTES on SQL Server, STANDARD_HASH on Oracle, MD5 plus a base conversion elsewhere — so two different platforms produce the same number for the same data. Which hash function is used is negotiated per reconciliation.

What leaves the database, in full: one row per query containing a count and a sum. No row values, no keys, no column contents. That is true of the quick check and of every level of the drill-down, unless you explicitly raise the privacy level.

The quick check

One query per side:

SELECT COUNT(*)        AS row_count,
       SUM(<row_checksum expression>) AS checksum
FROM (<your dataset>) AS sub

If both numbers agree, the datasets are identical over the compared columns, and the run is done. A 500-million-row table costs one aggregate scan per side to clear.

The bisection drill-down

When the totals disagree, the run needs to find where. It does that by splitting the key space and re-asking the same question of each piece.

1. Split the key range into N segments

   bisection.factor (default 32, maximum 1024) sets N. The boundaries come from the segmentation strategy below.

2. Compare every segment in one query per side

   Not N queries — one. The counts and checksums for all N segments come back from a single bucketed aggregate per side, so a level costs two queries regardless of the factor.

3. Recurse into the segments that differ; ignore the ones that agree

   A segment whose count and checksum match on both sides is identical and is never queried again.

4. Stop at the threshold

   Drilling stops when a segment’s row count falls below bisection.threshold (default 16384), when --depth is reached, or when the segment cannot be split any further. Those segments are the run’s mismatch leaves.

Because each level discards everything that matched, the number of queries scales with the logarithm of the row count for data that is mostly identical. Locating one bad row in ten million takes a handful of round trips.

The inverse also holds, and it is the one failure mode to know about. Bisection is efficient because most segments match. If most rows differ — a broken backfill, a wrong join, comparing two unrelated tables — nothing can be discarded, every segment recurses, and the drill degenerates into thousands of queries each scanning a slice of the table.

If a drill reports thousands of leaves or runs for minutes, stop it. Localise with an aggregate comparison first — one query per side tells you which groups diverge — then drill a dataset narrowed to those groups.

Segmentation strategies

How the key space is divided is configurable, and the choice is a trade-off between drill efficiency and what the boundaries reveal.

bisection.strategy	How segments are formed	What the queries expose	Behaviour
quantile (default)	NTILE-based split of the key range	Key boundary values	Recursive, multi-level
hash	MOD(hash(key), N) bucket assignment	Nothing about key values at the top level	Hash buckets first, then quantile within a bucket
time	Fixed periods of a time column	Time boundaries	Time buckets first, then quantile within a bucket
auto	Same as quantile	Same as quantile	Same as quantile

When to choose something other than the default

hash — sensitive keys

bisection:
  strategy: hash
  factor: 64

Segment boundaries are hash buckets, so the queries and the audit log contain no key values at the top level. Use it when the key is itself sensitive — an account number, a national id, a customer email.

The cost is locality: hash buckets scatter across the table, so the database cannot use a key index to prune a bucket the way it can prune a range.

time — time-partitioned tables

bisection:
  strategy: time
  time_column: created_at
  time_granularity: day

Segments are calendar periods (hour, day, week, month, quarter, year). On a table partitioned or clustered by that column this is the cheapest possible split — a clean day is dismissed with a partition-pruned query — and the findings read naturally: “the 17th is wrong”.

This is the right strategy for validating an incremental pipeline, where differences cluster by load date.

Privacy levels

By default nothing but counts and checksums is retrieved. reporting.level opts into more, per reconciliation:

reporting.level	Retrieved	Use for
count_only (default)	COUNT(*) and SUM(checksum)	Detecting and locating differences without exposing data.
with_keys	…plus the key values of differing rows	Handing someone an exact list of affected ids.
detailed	…plus sample row values	Debugging a specific difference in place.

reporting:
  level: with_keys
  sample_limit: 100          # cap on rows returned at with_keys or detailed

detailed additionally requires consent_acknowledged: true — a deliberate speed bump, because it is the one setting that puts row values into a run record.

You rarely need either. count_only already tells you which key ranges differ, and every mismatch leaf carries ready-to-run SQL you can execute yourself against the two databases. That keeps row values in your SQL client and out of the audit log — see investigation queries.

Cost, in queries

Stage	Queries
Quick check	2 — one per side
One drill level	2 — one bucketed aggregate per side, whatever the factor
A drill over mostly-identical data	2 × (levels needed), typically a handful
An aggregate comparison	2 per hierarchy level entered, and only diverging groups descend

What each query scans depends on your schema, which is why key column indexing matters more than any setting on this page. Running locally covers the pre-run scan estimate and the gate flags.

Next

Comparison modes

Choosing between counts, checksums and aggregate measures.

Investigating results

Reading a segment tree and following a leaf to the rows.