How comparison works

You do not need this page to use reconciliation. Read it when you want to know what the queries do: to review them, to estimate their cost, or 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:

Each column is converted to text, and the values are joined with | before hashing, in the order the comparison resolved them. NULL becomes the sentinel <NULL>, so a null and an empty string are different. Decimal, floating-point, timestamp, boolean and UUID columns get one text form on both sides first, so the same value gives the same text on two different databases. For numbers, that text form has a precision: see numeric precision.
Only 48 of the 128 bits are kept. Thus the sum of millions of row checksums fits in a 64-bit integer without overflow. This lets the database calculate the sum itself.
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)

Each dialect has its own expression for the same arithmetic: HASHBYTES on SQL Server, STANDARD_HASH on Oracle, and MD5 with a base conversion on the other platforms. Thus two different platforms give the same number for the same data. Which hash function is used is negotiated per reconciliation. On Oracle, STANDARD_HASH accepts at most 4000 bytes of joined text. To compare longer rows, the connection’s user needs the optional grant EXECUTE ON SYS.DBMS_CRYPTO. Without it, a longer row fails the reconciliation with ORA-01489. See step 4 of the Oracle integration.

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.

Split the key range into N segments

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

Compare every segment in one query per side

One query per side, not N queries. A boundary query on the source finds the N ranges. Then a single bucketed aggregate per side returns the counts and checksums of all N segments. Thus the number of queries for each split does not depend on the factor.

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.

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.

bisection.enabled: false keeps a reconciliation to the quick check. run --auto-drill, recheck --drill and every backend run (ad-hoc, scheduled or triggered) then do not drill into it, also when --drill asks for a drill-down. Only run-drill and drill-deeper drill into it, because you start them to drill. A bisection: block without enabled is enabled, and so is a reconciliation without a bisection: block. 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 opposite is also true, and it is the one failure mode to know. Bisection is efficient because most segments match. If most rows differ (a broken backfill, a wrong join, two unrelated tables), no segment can be discarded. Every segment is split again, and the drill-down becomes thousands of queries, each of which scans a part of the table.If a drill-down reports thousands of leaves or runs for minutes, stop it. First find the area with an aggregate comparison: one query per side tells you which groups differ. Then drill down into a dataset that contains only 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
time: time-partitioned tables

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 itself is sensitive, for example an account number, a national id or 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.

bisection:
  strategy: time
  time_column: created_at
  time_granularity: day

Segments are calendar periods (hour, day, week, month, quarter, year). On a table that is partitioned or clustered by that column, this is the cheapest split: one partition-pruned query clears a day with no differences. The findings are easy to read: “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, in a local run only.

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

detailed also requires consent_acknowledged: true. This is a deliberate extra step, because detailed is the only setting that puts row values into a run record: the local audit log. detailed works only in a local run, which you start with synq-recon on your own machine. A backend run never reads row values. Coalesce Quality refuses to save, promote or run a suite with reporting.level: detailed, with a message that names the reconciliations. Use with_keys or count_only in suites that run there. A local run at detailed shows the values of the mismatched rows in the terminal, and writes them into the audit log file of --audit-log. Each value is shown as the database holds it: text as stored, a decimal with all its digits, a timestamp in RFC 3339 in UTC, a date as the day, a boolean as true or false, and NULL as <NULL>. Row values never go to your workspace. When a local run reports its results, the uploaded audit log has no row values. It keeps the keys, the mismatch types and the row hashes, as at with_keys. Coalesce Quality also removes row values from each audit log that it receives, for example from an older synq-recon.

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. Thus row values stay in your SQL client and out of the audit log. See investigation queries.

Cost, in queries

Stage	Queries
Quick check	2: one per side
Start of a drill-down	2: the highest key on each side
Each segment that is split	5: a count and checksum of the segment on each side, a boundary query on the source, then one bucketed aggregate per side, for any factor. The first split uses the quick check’s totals, so it costs 3
A drill-down over data that is mostly identical	5 × (number of segments split), plus the queries at the start. Usually a small number
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.

Comparison modes

Choosing between counts, checksums and aggregate measures.

Investigating results

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

Get Started

Alerts

Monitors

Monitors Types

Reconciliations

Data Products

Scout AI

Ownership

Incidents

Analytics

Lineage

BI Integrations

Data Warehouse Integrations

Transactional databases

Code Integrations

dbt Integrations

SQLMesh Integrations

Coalesce Integrations

Orchestration Tool Integrations

Alerting Integrations

Catalogs

Google Cloud Integrations

Admin

Security

Support

Developer API

The row checksum

The quick check

The bisection drill-down

Segmentation strategies

When to choose something other than the default

Privacy levels

Cost, in queries

Next

Comparison modes

Investigating results

​The row checksum

​The quick check

​The bisection drill-down

​Segmentation strategies

​When to choose something other than the default

​Privacy levels

​Cost, in queries

​Next

Comparison modes

Investigating results

The row checksum

The quick check

The bisection drill-down

Segmentation strategies

When to choose something other than the default

Privacy levels

Cost, in queries

Next