Engineers test code. Finance ties out numbers; the month does not close until the sub-ledgers agree with the general ledger, and nobody considers that optional or insulting. Analytics sits between those two cultures and too often inherits neither discipline. A query executes without errors, the result grid fills in, the number looks plausible, and it goes in the deck. The query running is roughly the midpoint of the work.
This post is the finance discipline applied to queries: four checks, in the order I run them, and the rule that holds them together. You never ship a number you haven't tried to break.
The worked examples run on CellarDB, the series dataset (setup page).
Predict the row count before you run
The cheapest test available costs nothing and almost nobody runs it: before executing, say out loud how many rows should come back. Not after, when any count looks reasonable because it's sitting in front of you. Before.
You usually know the answer, because you know the grain. One row per open contract: that's however many rows BulkContracts has with Status = 'Open', a number you can get independently in five seconds. One row per vineyard block: about forty. One row per lot currently aging: you may not know exactly, but you know it's not four, and it's not forty thousand.
-- expectation: one row per open contract
-- predicted: SELECT COUNT(*) FROM BulkContracts WHERE Status = 'Open'I write the expectation as a comment at the top of the query, exactly like that, and compare it against the row count in the SSMS status bar after running. When they disagree, the query is wrong or my understanding of the data is wrong, and both of those are things I badly want to know before anyone else sees the number. A result with more rows than predicted usually means a join fanned out. Fewer usually means an INNER JOIN or a <> silently dropped rows with NULLs in them. Both are failure modes this series spent whole posts on, surfacing in a single integer at the bottom of the screen.
The check only works in this direction. A row count you didn't predict confirms nothing; it just gets rationalized.
Tie out against something trusted
A tie-out compares your number to an independent number that the business already trusts, computed a different way from different rows. Agreement doesn't prove you're right, but disagreement proves something is wrong, in your query or in the data itself, and either one is worth knowing before the number ships.
CellarDB carries two versions of the same physical fact. WineLots tracks each lot's volume; BarrelFills tracks what was actually put into barrels. For a lot that is fully in barrel, the fills should sum to the lot. So make the database say so:
-- tie-out: current barrel fills vs. lot volume, aging lots only
SELECT
wl.LotCode,
wl.VolumeGal AS lot_volume,
SUM(bf.FillVolumeGal) AS barreled_volume,
SUM(bf.FillVolumeGal) - wl.VolumeGal AS diff_gal
FROM WineLots AS wl
JOIN BarrelFills AS bf
ON bf.WineLotID = wl.WineLotID
WHERE wl.Status = 'Aging'
AND bf.EmptyDate IS NULL
GROUP BY wl.LotCode, wl.VolumeGal
HAVING ABS(SUM(bf.FillVolumeGal) - wl.VolumeGal) > 1.0;The HAVING line is a tolerance. Real reconciliations always have one, because DECIMAL rounding and small physical losses produce noise; you set the threshold where noise ends and signal begins, and a gallon is generous here.
Run that against CellarDB and exactly one lot comes back. Its barrels claim 59.4 gallons more wine than the lot contains.
The part a decade of reconciliations actually teaches, the part I have never found in a textbook, is that differences have shapes. Before you read a single row, the size of a discrepancy tells you where to look. A difference of exactly ten times something is a decimal point. A small ugly difference with digits everywhere is rounding. And a difference that exactly equals one unit of something — one shipment, one invoice, one pallet — means a whole row is duplicated or missing in the table with that grain. This lot is off by 59.4 gallons, and 59.4 gallons is precisely one standard barrel. The tie-out has failed and named its suspect in the same breath: BarrelFills, one fill event too many.
Check the grain for duplicates
So we go looking for a duplicated fill. The duplicate check is the grain question from earlier in the series turned into an assertion. BarrelFills means one row per fill event, so any (barrel, lot, date) appearing twice is two claims about one event:
-- assertion: one fill event per barrel, lot, and date
SELECT BarrelID, WineLotID, FillDate, COUNT(*) AS n
FROM BarrelFills
GROUP BY BarrelID, WineLotID, FillDate
HAVING COUNT(*) > 1;One pair comes back: the same barrel, the same lot, the same fill date, two different BarrelFillIDs, entered a day apart. Someone logged the fill twice, and the IDENTITY primary key never had a chance of catching it: as the grain post established, a surrogate key guarantees distinct rows, not distinct events.
The repair is the ROW_NUMBER dedupe pattern from the window functions post, so I won't rebuild it here. The point that belongs in this post is what the duplicate was about to do: 59.4 phantom gallons, riding silently into the barrel inventory report, the vintage yield summary, and anything else that sums FillVolumeGal. Nothing would have errored. Every query would have run clean. The number would have been wrong by one barrel for as long as nobody checked.
Run a sanity ratio
Row counts and tie-outs are internal checks; the data agreeing with itself. A sanity ratio checks the data against the world. Every domain has quantities with known physical ranges, and dividing two aggregates by each other costs one query.
In a winery, the load-bearing ratio is gallons per ton. A ton of grapes yields somewhere around 150 to 170 gallons of wine; the exact figure moves with variety and pressing, but it does not move to 80 and it does not move to 300. Compute each side at the same grain first (aggregate, then join, never the reverse) and divide:
WITH fruit AS (
-- one row per vintage: tons crushed
SELECT Vintage, SUM(Tons) AS tons
FROM FruitLots
GROUP BY Vintage
),
wine AS (
-- one row per vintage: gallons from estate fruit
SELECT Vintage, SUM(VolumeGal) AS gallons
FROM WineLots
WHERE FruitLotID IS NOT NULL -- purchased bulk has no tons behind it
GROUP BY Vintage
)
SELECT
f.Vintage,
w.gallons / f.tons AS gal_per_ton
FROM fruit AS f
JOIN wine AS w
ON w.Vintage = f.Vintage
ORDER BY f.Vintage;A vintage near 160 is healthy. A vintage at 320 means something doubled, either a fan-out or the duplicate fill from the last section if you computed gallons from BarrelFills. A vintage at 75 means rows went missing, and the FruitLotID IS NOT NULL filter is the kind of thing to suspect first: leave it out and purchased bulk wine inflates the numerator with gallons that no ton of estate fruit ever produced.
Your domain has its own versions. Revenue per unit. Component percentages summing to roughly 100 per blend, though check COUNT(*) against COUNT(ComponentPct) first, because a NULL percentage makes the sum fall short while looking like a math error. Orders per customer per month. The ratio doesn't need to be precise. It needs to be the kind of wrong you can see from across the room.
Ship the checks with the report
None of this is one-time work, and that is the last habit worth stealing from finance. The tie-out and the grain assertion above are not things I ran once while developing the report; they live in the report's script, and they run on every refresh. Finance doesn't re-derive its controls each month, it operates them. A check that ran in development protects the number you shipped in development. Data changes; next month's duplicate hasn't been entered yet.
The format I use is deliberately blunt: each assertion is written so that the correct result is zero rows. An empty grid means pass. Anything else is the failure, already itemized, because every reconciliation difference is made of specific rows and the fastest path to the cause is having them in front of you.
So, the rule, with its full weight: never ship a number you haven't tried to break. Predict its row count, tie it to something trusted, assert its grain, ratio it against the physical world. Most days, every check passes and the whole ritual costs five minutes. But the asymmetry is enormous. When you find the 59.4 gallons, it costs you one quiet morning. When the person reading your report finds it, it costs the report — and every number you send after that arrives with an asterisk you can't see and didn't put there. Trust in a number is bought before it ships, never after.
— Lo