SQL reconciliation: prove the cohort survived each transformation

Last updated: 5 Oct 20265 min read

tutorial

A query that returns a plausible number still needs row-count, key and unmatched-record checks at each stage.

Write conservation checks

Start with the raw intake IDs and a declared eligibility rule. Count raw events, distinct case IDs, eligible case IDs, matched outcomes, unmatched outcomes and final metric rows. An eligibility filter may intentionally remove records, but every removal needs a reason and a count. A one-to-one outcome join should preserve eligible case count even when the outcome is missing. Cardinality checks make this an executable contract.

Use anti-joins to find gaps

A LEFT JOIN from eligible cases to selected outcomes followed by WHERE outcome.case_id IS NULL identifies cases without an outcome. Reverse the direction to find orphaned outcomes with no eligible case. Report both. An unmatched case is not automatically bad data: it may be open or censored. The ledger should classify it before any rate denominator changes. Open-case semantics matter here.

Inspect the plan, not just the SQL text

Run the database EXPLAIN facility on the exact production-shaped query. Check whether filters prune partitions and whether key joins use indexes or scan large tables repeatedly. A query that runs quickly on 47 rows may become expensive at 47 million. Query plans vary by engine and statistics, so record the plan with a representative input scale. Avoid adding an index solely from intuition when the warehouse is columnar or the workload is dominated by partition scans.

Protect metric revisions

After a backfill, compare old and new snapshots by case ID: added, removed and changed outcome rows. Recompute the rate from both snapshots and attribute the difference to those categories. If 13 late resolutions arrive, a previously published two-day rate may restate; the system should show the revision version and reason. Atomic publication prevents readers from seeing a half-updated table.

Set an explicit stop rule

Fail the metric build if eligible IDs are duplicated, outcome IDs are unexpectedly orphaned, or the final joined row count differs from the eligible count under a one-to-one contract. Separate expected open cases from unexpected missing cases, and keep the failed batch available for investigation. A zero-row query without an error is not proof of a zero rate.

Implementation

WITH eligible_cases(case_id) AS (
    VALUES (501), (502), (503), (504)
), selected_outcomes(case_id, solved) AS (
    VALUES (501, 1), (502, 0), (505, 1)
), joined AS (
    SELECT intake.case_id, outcome.solved
    FROM eligible_cases AS intake
    LEFT JOIN selected_outcomes AS outcome ON outcome.case_id = intake.case_id
)
SELECT COUNT(*) AS eligible_after_join,
       SUM(CASE WHEN solved IS NULL THEN 1 ELSE 0 END) AS unmatched_eligible,
       COUNT(solved) AS observed_outcomes
FROM joined;

Performance and operating cost

A hash join of N eligible cases and M outcomes costs O(N + M) expected time and space; indexed nested-loop joins have a different cost profile. Reconciliation counts can run alongside the final query, but retaining per-ID differences for a backfill takes O(N + M) storage.

Common Mistakes

Do not accept a plausible rate without checking row conservation.
Do not classify all unmatched cases as data corruption.
Do not infer production cost from a tiny fixture or from SQL syntax alone.