> ## 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. # 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 ```yaml theme={null} 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 ```yaml theme={null} 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. ```yaml theme={null} 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__` — 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. ```yaml theme={null} 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: ```yaml theme={null} 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: The threshold for this measure at this level of the hierarchy. The threshold for this measure, at every level. The threshold for every measure at this level. 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`](/reconciliation/cicd-and-automation#choosing-what-fails-the-build). ## 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 The checksum formula, bisection, segmentation strategies and privacy levels. Aggregate mode on a lagging target needs a cutoff. Here is why. Reading a drill tree, and following a finding to the rows. Every field of `aggregate:` and `thresholds:`, generated.