Investigating results - Coalesce Quality Documentation

A mismatch is the start of the work, not the end of it. This page is the path from “these two disagree” to “here is the row, and here is why”.

Two statuses, and they mean different things

Run status	Meaning
`scheduled`, `in_progress`	Not finished.
`succeeded`	The run executed cleanly. This says nothing about whether the data agreed.
`failed`	The run itself could not complete: a connection, a timeout, a bad query.
`cancelled`	Cancelled, by a person or by `runs cancel`.

Result status	Meaning
`passed`	The two sides agree.
`within_threshold`	They differ, but inside the configured aggregate thresholds or row count tolerance.
`mismatched`	A real difference.
`failed`	This reconciliation could not be evaluated.

A succeeded run that reports mismatched is the normal case: the tool did its work and found a difference. Filter on the result status, not the run status, when you want “did anything differ?”.

A passed result on decimal or floating-point columns is a match at the precision that the run used for them. The audit log records that precision for each column. See numeric precision.

Reading a finished run

synq-recon audit-logs list --status mismatched --limit 10
synq-recon audit-logs get <invocation-id>
synq-recon audit-logs get <invocation-id> -o json > run.json

The segment tree

In row_count and row_checksum modes, a drill-down produces a tree of key ranges. Each node carries both sides’ count and checksum, and the leaves are where drilling stopped:

Depth 0: Segment [*, *)
  Source: 1,000 rows, checksum: -2052783606565609
  Target: 999 rows, checksum: -2210053578859533
  Status: MISMATCH -> drilling down (20/1,000 rows in mismatched segments)
  Depth 1: Segment [301, 401)
    Source: 100 rows, checksum: -167029730887454
    Target: 99 rows, checksum: -295596598382435
    Status: MISMATCH -> drilling down (10/100 rows in mismatched segments)
    Depth 2: Segment [381, 391)
      Source: 10 rows, checksum: 330886401684630
      Target: 9 rows, checksum: 202319534189649
      Status: MISMATCH (leaf: threshold_reached)

20/1,000 rows in mismatched segments is the useful number at each level: it is how much of the dataset is still in play. If it stays close to the total as you descend, the data is not mostly-identical and you are in the expensive case. The app shows the same tree as a graphic, and also a drift map: the key space with the divergent ranges highlighted. With it, you can quickly see if “the whole tail is wrong” or if “three rows in different places are wrong”.

What a mismatch leaf tells you

Read the types, not the counts. Every type that applies is reported, so a segment that differs in both size and content reports two:

`mismatch_types`	Meaning
`MISSING_IN_TARGET`	Rows on the source, none on the target: a real gap.
`MISSING_IN_SOURCE`	Rows on the target, none on the source: usually an orphan.
`COUNT_MISMATCH`	Both sides have rows, but not the same number.
`DATA_MISMATCH`	Both sides have the same rows and the checksums do not agree: a value changed.

Count direction alone does not classify a difference. A target with one row more than the source can still be missing a live row and carrying two orphans. The counts net out; the types do not.

Each leaf also carries a drill_stop_reason, which tells you whether the finding is as narrow as it can get:

Reason	Meaning	What to do
`THRESHOLD_REACHED`	The segment fell below `bisection.threshold`.	Drill deeper with a lower `--threshold` if you want a tighter range.
`MAX_DEPTH_REACHED`	The depth limit stopped it.	Drill deeper with `--depth`.
`CANNOT_SPLIT`	The segment cannot be split, usually because its key values are identical.	The range cannot be narrower. Look at the rows.
`ERROR`	A query of the segment failed.	Read the error of the reconciliation, then Drill deeper.

The aggregate drill tree

In aggregate mode, the output is a hierarchy of groups. Each group has a label that tells why it differs: MEASURE_MISMATCH for its own numbers, SUBGROUP_MISMATCH for a group below it. Comparison modes has a worked example.

Follow a finding to the rows

Every mismatch leaf comes with investigation queries: ready-to-run SQL for both sides, scoped to that leaf’s key range.

synq-recon run-drill suite.yaml --include orders -o json 2>/dev/null \
  | jq -r '.investigation_queries[] | .source_query, .target_query, "--"'

Or from a saved audit log:

jq -r '.reconciliations[].stages[].bisection_result?.mismatch_leaves[]?.diff_queries
       | select(.) | .source_rows, .target_rows, "--"' run.json

They look like this: the dataset of the reconciliation, inside the key range of the leaf:

-- Source: primary (postgres)
-- Segment: depth=0, key range [381, 391)
select *
from (
  SELECT * FROM app_orders
) AS sub
where order_id >= 381 AND order_id < 391
order by order_id

Run them on the two databases to see the rows: ten of them, not a million.

This is why raising the privacy level is rarely necessary. count_only already tells you exactly which rows to look at; running the query yourself keeps the values in your SQL client rather than in a run record.

Re-check and Drill deeper

Two operations on a finished run, not on a suite. Both take a local audit-log file, or the invocation id of a run that is stored in the workspace.

# Did the difference go away? (after a backfill, a fix, a redeploy)
synq-recon recheck run.json --connections .connections.yaml

# Narrow every still-open segment further
synq-recon drill-deeper run.json --connections .connections.yaml --threshold 2

# Break each divergent aggregate group down by another column
synq-recon drill-deeper run.json --add-group-column region

# Hand the replay to the backend instead, against workspace integrations
synq-recon recheck <invocation-id> --remote

A re-check answers the only question that matters after a remediation, and answers it in one line:

Re-validation vs Previous Run
=============================
Previous run: bdb89bea-ab7f-434b-8259-e6dad97531b2
This run:     ba170eb5-392c-4a6f-98a1-0a9b6603c5e7

RECONCILIATION  BEFORE            AFTER             CHANGE
--------------  ----------------  ----------------  ------------------
orders          mismatched        mismatched        still mismatched (row gap unchanged at -1)

And a drill deeper picks up where the previous run stopped rather than starting over. Continuing the run from Getting started at --threshold 2:

Depth 0: Segment [*, *)
  Source: 20 rows, checksum: -50271825170593
  Target: 19 rows, checksum: -207541797464517
  Status: MISMATCH -> drilling down (2/20 rows in mismatched segments)
  Depth 2: Segment [381, 391)
    Status: MISMATCH -> drilling down (1/10 rows in mismatched segments)
    Depth 3: Segment [384, 385)
      Source: 1 rows, checksum: 128566867494981
      Target: 0 rows, checksum: 0
      Status: MISMATCH (leaf: threshold_reached)
  Depth 2: Segment [611, 621)
    Status: MISMATCH -> drilling down (1/10 rows in mismatched segments)
    Depth 3: Segment [617, 618)
      Source: 1 rows, checksum: -51531599134958
      Target: 1 rows, checksum: -80234703933901
      Status: MISMATCH (leaf: threshold_reached)

The drill-down compared 22 segments with 10 queries, and the answer is exact: order 384 is absent from the target, and order 617 exists on both sides with a different value. It started again at depth 0 with 20 rows, not 1,000, because only the segments that the previous run left open are included.

Four things that govern a replay

Queries are replayed, not re-derived

The audit log holds each query after variable interpolation and cutoff resolution, so a re-check compares the same window as the original run. That is precisely what makes “did the gap move?” a meaningful question rather than two unrelated measurements.The consequence: a replay does not pick up an edited query, a changed variable, or a new cutoff until you pass --reresolve. And --reresolve is required for an as_of reconciliation, which otherwise re-compares the same frozen snapshot forever and can never turn green.

Only what was left open resumes

drill-deeper picks up the segments the previous run stopped at on its threshold or depth limit. --depth counts from the depth they already reached, so it means “this many more levels”.Segments that could not be split further, and aggregate groups present on only one side, are reported and skipped rather than silently dropped. An aggregate drill already visits every configured group column, so resuming one needs at least one --add-group-column.

recheck re-runs only what did not pass

By default. --all re-runs every reconciliation in the run.

Credentials never come from an audit log

It records connection names only, never credentials. Give --connections, as for a normal run. Without it, the command fails with no connections available to replay run ....

--remote sends the replay to the backend, which uses workspace integrations and not local credentials. It needs the invocation id of a stored run, because a local file has nothing to refer to on the server. It accepts --include but not --exclude.

Reading an audit log directly

The audit log is the machine-readable form of everything above, and its shape is published:

Audit log reference

Every field, rendered.

audit-log.schema.json

Validate a log against the schema you were sent.

Running locally has the jq starting points, and the two traps worth internalising: snake_case in a local file versus camelCase from the API, and counts encoded as strings.

Time windows and cutoffs

Before you chase a difference, rule out the comparison window.

CI/CD and automation

Turning a reconciliation into a gate that blocks a bad release.

​Two statuses, and they mean different things

​Reading a finished run

​The segment tree

​What a mismatch leaf tells you

​The aggregate drill tree

​Follow a finding to the rows

​Re-check and Drill deeper

​Four things that govern a replay

​Reading an audit log directly

Audit log reference

audit-log.schema.json

​Next

Time windows and cutoffs

CI/CD and automation

Two statuses, and they mean different things

Reading a finished run

The segment tree

What a mismatch leaf tells you

The aggregate drill tree

Follow a finding to the rows

Re-check and Drill deeper

Four things that govern a replay

Reading an audit log directly

Next