Reconcile Against Something Real

The check you cannot do to yourself

Everything in Module 3 was internal consistency: the join holds its grain, the load reruns cleanly, the transform matches the definition. All necessary. None of it can tell you the number is right, because every one of those checks is downstream of the same assumptions.

Reconciliation is the outside check. You compare your output to something built by different people, from a different path, that would disagree with you for real reasons if you were wrong.

If you took Cloud Modernization, this is that training’s rule — reconcile, then cut over — arriving from the other side. There, an independent count check gated a one-time migration. Here it gates every publication, and the target is not a copy of the source but an operational system that has its own opinion.

Prompt first: diagnose, do not adjust

My monthly table reports 3,847 fault events for July. The outage
management system reports 3,731. That is +3.1%, and my tolerance
is 2%.

Do not adjust my query. Generate the diagnostic queries that would
confirm or eliminate each of these, in order: population differences
(decommissioned circuits, transmission vs distribution), time-zone
boundary effects, source-side suppression rules, grain/fan-out on my
side, and report timing.

For each, tell me what result would confirm it as the cause and
roughly how much of the 116-event gap it could explain.

“Do not adjust my query” is doing real work. Asked about a variance, an agent will offer to fix it, and the fix will look reasonable. You want the diagnosis, and you want the magnitude estimate so you can tell whether one cause explains the whole gap or you are still looking at two problems.

Stop and escalate when a variance will not resolve to a named cause: publication stays blocked, and the question goes to the metric owner and the source-system owner together, with both counts and your eliminated causes attached. An unexplained gap that publishes anyway is a decision someone else should have gotten to make.

Choosing a target that can disagree

Lesson 1.4 established the requirement; this is how to satisfy it in practice, in descending order of strength:

1. The operating system of record. Meridian’s outage management system knows how many faults it dispatched crews to. It is not a copy of your source — it is the source, queried through its own application logic. When it disagrees, one of you is wrong and the difference is informative.
2. An externally published figure. Last year’s regulatory reliability filing. Slow, narrow, and unarguable — somebody already staked something on that number.
3. A human who knows one case well. The supervisor who remembers the Elm Street failures. Covers one circuit, catches errors of kind rather than errors of degree, and costs ninety seconds.
4. A parallel calculation you did not write. Weakest of the four, and only genuinely independent if it reads a different source. A colleague’s query against the same lake shares your failure modes.

Tolerance, set before you look

Write the tolerance into the acceptance test, not after seeing the variance. The order matters: a tolerance chosen after the fact is a rationalization with a number attached.

RECONCILIATION - circuit_degradation_score, monthly

TARGET      outage management system, fault event count, same period,
            queried through its own reporting module
TOLERANCE   total within 2%; no single circuit differing by more
            than 5 events
EXPECTED    a small positive variance is normal: OMS suppresses
            momentary faults under 30 seconds, our source feed does
            not. Historically 1.1-1.6%.
BREACH      publication blocks. Investigate to a named cause before
            release; do not adjust the calculation to close the gap.

The EXPECTED line is the one that separates a real reconciliation from a ritual. A known, explained, quantified difference means you understand both systems. A variance with no explanation means you understand neither, even when it is small.

Investigating a variance

When it breaches, the discipline is to find the cause, not to close the gap. Ordered by how often it turns out to be the answer:

1. Population. Different inclusion rules. Yours includes decommissioned circuits, theirs does not. The most common cause by a wide margin.
2. Time boundary. Your month is America/Chicago, theirs runs on UTC. Five hours of events sit in different buckets.
3. Business rules in the source. The suppression rule you found in Lesson 2.3 — invisible to lineage, decisive to the count.
4. Grain. Your fan-out, or their deduplication.
5. Timing. They ran their report before a late batch landed.
6. One of you is actually wrong.

Work down the list. Each has a query that confirms or eliminates it in a couple of minutes.

Key takeaway

Internal consistency cannot tell you a number is right, because every internal check shares the same assumptions. Reconcile against something that could genuinely disagree — the operating system of record, a published figure, or a person who knows one case — and never against a copy of your own source. Set the tolerance before you look, and record the variance you expect along with why. When it breaches, work down the causes to a named one; if you cannot justify a filter independently of its effect on the gap, you have fitted the answer rather than found it. Lesson 4.2 makes these checks run on every load instead of once.