> ## Documentation Index > Fetch the complete documentation index at: https://docs.synq.io/llms.txt > Use this file to discover all available pages before exploring further. # 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 ``, 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](/reconciliation/comparison-modes#hash-algorithms). **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](#privacy-levels). ## The quick check One query per side: ```sql theme={null} SELECT COUNT(*) AS row_count, SUM() AS checksum FROM () 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. `bisection.factor` (default 32, maximum 1024) sets N. The boundaries come from the segmentation strategy below. 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. A segment whose count and checksum match on both sides is identical and is never queried again. 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](/reconciliation/comparison-modes#aggregate-mode) 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 ```yaml theme={null} 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. ```yaml theme={null} 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. | ```yaml theme={null} 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](/reconciliation/investigating-results#follow-a-finding-to-the-rows). ## 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](/reconciliation/authoring-suites#key-columns-decide-everything) matters more than any setting on this page. [Running locally](/reconciliation/running-locally#gate-a-large-run-before-it-happens) covers the pre-run scan estimate and the gate flags. ## Next Choosing between counts, checksums and aggregate measures. Reading a segment tree and following a leaf to the rows.