Comparison modes

Row counts, row checksums and aggregate measures — which one answers your question

Every reconciliation has a mode:, and it decides what “agree” means.

Mode	Compares	Catches	Misses
row_count	Row counts	Missing and extra rows	Any change to a value
row_checksum (default)	Row counts and a checksum over the compared columns	Any difference at all	Nothing, within the compared columns
aggregate	Grouped measures — SUM, COUNT, AVG, MIN, MAX — with a tolerance	Divergence in totals per group	Offsetting errors within a group

When in doubt, use row_checksum. It is the default, it costs one query per side, and it does not miss anything. Reach for the others when you have a specific reason: row_count when only presence matters, aggregate when the two sides are not row-for-row comparable in the first place.

Row count mode

reconciliations:
  orders-present:
    source:
      connection: gateway
      table: public.orders
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.ORDERS
    key_columns: [order_id]
    mode: row_count

The cheapest possible comparison: COUNT(*) per side. Use it when the target is known to transform values — so a checksum would never match — but must not lose or duplicate rows. A drill-down still works, narrowing which key ranges have the wrong count.

Because nothing here reads a column, the two sides do not have to have the same columns. A target carrying columns the source has never heard of — surrogate keys, load timestamps, whole extra dimensions bolted on by the warehouse — is compared as it stands, and you do not have to list columns to make the two sides look alike. Only the key columns have to exist on both sides, so the drill-down can range over them.

Row checksum mode

reconciliations:
  orders-identical:
    source:
      connection: gateway
      table: public.orders
      exclude_columns: [synced_at, etl_batch_id]
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.ORDERS
      exclude_columns: [synced_at, etl_batch_id]
    key_columns: [order_id]
    mode: row_checksum

Each row is hashed across the compared columns, and the hashes are summed per side. One query per side returns the count and that sum, so a single changed character in a single row changes the total.

The columns you compare are the ones you decide to compare. columns: and exclude_columns: are how you keep pipeline bookkeeping — load timestamps, batch ids, surrogate keys generated per side — from being reported as a difference for the rest of time.

Each row’s hash runs over the compared columns in order, position by position, so both sides must present the same number of columns. A target that is wider than the source is the usual reason a run stops with source has N columns but target has M columns; name the columns you actually want compared, on both sides:

reconciliations:
  orders-common-columns:
    source:
      connection: gateway
      table: public.orders
      columns: [order_id, customer_id, amount, status]
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.ORDERS
      columns: [order_id, customer_id, amount, status]
    key_columns: [order_id]
    mode: row_checksum

exclude_columns: on the wider side does the same job when it is easier to name what to drop than what to keep. If you only care that no rows went missing, mode: row_count compares the two sides with no column list at all.

full is an accepted legacy spelling of row_checksum. Suites round-tripped through synq-recon suite yaml come back written as row_checksum.

Aggregate mode

Row-level comparison is the wrong instrument when the target is a summary of the source, when row identity differs between the two systems, or when you care about the numbers rather than the rows. Aggregate mode compares measures, grouped, with a tolerance.

reconciliations:
  daily-revenue:
    title: Daily revenue by store
    source:
      connection: operational
      table: public.sales
      columns: [sale_date, store_id, amount, sale_id]
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.SALES
      columns: [sale_date, store_id, amount, sale_id]
    key_columns: [sale_date]
    mode: aggregate
    aggregate:
      measures:
        - column: amount
          function: [SUM, COUNT]
      group_columns: [sale_date, store_id]
      thresholds:
        absolute: 0.01
        percentage: 0.001

Measures

A measure is a column and one or more functions. function: takes a single value or a list, so the block above expands to two measures: SUM(amount) and COUNT(amount). Available functions are SUM, COUNT, AVG, MIN and MAX.

Pair a SUM with a COUNT on the same column. SUM alone cannot distinguish “one row is missing” from “one value is wrong” — the total is short either way. With the count beside it, the two are immediately distinguishable, and it costs nothing extra because both are computed in the same query.

The group hierarchy

group_columns: is a hierarchy, evaluated outermost-first. The comparison groups by the first column; only groups that diverge are then broken down by the second, and so on. Groups that agree are never queried again.

With group_columns: [sale_date, store_id], a run over a discrepancy on one store on one day looks like this:

Coalesce Quality Reconciliation - Aggregate Check
================================================

Reconciliation: Daily revenue by store (daily-revenue)
Mode: aggregate
Group Columns: sale_date -> store_id
Measures: SUM(amount), COUNT(amount)

Total Groups:    10
Matched Groups:  8
Mismatch Groups: 2

Status: MISMATCH

Drill-Down Tree:
  Level 0: GROUP BY sale_date (10 groups, 8 matched)
    - [AGGREGATE_DIVERGENCE_TYPE_SUBGROUP_MISMATCH] sale_date=2026-01-08 00:00:00
        Source _measure_sum_amount: 6840
        Source _measure_count_amount: 30
        Target _measure_sum_amount: 6829.5
        Target _measure_count_amount: 29
        Diff   _measure_sum_amount: -10.5
        Diff   _measure_count_amount: -1
      Level 1: GROUP BY store_id (3 groups, 2 matched)
        - [AGGREGATE_DIVERGENCE_TYPE_MEASURE_MISMATCH] sale_date=2026-01-08 00:00:00, store_id=STORE-1
            Source _measure_sum_amount: 2130
            Source _measure_count_amount: 10
            Target _measure_sum_amount: 2119.5
            Target _measure_count_amount: 9
            Diff   _measure_sum_amount: -10.5
            Diff   _measure_count_amount: -1

Eight of ten days agreed on the first pass. Reading down the tree: on 8 January the target is one row and 10.5 short on the total, and it is store STORE-1’s numbers that are wrong. Two levels, and the finding is specific enough to act on.

Each measure appears as _measure_<function>_<column> — the alias the generated SQL gives it, which is also what you will find in -o json and in the audit log. Each divergent node is labelled with why it is there:

Divergence type	Meaning
MEASURE_MISMATCH	This group’s own measures differ.
SUBGROUP_MISMATCH	The group differs because something below it does — keep reading down.

If you omit group_columns, the hierarchy falls back to [key_column].

Thresholds

Floating-point arithmetic, rounding, and currency conversion all produce differences that are not errors. A threshold is what separates them from the ones that are.

thresholds:
  absolute: 0.01              # ignore differences up to this magnitude
  percentage: 0.001           # ...or up to 0.1%
  percentage_mode: source     # percentage of: source (default), target, or symmetric

A group passes if it is within either limit. absolute: 0 means exact agreement.

Overrides get more specific from there — per grouping column, and per measure:

thresholds:
  absolute: 0.01
  percentage: 0.001

  # Looser at the leaf level, where individual values are small
  per_column:
    product:
      absolute: 1.0
      per_measure:
        "SUM(revenue)":
          absolute: 10.0

  # An average is noisier than a sum, everywhere
  per_measure:
    "AVG(revenue)":
      percentage: 0.05

Resolution order, most specific wins:

1. per_column[column].per_measure[measure]

   The threshold for this measure at this level of the hierarchy.

2. per_measure[measure]

   The threshold for this measure, at every level.

3. per_column[column]

   The threshold for every measure at this level.

4. thresholds

   The suite-wide default.

Measures are keyed by their rendered name, "FUNC(column)" — "SUM(revenue)", "COUNT(amount)" — and the quotes are needed because of the parentheses.

A run whose only divergences are inside their thresholds reports within_threshold rather than mismatched. That is a distinct status you can act on separately — see --fail-on.

Hash algorithms

row_checksum needs a hash function that both databases compute identically. The tool negotiates the best one available on both sides, so you normally do not think about it:

Algorithm	Why it might be chosen
farm_fingerprint	Fastest where both sides support it — notably BigQuery.
xxhash64	Fast, same-platform comparisons (Databricks).
md5	The universal fallback. Supported everywhere.

Set hash_algorithm: explicitly (auto, md5, farm_fingerprint, xxhash64) only when you have a reason to pin it — for example, to keep checksums stable across a platform migration.

Next

How comparison works

The checksum formula, bisection, segmentation strategies and privacy levels.

Time windows and cutoffs

Aggregate mode on a lagging target needs a cutoff. Here is why.

Investigating results

Reading a drill tree, and following a finding to the rows.

Configuration reference

Every field of aggregate: and thresholds:, generated.