Day 41. Reconciliation, the skill that earns trust

⏱ 4h · learn 70m · lab 135m · ship 20m · review 15m🏷 retail, hospitality, marketingBefore: Day 40

By the end of today you can

Rebuild a reported number from raw data, step by step
Bridge two different totals with a variance waterfall
Run a "the numbers don't match" investigation with evidence
Set up certification and sign-off for important reports

1. Why this matters

Sooner or later someone says "your number is wrong". A junior defends the dashboard. A senior says "let's bridge it", and twenty minutes later shows exactly which differences are definitions, timing, scope and genuine errors. That skill is what makes managers trust a BI person, and it's a great interview story.

2. Why two totals differ

Almost every difference is one of these, in roughly this order of frequency:

Definition: gross vs net, with or without tax, tips, delivery fees, refunds, comps.
Timing: business date vs posting date vs calendar date; cut-off times; late files; provisional data.
Scope: which stores/hotels/campaigns; closed or new locations; test transactions; currency.
Data errors: duplicates, missing files, bad joins (fan-out), quarantined rows.
Rounding: per line vs per receipt vs per day.

3. The bridge

Start from number A, apply each explained difference as a step, arrive at number B. What's left unexplained is the real problem.

Step	Amount	Evidence
Store system: September net sales, store R104	$412,880	Manager's report
− Sales tax included in store figure	−$47,500	Store report is tax-inclusive (contract note)
− 30 Sept 23:00–01:00 sales booked to 1 Oct by business-day rule	−$1,240	`fct` query by order_ts
+ Refunds netted by store, gross in BI	+$2,150	refund rows in quarantine/refund table
− Duplicate email on 14 Sept removed by BI	−$0	duplicate correctly ignored (manifest)
Explained BI figure	$366,290
BI dashboard	$366,140
Unexplained	$150	investigate: 2 receipts in quarantine (bad timestamps)

Visualise it as a waterfall chart; finance people read waterfalls natively.

Maths and principle

Principle: a bridge decomposes a difference into additive, independently evidenced components; the residual is the test.

Equation: $B - A = \sum_k \Delta_k + \varepsilon$. Each $\Delta_k$ must be computed from data, not inferred from the gap. You're done when $|\varepsilon|$ is below materiality (say 0.1% of $A$) or traced to named rows.

Analogy: energy accounting in an experiment: known losses (friction, heat) are measured separately, and the residual tells you whether there's an unknown effect. Where it breaks: if you "explain" a step by plugging the gap, $\varepsilon$ is zero by construction and proves nothing.

Try it: A = $100,000, steps −$12,000, +$800, −$300; B = $88,400. Residual? (100,000 − 12,000 + 800 − 300 = 88,500; residual −$100.)

4. The investigation routine

Restate the question precisely: which number, which report, which filters, which dates, which version.
Reproduce both numbers yourself from their sources. Save queries and results (evidence/). This uses the read-only SQL and evidence habits from your bi-skills-kit.
Bridge from one to the other, biggest suspected step first.
Classify the residual: definition, timing, scope, data error.
Report in writing: the bridge table, what's a definition choice (needs a business decision), what's a bug (needs a fix with a test), and the owner of each.
Prevent: add a reconciliation check (control totals, source vs warehouse daily) so this class of difference is caught automatically next time.

The 5-year lens

Never say "the data is right" before the bridge is done, and never say "the data is wrong" before it's done either. Use "not yet checked" for steps you haven't evidenced. Keep a reconciliation log (date, question, outcome, cause, fix) in the repo; after six months it's a map of where your platform is fragile, and it's gold for interviews: "I kept a reconciliation log; 60% of issues were definitions, which is why I wrote the metric dictionary."

5. Certification and sign-off

For executive and finance reports: a named owner, a certified semantic model (Power BI endorsement: Certified), a daily reconciliation check to a control total (POS daily Z-report totals, PMS night-audit totals, ad platform billing), and a change process (a new definition needs the owner's sign-off, recorded in the dictionary's changelog).

Lab

Create a "store-reported" September figure for R104: a CSV that includes tax (13% HST), nets refunds, uses calendar dates and double-counts one resent email (ask Claude Code to generate it from the raw data with these rules, then hide the rules from yourself by not reading the script until you finish).
Reproduce both numbers with saved queries. Build the bridge table; reach a residual under $50 or trace it to rows.
Draw the waterfall (Power BI waterfall visual, or Matplotlib).
Write docs/reconciliation/2026-09-R104.md with the six-step routine.
Add an automated daily check: QSR source control totals (sum of each POS file) vs fct_restaurant_daily. Make it a Dagster asset check.

Ship

docs+feat(recon): R104 bridge, waterfall, and daily control-total check.