The churn number in the board deck said 3.8%. Finance pulled 4.2% from the same warehouse two days later. Both queries ran clean, no errors, no failed jobs, and nobody in the room could say which one was right, or why they didn't match. I've sat in that meeting more than once, and the uncomfortable part is always the same: the metric was never tested, it was just trusted.
Most companies unit test their application code without thinking twice about it. A function that calculates a shipping discount gets a test suite with edge cases: zero quantity, a negative quantity, a coupon that doesn't exist. The metric that calculates monthly recurring revenue, the one the CFO reads out loud on the earnings call, usually gets none of that. It lives in a SQL query, a BI tool's calculated field, or a notebook cell, and it gets tested the way most untested code gets tested: by running it in production and seeing if anyone complains.
I've built enough of these pipelines to know the fix isn't a nicer dashboard or another entry in the data catalog. It's treating the metric calculation like what it actually is: a function with defined inputs and a defined expected output, which means it deserves the same discipline you'd never ship any other function without. I use a version of red-green-clean, borrowed from the classic test-driven development cycle and adapted for metrics instead of application logic. It's simple enough to hold in your head every time you touch a calculation that someone downstream is going to make a decision on.
Red: before you trust the calculation, write down the exact expected number for a specific, fabricated scenario, then assert it against mocked rows built to represent that scenario. Not a sample of production data, mocked data, because you need to know the ground truth going in, not go looking for it after the fact. Say you're testing MRR. Build four fake customers: one who signed up mid-month at $100 and should contribute a prorated amount, one who upgraded from $50 to $150 on the 15th, one who canceled on the 20th and should drop to zero the following month, and one who's on a free trial and shouldn't count yet at all. Write the assertion for what MRR should equal against exactly those four rows, then run it. It should fail, either because the calculation doesn't exist yet or because you've never actually verified it handles a mid-month upgrade correctly. That failure is the point. A test that passes on the first run without ever failing hasn't told you anything.
Green: Now make the calculation to produce that exact number. Not a number that's close, not a number that's directionally right, the number you wrote down before you touched the logic. Fix the calculation until the assertion passes against those four rows. This is where most of the real bugs surface, because writing the mock data forces you to decide, explicitly, how a mid-month upgrade gets prorated, before you've convinced yourself you already know the answer.
Clean: once it's green, you're not done, you've covered exactly one scenario. Add the next one: a refund issued after the invoice closed, a currency conversion, a customer who churned and came back inside the same month. Run red, then green, for each of those in turn. Clean also means stripping out anything in the logic that only exists to make one specific test pass, a hardcoded customer ID or a magic date range that snuck in while you were chasing green. And it means the fixtures themselves stay isolated, one test's mock data shouldn't leak into another's, and reproducible, so the suite still means something when someone touches this logic eight months from now with no memory of why the free trial exclusion is there.
The tooling to run this doesn't matter as much as people think. dbt's test framework makes the red-green loop fast if that's already in your stack, but you can do the same thing with a pytest suite pointed at a scratch schema, or a plain SQL script that loads fixtures into a temp table and diffs the output against expected values. What matters is that the mocked scenarios exist at all, and that they cover the edge cases that actually break metrics in production: partial periods, refunds, timezone boundaries, NULL dimensions, late-arriving events, duplicate records. None of that shows up if you only ever test against a clean sample of production data, because production data won't hand you the case you haven't seen yet. It'll just quietly produce the wrong number until you do.
I'd also push back on the instinct to treat this as a one-time exercise. A metric's definition drifts the same way application logic drifts, someone adds a new subscription tier, a new region goes live in a different timezone, finance changes how refunds get recognized. Every one of those is a reason to write a new red test before you change the calculation, not after someone flags that the number moved. The mock scenario for a currency conversion you haven't launched yet costs you twenty minutes today. Explaining a wrong revenue number to the board costs a lot more than that.
A metric that's never had a red test is a metric nobody has actually verified, it's just a query that hasn't been proven wrong yet. Treat it like the load-bearing function it is.