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.
`mismatched`	A real difference.
`failed`	This reconciliation could not be evaluated.

A succeeded run reporting mismatched is the normal, healthy case — the tool did its job and found something. Filter on the result status, not the run status, when you want “did anything differ?”.

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 renders the same tree graphically, plus a drift map — the key space with the divergent regions highlighted, which makes “the whole tail is wrong” versus “three rows scattered” obvious at a glance.

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 disagree — 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`.
`NOT_SPLITTABLE`	The segment cannot be divided further — typically identical key values.	This is as narrow as it gets. Look at the rows.

The aggregate drill tree

In aggregate mode the equivalent output is a hierarchy of groups, each labelled with why it diverged — MEASURE_MISMATCH for its own numbers, SUBGROUP_MISMATCH for something 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 reconciliation’s own dataset, wrapped in the leaf’s key bounds:

-- Source: primary (postgres)
-- Segment: depth=2, 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 against the two databases and you are looking at the rows themselves — 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, rather than on a suite. Both take either a local audit-log file or, for a run stored in the workspace, its invocation id.

# 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)

Twenty-two queries, and the answer is exact: order 384 is absent from the target, and order 617 exists on both sides with a different value. Note that it re-entered at depth 0 with 20 rows, not 1,000 — only what the previous run left open is in scope.

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. Pass --connections, exactly as for a normal run — no connections available to replay run ... is what a missing one looks like.

--remote hands the replay to our backend, against workspace integrations instead of local credentials. It needs a stored run’s invocation id — a local file has nothing server-side to reference — and 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