Comparison modes - Coalesce Quality Documentation

Every reconciliation has a mode:. The mode 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 in the compared columns	A number that differs only past its numeric precision
`aggregate`	Grouped measures (`SUM`, `COUNT`, `AVG`, `MIN`, `MAX`) with a tolerance	Divergence in totals per group	Errors that cancel each other out within a group

If you are not sure, use row_checksum. It is the default, and it costs one query per side. Use the other modes for a specific reason: row_count when only the presence of rows is important, and aggregate when the two sides do not have the same rows.

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 comparison: COUNT(*) per side. Use it when the target changes the values, so that a checksum can never match, but must not lose or duplicate rows. A drill-down still works, and finds which key ranges have the wrong count. This mode reads no column, so the two sides do not need the same columns. A target can have columns that the source does not have, such as surrogate keys, load timestamps or extra dimensions that the warehouse adds. It is compared as it is, and you do not have to list columns to make the two sides equal. Only the key columns have to exist on both sides, so the drill-down can range over them.

A tolerance on the count

A replica that always lags a little, behind a CDC stream or a loader that runs every few minutes, never has exactly the source’s count. row_count.thresholds says how far apart the two counts may be:

    mode: row_count
    row_count:
      thresholds:
        percentage: 0.0005   # 0.05% of the source count

It takes absolute, percentage and percentage_mode, with the same meaning as in aggregate thresholds; per_column and per_measure belong to aggregate mode and are rejected here. The counts are compared as integers. A difference inside the tolerance reports within_threshold and is not drilled into, since the tolerance says it is expected. A difference past it is mismatched and drills down as usual. row_count is read only in row_count mode, and other modes ignore it.

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. You decide which columns to compare. Use columns: and exclude_columns: to keep pipeline bookkeeping columns out of the comparison, such as load timestamps, batch ids, or surrogate keys that each side generates. Otherwise, each run reports them as a difference. 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.

Numeric precision

In row_checksum mode, every value is converted to text before it is hashed. Two databases can write the same number as different text: 5, 5.0 and 5.00, or a floating-point value with a different number of digits. For this reason, decimal and floating-point columns are converted to one text form on both sides. The precision of that text form decides which differences the checksum can find.

Default precision

If you do not set comparison, the column types on the two sides decide the precision:

Source and target columns	Compared at	Audit log warning
Two integers	Every digit	No
Two decimals of the same type, on the same kind of database	Every digit, as the database writes them	No
Two decimals of different types, on the same kind of database	The larger of the two scales	No
Two decimals, on two different kinds of database	The smaller of the two scales	No
An integer and a decimal	The scale of the decimal	No
A decimal with no reported scale, against an integer or against a decimal with no reported scale (but not two decimals of the same type on the same kind of database)	6 decimal places	Yes
Two floating-point columns	15 significant digits, or 6 if one side is single precision	Yes
A floating-point column, and a decimal or an integer	As floating-point values, at the significant digits of the floating-point column	Yes

The scale is the scale that the database reports for the column of your query. The type name alone does not decide it. Thus a Snowflake or Oracle NUMBER with scale 0 is an integer. If only one side reports a scale, the scale of that side is used. The warning for 6 decimal places names the side that does not report its scale. A floating-point value has no fixed scale, so it is compared to a number of significant digits, not to a number of decimal places. A double holds 15 significant digits exactly. Thus 0.1 + 0.2 (0.30000000000000004) and 0.3 agree. NaN, Infinity and -Infinity agree only with the same value on the other side. The warning in the audit log tells you that a match on that column is a match at that precision only.

Between two different kinds of database, floating-point values below about 1e-8 or above about 1e37 can differ in the last of the 15 digits. If a column holds such values, set numeric_scale for it.

Set the precision with `comparison`

The comparison block replaces the default precision. You can write it at the top level of the suite, on a reconciliation, or both.

Field	Type	Default	Description
`comparison.numeric_scale`	Integer from 0 to 18, or `exact`	Not set: the default precision applies	The precision of every decimal and floating-point column.
`comparison.columns`	List	Empty	Settings for one column each.
`comparison.columns[].column`	String, required	—	The column name on the source side, not case-sensitive. With a `column_mapping`, the source column of the pair.
`comparison.columns[].numeric_scale`	Integer from 0 to 18, or `exact`	Not set	The precision of this column.

numeric_scale has no effect on text, timestamp, boolean or UUID columns, or on a pair of two integer columns. An integer paired with a decimal or a floating-point column is compared at the numeric_scale you set. The two values of numeric_scale do different things:

A number of decimal places
exact

comparison:
  numeric_scale: 2

Rounds both sides to this number of decimal places, then compares them. Rounding is half away from zero. Use it when a loader rounds the values on one side, or to compare a floating-point column with a decimal column.A number replaces the default precision and its warning, for decimal and floating-point columns.

comparison:
  numeric_scale: exact

Compares every digit that the column holds:

A decimal is compared at its declared scale. Both sides must report a scale, and the two scales must be equal. An integer has scale 0.
A floating-point value is compared digit for digit. Both sides must be floating-point columns on the same kind of database. Snowflake, Redshift and Db2 cannot write every digit of a floating-point value, so exact is not available for floating-point columns on them.

If a column does not meet these conditions, the reconciliation fails with a message that names the column. It does not round instead.

Rounding is not a tolerance. 10.1249 and 10.1250 differ by much less than 0.01, but at 2 decimal places they become 10.12 and 10.13, which do not agree. To accept a difference up to a limit, compare totals in aggregate mode with thresholds.

Which setting applies

A column can get a setting from four places. The first setting in this list that exists applies:

The entry for the column in the reconciliation’s comparison.columns.
The reconciliation’s comparison.numeric_scale.
The entry for the column in the suite’s comparison.columns.
The suite’s comparison.numeric_scale.

If none of the four exists, the default precision applies. Thus a reconciliation’s numeric_scale applies before a suite’s entry for the same column.

comparison:                     # the suite: every reconciliation
  numeric_scale: 4

reconciliations:
  payments:
    source:
      connection: gateway
      table: public.payments
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.PAYMENTS
    key_columns: [payment_id]
    mode: row_checksum
    comparison:                 # this reconciliation only
      numeric_scale: exact
      columns:
        - column: fx_rate       # the column name on the source side
          numeric_scale: 8

In this suite, payments compares fx_rate at 8 decimal places and every other decimal column at its declared scale. All other reconciliations in the suite compare decimal and floating-point columns at 4 decimal places. comparison applies only in row_checksum mode. row_count mode reads no column values, and aggregate mode compares measures as numbers. If you set comparison on a row_count or aggregate reconciliation, or on a suite that has no row_checksum reconciliation, check-config and the run give a warning that names the field. The suite still loads. In the app, set it on the Comparison tab of a reconciliation, under Precision, or for the whole suite on the suite’s Settings tab. Add a column under Column settings to set its own precision. When the warehouse reports the column’s type, the form shows the precision the column gets by default, and warns when exact would fail the run.

Find problems before a run

check-config --db makes the same checks as a run for each reconciliation that has comparison settings. It shows a column that exact cannot compare:

Validating numeric comparison:
  - payments: FAILED (column "amount" cannot be compared exactly (numeric_scale: exact): the source holds 2 decimal places and the target 4; set numeric_scale to the places to compare at)

If a reconciliation’s comparison.columns names a column that the reconciliation does not compare, the run adds a warning to the audit log. An entry at the suite level applies only to the reconciliations that have that column, so it gives no warning.

See the precision of a run

The audit log records the precision of each decimal and floating-point column in normalization.numeric_columns. This includes the columns that needed no conversion, such as two decimals of the same type on the same kind of database:

jq '.reconciliations[] | {name: .reconciliation.name, numeric: .normalization.numeric_columns, warnings}' run.json

{
  "name": "payments",
  "numeric": [
    { "column": "amount", "decimal_places": 2 },
    { "column": "fx_rate", "significant_digits": 15 }
  ],
  "warnings": [
    "column fx_rate is a floating point number, compared to 15 significant digits"
  ]
}

Each column has one of decimal_places, significant_digits or exact. A column that needed no conversion and has no reported scale is recorded as exact, because each side keeps all of its digits. configured: true shows that a comparison setting gave the precision. Without it, the column types gave the precision.

Timestamp precision and time zones

A timestamp is converted to one text form on both sides too, 2026-01-08 14:05:00.123456, with a timestamp that has a time zone written in UTC. By default it is compared at the precision that both sides hold, the coarser of the two, and between two different kinds of database to the microsecond at most. A date is compared as the day. A date on one side against a timestamp on the other is compared by day too, 2026-01-08 on both sides, which is how an Oracle DATE (which holds a time) matches a DATE it was loaded into on another warehouse. The same rules apply to a date or timestamp key column. The drill-down splits on it like on any key, and a source that holds nanoseconds against a target that holds microseconds puts each row in the same segment on both sides. Two things cannot be read from the column types, so comparison has a setting for each:

Field	Type	Default	Description
`comparison.timestamp_precision`	`second`, `millisecond`, `microsecond` or `exact`	Not set: the precision both sides hold	How much of a second every timestamp column is compared to, key columns included.
`comparison.source_time_zone`	IANA time zone name, such as `Europe/Warsaw`	Not set: UTC	The time zone that the source side’s timestamps without a time zone hold their wall clock in.
`comparison.target_time_zone`	IANA time zone name	Not set: UTC	The same for the target side.
`comparison.columns[].timestamp_precision`, `source_time_zone`, `target_time_zone`	As above	Not set	The same settings for one column.

second, millisecond and microsecond cut both sides to that precision, so a value that differs only beyond it matches. Use it when a loader cut the values on one side. exact compares every digit that both sides hold: on one kind of database the column as the database writes it, and between two kinds of database only when both sides hold the same precision, at most microseconds. Otherwise the reconciliation fails with a message that names the column. A timestamp without a time zone is compared as its wall clock, read as UTC. When a loader moved local wall clocks to UTC, the two sides hold different wall clocks for the same row, and nothing in the types says so. Name the time zone of each side, and both are compared as the instant they are:

reconciliations:
  loads:
    source:
      connection: gateway
      table: dbo.loads
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.LOADS
    key_columns: [load_ts, load_id]
    mode: row_checksum
    comparison:
      source_time_zone: Europe/Warsaw   # the target is not set: UTC

A timestamp with a time zone already says which instant it is, so a time zone set for it gives a warning and changes nothing. The time zone is converted by the database: MySQL and MariaDB need their time zone tables loaded, SQL Server uses the Windows name of the zone, and Athena and Microsoft Fabric timestamps take neither setting. These settings are found in the same order as numeric_scale, and each side’s time zone on its own: a column’s source_time_zone does not hide the reconciliation’s target_time_zone. In the app, set them under Precision, next to numeric precision.

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. To count rows rather than the non-null values of a column, write column: "*" with COUNT. It is compared as COUNT(*) and reported as _measure_count_all. "*" takes no other function, and a suite that pairs it with one fails to load.

      measures:
        - column: "*"
          function: COUNT

Use a SUM and a COUNT on the same column. With SUM only, “one row is missing” and “one value is wrong” look the same: the total is too small in both cases. With the count, you can see which one it is. The count costs nothing more, because the same query calculates both.

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
        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, 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 shows as _measure_<function>_<column>. This is the alias in the generated SQL, and the name in -o json and in the audit log. Each divergent node has a label that tells why it is there:

Divergence type	Meaning
`MEASURE_MISMATCH`	This group’s own measures differ.
`SUBGROUP_MISMATCH`	The group differs because a group below it differs. Read further down.
`MISSING_IN_TARGET`	The group is on the source only.
`MISSING_IN_SOURCE`	The group is on the target only.

A group is matched across the two sides by its key, so each side reads a group column in one text form, whatever type it holds the column in and however its warehouse would print it: a date as 2026-01-08, a timestamp as 2026-01-08 14:05:00.000000 (in UTC when it carries a time zone), a decimal at one scale on both sides (52.50, never 52.5 against 52.50), a floating-point value at 15 significant digits (also against an integer or a decimal, so 5 matches 5.0), and a boolean as 1 or 0, so it lines up with a number on a warehouse that has no boolean type. A date on one side against a datetime on the other is compared by day, which is how a DATE loaded into an Oracle DATE finds its source. Types with nothing in common, such as text against a number, are compared as each warehouse prints them, so a value printed differently shows up as a group missing on each side: cast one side in its query to match the other. If you omit group_columns, the hierarchy falls back to [key_column]. A key is usually unique, so that makes one group per row: fine for a small table, but each level holds one side’s groups while it compares the other, and a level with more than 100,000 groups on either side fails the reconciliation instead. To compare row counts use row_count mode, with a tolerance if the two sides may differ a little, and to compare row by row use row_checksum.

Comparing the whole table

Leave out both group_columns and the key columns, and the aggregate compares the whole dataset as a single group: one query per side, with no GROUP BY. This is the way to say “the total SUM(amount) and COUNT(amount) must agree, within a tolerance”.

reconciliations:
  revenue-totals:
    title: Total revenue
    source:
      connection: postgres
      table: public.sales
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.SALES
    mode: aggregate
    aggregate:
      measures:
        - column: amount
          function: [SUM, COUNT]
      thresholds:
        absolute: 0.01

An aggregate over no rows still returns a row, so an empty side is compared (a COUNT of 0 against the other side’s) rather than reported missing. When the totals differ, the result has one MEASURE_MISMATCH with no group key. To find where the difference is, resume the run with synq-recon drill-deeper --add-group-column <column>: the whole table is then broken down by that column, as if it had been the first group column. A key column set on the reconciliation takes over as the group column, so remove it to compare the whole table.

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. Integer and decimal measures are compared exactly, so two large sums that differ only in their last digits still differ. A measure that is NULL on one side and a number on the other, such as a SUM over NULL amounts that the target loaded as 0, is a mismatch whatever the threshold. NULL on both sides agrees. You can override the threshold for a group column, and for a 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

The most specific threshold applies, in this order:

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 default for every measure of the reconciliation, at every level.

A measure is named by its rendered form, "FUNC(column)", for example "SUM(revenue)" or "COUNT(amount)". The quotes are necessary because of the parentheses.

A run whose only divergences are inside their thresholds, aggregate or row count, reports within_threshold rather than mismatched. This is a different status, and you can act on it 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 when both sides support it, for example BigQuery.
`xxhash64`	Fast, same-platform comparisons (Databricks).
`md5`	The universal fallback. Supported everywhere.

Set hash_algorithm: (auto, md5, farm_fingerprint, xxhash64) only when you must fix it, for example to keep the checksums the same during 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.

​Row count mode

​A tolerance on the count

​Row checksum mode

​Numeric precision

​Default precision

​Set the precision with comparison

​Which setting applies

​Find problems before a run

​See the precision of a run

​Timestamp precision and time zones

​Aggregate mode

​Measures

​The group hierarchy

​Comparing the whole table

​Thresholds

​Hash algorithms

​Next

How comparison works

Time windows and cutoffs

Investigating results

Configuration reference

Row count mode

A tolerance on the count

Row checksum mode

Numeric precision

Default precision

Set the precision with `comparison`

Which setting applies

Find problems before a run

See the precision of a run

Timestamp precision and time zones

Aggregate mode

Measures

The group hierarchy

Comparing the whole table

Thresholds

Hash algorithms

Next