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 revenue 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 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's Reports API defaults to UTC unless you pass a timezone; your dashboard probably 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, and every month-end is now off by a few hours 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 balance reports record the refund when it happens; your models may push it back onto March. The delta shows up in two months at once.
• Currency conversion timing. Stripe records the rate it used on each balance transaction's exchange_rate. A model that converts at month-end rates, or at today's rate, yields a different total 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. Stripe retries failed deliveries for up to three days in live mode, so an endpoint that returns errors usually catches up on its own. The dangerous gaps are quieter: a handler that returns 200 before it has durably saved the event, an outage longer than the retry window, or an endpoint that was disabled. 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. The financial reports you can download from the Stripe Dashboard are available through the Reports API, so this can be a scheduled job. Compare like with like: those reports describe money movement (charges, refunds, fees), so reconcile the cash measure your revenue and MRR models are built from rather than MRR itself. Stripe prepares report data on a semi-daily basis, so compare only through the latest available data and let the next run pick up the rest.

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

A typical catch looks like this. The alert fires on a Tuesday with the current month 1.8% under source. Per-day totals show three specific days short, and the charge-id diff shows a missing window that lines up exactly with last weekend's deploy, which shipped a handler that acknowledged events before writing them. Stripe saw successful deliveries, so it never retried. The Events API keeps 30 days of events, so you list the missing window, replay it through the handler, backfill idempotently and are green by evening. Total cost: about an engineer-hour. The identical gap, unnoticed, is the four-percent phone moment two quarters later, long after those events have aged out of the API. 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 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 becomes necessary 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.

Sources

• Receive Stripe events in your webhook endpoint, Stripe documentation
• List all events, Stripe API reference
• How the Reports API works, Stripe documentation
• The Balance Transaction object, Stripe API reference