Comparison modes

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.

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.

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:

per_column[column].per_measure[measure]

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

per_measure[measure]

The threshold for this measure, at every level.

per_column[column]

The threshold for every measure at this level.

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.

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.

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

Row count mode

Row checksum mode

Aggregate mode

Measures

The group hierarchy

Thresholds

Hash algorithms

Next

How comparison works

Time windows and cutoffs

Investigating results

Configuration reference

​Row count mode

​Row checksum mode

​Aggregate mode

​Measures

​The group hierarchy

​Thresholds

​Hash algorithms

​Next

How comparison works

Time windows and cutoffs

Investigating results

Configuration reference

Row count mode

Row checksum mode

Aggregate mode

Measures

The group hierarchy

Thresholds

Hash algorithms

Next