Investigating results - Coalesce Quality Documentation

A mismatch is where the investigation starts. This page takes you from “these two disagree” to “here is the row, and here is why”.

Run status and result status

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. A segment gets every type that applies, so a segment that differs in both size and content gets 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.

The direction of the count does not tell you the type of difference. A target with one row more than the source can still be missing a row and hold two orphans. The counts cancel 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 finished run, as a local audit-log file or the invocation id of a stored run:

synq-recon audit-logs queries run.json
synq-recon audit-logs queries <invocation-id> --include orders

--all-leaves also prints the query of each segment that a merged query covers. --executed prints the statements that the run itself sent, in stage order.

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. You get ten rows, not a million.

You rarely need a higher privacy level: count_only already tells you which rows to look at. When you run the query yourself, the values stay in your SQL client and do not go into a run record.

Re-check and Drill deeper

These two operations work on a finished run. Each takes 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

# Re-check, then drill into what still mismatches
synq-recon recheck run.json --drill

# 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 tells you, in one line for each reconciliation, if a fix removed the difference:

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)

A drill deeper continues from where the previous run stopped. It does not start again. 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 at depth 0 with 20 rows, not 1,000, because it includes only the segments that the previous run left open.

Four things that govern a replay

Queries are replayed, not re-derived

The audit log holds each query after variable interpolation and cutoff resolution. Thus a re-check compares the same window as the original run, and you can see if the gap moved.A replay does not use an edited query, a changed variable or a new cutoff. Pass --reresolve to derive the queries again against current data. An as_of reconciliation needs --reresolve, because without it the replay compares the same snapshot and can never pass.

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 cannot be split further, and aggregate groups that are on only one side, are reported and skipped. An aggregate drill already uses every configured group column, so to resume one, give at least one --add-group-column.

recheck re-runs only what did not pass

--all re-runs every reconciliation in the run. --drill drills into each reconciliation that still mismatches.

Credentials never come from an audit log

An audit log records connection names only, never credentials. The replay finds the connections as a normal run does: from --connections, or from a .connections.yaml or connections.yaml in the working directory or a parent directory. If it finds no connections, 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. --map connection=integration_id binds a connection name that the run recorded to an integration. --remote 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 jq examples. It also shows two differences to know about: a local file uses snake_case and the API uses camelCase, and counts are 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.

​Run status and result status

​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

Run status and result status

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