--- name: reconciling-to-source description: Use this skill whenever the analytical metric has an authoritative version that exists outside the query — general ledger totals, audited reports, regulatory filings, vendor reports, previously-trusted dashboards, or any source-of-truth at any grain. Required for high-stakes analyses; recommended for medium-stakes whenever an authoritative version is plausibly available. The new query must tie out to the authoritative source at some grain (day, month, by-segment) before the result is trusted. Trigger on metrics involving revenue, headcount, billed amounts, refunds, regulatory totals, or any number that has been counted by something other than your query. --- # Reconciling to Source A new query is guilty until it ties out. If something else has counted this number — accounting, regulatory, an audited report, last quarter's trusted dashboard — your query must agree with it at some grain before you trust your query. ## What this skill catches - **Quiet definitional drift.** Two queries that look like they're computing the same thing but differ in how they treat refunds, voids, chargebacks, cancellations, or partials — and neither owner realized. - **Join multiplication.** A new query produces a number suspiciously close to authoritative, but high by a small percentage that gets waved off — when in fact a fan-out join is double-counting a small slice. - **Period boundary mismatches.** Authoritative source uses settlement date; the new query uses transaction date. Numbers differ by one day's tail and the difference looks like noise. - **Currency/unit drift.** Authoritative source is in cents; query returns dollars and a 100x divergence is silently corrected by an ad-hoc fix that survives into other analyses. - **The "close enough" trap.** A 0.3% delta is treated as immaterial and never investigated; the underlying bug surfaces three months later in a stakes-laden question. ## What this skill enforces 1. **Identify the authoritative source.** What is the number that finance / leadership / regulators / auditors would point to as canonical? If multiple sources exist, pick the most authoritative for the analysis at hand. 2. **Choose a reconciliation grain.** Daily totals? Monthly by segment? Whatever grain lets you compare your query's output to the source. Coarser grain is easier; finer grain catches more. 3. **Run the reconciliation query.** Compute your query at the reconciliation grain and pull the source numbers. 4. **Compare.** Acceptable variance depends on the metric and stakes — but variance must be **explained**, not waved off. "Off by 0.3%" is not a pass; it's an open investigation. 5. **Document the reconciliation** in the methodology DB. Future analyses against this metric reference past reconciliations. ## When reconciliation fails If the new query disagrees with the source by more than the explained variance band, exactly three things can be true: - The new query is wrong, OR - The source is wrong (rare but real — sources have bugs too), OR - The two are measuring slightly different things (most common — definitions drift) Investigate in that order. Document which it was. Do not "split the difference" or pick the convenient one. If the source turns out to be the wrong one, that finding goes into the writeup explicitly — don't bury it. ## Logging Log this skill's activation: ```bash python3 .analytics/db.py log-skill \ --skill reconciling-to-source \ --question-slug \ --completed ``` Log the reconciliation result itself, whether it passed or failed: ```bash python3 .analytics/db.py log-reconciliation \ --question-slug \ --metric "" \ --grain "" \ --new-value \ --source-value \ --variance-explained "" \ --passed # add this flag only if reconciliation passed ``` The variance is computed automatically. Future analyses against the same metric can read past reconciliations to see how the metric has tied out historically. ## Boundary cases **"There's no authoritative source for this metric."** Then this skill doesn't apply — note that explicitly in the question doc, hand off, and continue. Skipping reconciliation when no source exists is fine; skipping reconciliation when one does exist is the failure this skill prevents. **"The source is itself a query someone else wrote."** That's still a source for our purposes — but if that other query has never been reconciled to anything, this skill's logic recurses. The first query that *was* reconciled (to a ledger, a regulatory filing, an audit) is the actual root. Document the chain. **"The variance is small and the deadline is tight."** Small unexplained variance is exactly what this skill exists to catch. The 0.3% delta has eaten more reputations than the 30% delta — the small one looks like a tolerance issue, but is often a join bug or a definitional drift that compounds elsewhere. Investigate, then deadline. **"The source and my query disagree, and I think the source is wrong."** Possible, but document it carefully. Get a second pair of eyes. If you turn out to be right, the writeup names the source bug as a finding — not as a footnote. ## Handoff After reconciliation passes (or its failure is fully explained and logged), hand off to `confound-audit/SKILL.md`.