The reconciliation query that ends board-meeting surprises

There is a specific bad moment we’ve watched happen more than once: a board member pulls up Stripe on their phone because the MRR slide looks off, and the number on the slide disagrees with Stripe by four percent. Nothing said in the rest of that meeting recovers. Whether the pipeline was right, the deck was right, or neither — the damage is identical, because the disagreement itself is the problem.

The defense costs almost nothing. Of every data test you can write, one has a claim to the highest return: does the pipeline’s monthly revenue agree with the billing system’s own reporting, to within rounding? One comparison query per month, run daily, alert past a threshold. That’s the whole design. What’s interesting is why it fires — and what happens to the rest of your metrics once it stops firing.

Why two honest systems disagree about revenue

Neither system needs a bug for the numbers to drift. The usual causes:

• Timezone boundaries. Stripe reports in UTC; your dashboard groups by local time. A charge at 11 p.m. Pacific on July 31 belongs to July in one system and August in the other. Both are right; every month-end is now wrong by a day of edge traffic.
• Refunds and disputes across periods. A March charge refunded in May: does March revenue change, or does May absorb a negative? Stripe’s reports make one choice, your models make another, and the delta shows up in two months at once.
• Currency conversion timing. Converting at charge time, settlement time, or month-end rates yields three different totals from identical charges once you sell in more than one currency.
• Test-mode and deleted data. A test-mode customer that leaked into the warehouse, or objects deleted upstream that your pipeline still holds because nothing propagates deletions.
• Webhook gaps. The big one. Ingestion missed events during a deploy, an outage, or a signing-secret rotation. Everything downstream is silently missing those charges, and nothing errors — the data is simply not there.

The mechanics: one query, run daily

The implementation is deliberately small. For each recent month, compute revenue from the pipeline’s models and pull the equivalent total from the billing system’s own reporting — Stripe’s reporting API returns the same numbers its dashboard shows. Compare. Alert when any month diverges beyond a threshold; we default to 0.5%, tight enough to catch a real gap, loose enough to ignore rounding and conversion noise. Run it every morning, covering at least the trailing three months, because refunds and disputes rewrite the past.

When it fires, the drill-down is mechanical rather than heroic: break the diverging month into per-day totals to find which days disagree, then diff charge ids for those days between the warehouse and the source. The answer is one of the causes above, and the per-charge diff usually names it within the hour — a contiguous run of missing ids says webhook gap; a small systematic skew across every day says timezone or currency.

The payoff, and the trust cascade

The pattern we keep seeing: the alert fires on a Tuesday with the current month 1.8% under source. Per-day totals show three specific days short; the charge-id diff shows a missing window that lines up exactly with last weekend’s deploy, when a webhook endpoint returned 500s for a few hours and the events aged out of retry. Re-request the events, backfill idempotently, green by evening. Total cost: about an engineer-hour. The identical gap, unnoticed, is the four-percent phone moment two quarters later — same root cause, catastrophically different price, and the only difference is who found it first.

The second-order effect is bigger than the catches. Revenue is the one number in your warehouse with an external referee, and everyone knows it. When it reconciles daily, every metric downstream inherits credibility — LTV, payback, and net revenue retention are all built on charges that provably match the source. Until it reconciles, the doubt runs the other way: a board member who catches revenue wrong once will discount every dashboard you show them afterward, including the correct ones. Trust in a data stack isn’t distributed evenly; it cascades outward from the audited number, and revenue is the only number that can be audited from the outside.

When you don’t need this

If revenue at your company means “what the Stripe dashboard says” — the deck screenshots it, finance exports it, and no pipeline, spreadsheet, or BI tool computes its own version — there is nothing to reconcile, and building this test would be ceremony. One system, one number, no drift. The test exists the moment a second system produces the number: the first dbt model that sums charges, the analyst’s revenue sheet, the ops dashboard with its own Stripe sync. From that day forward there are two numbers, they will drift for the reasons above, and the only question is whether a query notices first or a board member does.

Reconciliation checks like this one ship as standard in every pipeline we build, and watching them each morning — and owning the fix when one fires — is what our data retainers are for.