the zero-rows-means-healthy convention is the part i'd steal first. trivial alerting, and it forces every check to define what wrong looks like up front.
also agree on starting with grain and freshness. the unknown bucket one is the quiet killer in my experience, mapping tables drift for months and nobody notices because each individual landing is tiny.
Late reply, but you're right about the unknown bucket. What helped me: alert on the share of rows landing in "unknown", not the count, and compare it to the trailing 14-day average. A mapping table drifting by 0.2% a day never trips a fixed threshold, but a rising share does. Same zero-rows-means-healthy shape: select the day only if the share is over 1.5x its average.
the relative check is the right fix for slow drift. one guard i would add is a floor on the share itself, so a jump from a tiny base does not page anyone. 1.5x of almost nothing is still almost nothing.
Yeah, the floor is the missing piece. I'd put three conditions on the same row: share above 1.5x its 14-day average, share above some absolute floor (0.5% or whatever "unknown" normally sits under), and a minimum row count for the day so a quiet Sunday doesn't fire on 3 rows out of 40.
that combination is neat, the row-count floor is the part that saves it on quiet days. a check that only fires when the share is up and the sample is big enough is one i would actually leave on.
Practical Companion Guides for developers (Git, Python, JavaScript, SQL, VS Code, command line) at systemcraftpress.com. As featured in PyCoder's Weekly.
The grain check (#3) is doing something worth naming explicitly: count(*) <> count(DISTINCT order_id) is directly measuring fan-out â the moment a join turns "one row per order" into "one row per order line," this catches it in one comparison. It's a nice complement to catching the bug at write time (reviewing which side of a join is one-to-many before you aggregate over it) â this catches it after the fact, on data that already shipped.
One edge case worth flagging for anyone adopting this: the grain check only fires if the count changes. If a join fans out 1:1 by coincidence (say, every order happens to have exactly one matching row in the joined table on a given day), the row count matches and this stays green even though the join is still semantically doing something risky the moment that assumption breaks. Worth pairing with an explicit check on the join key's cardinality on the "many" side, not just the row count after the fact.
Good catch, and that one's nastier than it sounds because it can stay green for months. Row count only moves once the data actually has a second match. The cheap fix is to assert the key you're joining on is unique on the side that's supposed to be "one", before the join ever runs:
SELECT customer_id FROM dim_customer GROUP BY customer_id HAVING count(*) > 1
Zero rows means the assumption still holds. It fires the day someone loads a duplicate, which is usually weeks before the fan-out would show up in a total. I'll add it to the post as #3b.
One thing I've seen with reconciliation is that matching totals can still hide some pretty serious problems.
Take a payment platform processing card transactions through multiple PSPs. A duplicated capture and a missing refund can offset each other in the daily totals, so the dashboard still looks fine. But individual customer balances are already wrong.
I'd keep a transaction-level reconciliation alongside the aggregate checks, matching on provider transaction ID, currency and amount, then tracking unmatched captures, duplicate settlements and refunds separately. Settlement date also needs its own treatment because it doesn't always line up with the original transaction date.
The aggregate tells you whether the numbers add up. The record-level check tells you whether they're actually right.
Yes, that's the one that bit me too. A duplicate capture and a missing refund of about the same size net to zero and the daily total stays green.
What I run next to the totals now: join both sides on the provider transaction id and look at two numbers, the net difference and the gross one (sum of abs(amount difference) per matched row), plus a full join count of unmatched ids on each side. Net zero with gross above zero means the totals agree by accident.
That's a solid approach. I especially like keeping the gross difference next to the net one, because it exposes cases where opposite errors cancel each other out.
One additional edge case I'd watch for is join cardinality. In payment systems, a single provider transaction ID can sometimes have multiple capture, refund, or adjustment records. If you join those tables without accounting for the actual transaction grain, you can accidentally multiply rows and inflate the gross difference.
I'd probably validate key uniqueness on both sides first, then separate one-to-many payment events before comparing amounts. Otherwise, the reconciliation query itself can introduce inconsistencies that look like real data issues.
It's interesting how a dashboard can pass every aggregate check while the underlying records tell a completely different story.
Yeah, that one is sneaky because the inflated gross looks exactly like a real mismatch.
What I do now is roll each side up to one row per provider transaction id before the join (captures minus refunds minus adjustments as one amount, plus a count of events). And a check before that: count(*) minus count(distinct provider_txn_id) on each rolled-up side has to be 0, otherwise the query stops there instead of reporting differences.
Partial captures then show up as the event count differing between the two sides, which is usually the interesting bit anyway.
We're a place where coders share, stay up-to-date and grow their careers.
the zero-rows-means-healthy convention is the part i'd steal first. trivial alerting, and it forces every check to define what wrong looks like up front.
also agree on starting with grain and freshness. the unknown bucket one is the quiet killer in my experience, mapping tables drift for months and nobody notices because each individual landing is tiny.
Late reply, but you're right about the unknown bucket. What helped me: alert on the share of rows landing in "unknown", not the count, and compare it to the trailing 14-day average. A mapping table drifting by 0.2% a day never trips a fixed threshold, but a rising share does. Same zero-rows-means-healthy shape: select the day only if the share is over 1.5x its average.
the relative check is the right fix for slow drift. one guard i would add is a floor on the share itself, so a jump from a tiny base does not page anyone. 1.5x of almost nothing is still almost nothing.
Yeah, the floor is the missing piece. I'd put three conditions on the same row: share above 1.5x its 14-day average, share above some absolute floor (0.5% or whatever "unknown" normally sits under), and a minimum row count for the day so a quiet Sunday doesn't fire on 3 rows out of 40.
Each one alone gets noisy. All three together only return a row when the share is meaningful, rising, and backed by enough data to trust.
that combination is neat, the row-count floor is the part that saves it on quiet days. a check that only fires when the share is up and the sample is big enough is one i would actually leave on.
The grain check (#3) is doing something worth naming explicitly:
count(*) <> count(DISTINCT order_id)is directly measuring fan-out â the moment a join turns "one row per order" into "one row per order line," this catches it in one comparison. It's a nice complement to catching the bug at write time (reviewing which side of a join is one-to-many before you aggregate over it) â this catches it after the fact, on data that already shipped.One edge case worth flagging for anyone adopting this: the grain check only fires if the count changes. If a join fans out 1:1 by coincidence (say, every order happens to have exactly one matching row in the joined table on a given day), the row count matches and this stays green even though the join is still semantically doing something risky the moment that assumption breaks. Worth pairing with an explicit check on the join key's cardinality on the "many" side, not just the row count after the fact.
Good catch, and that one's nastier than it sounds because it can stay green for months. Row count only moves once the data actually has a second match. The cheap fix is to assert the key you're joining on is unique on the side that's supposed to be "one", before the join ever runs:
SELECT customer_id FROM dim_customer GROUP BY customer_id HAVING count(*) > 1
Zero rows means the assumption still holds. It fires the day someone loads a duplicate, which is usually weeks before the fan-out would show up in a total. I'll add it to the post as #3b.
One thing I've seen with reconciliation is that matching totals can still hide some pretty serious problems.
Take a payment platform processing card transactions through multiple PSPs. A duplicated capture and a missing refund can offset each other in the daily totals, so the dashboard still looks fine. But individual customer balances are already wrong.
I'd keep a transaction-level reconciliation alongside the aggregate checks, matching on provider transaction ID, currency and amount, then tracking unmatched captures, duplicate settlements and refunds separately. Settlement date also needs its own treatment because it doesn't always line up with the original transaction date.
The aggregate tells you whether the numbers add up. The record-level check tells you whether they're actually right.
Yes, that's the one that bit me too. A duplicate capture and a missing refund of about the same size net to zero and the daily total stays green.
What I run next to the totals now: join both sides on the provider transaction id and look at two numbers, the net difference and the gross one (sum of abs(amount difference) per matched row), plus a full join count of unmatched ids on each side. Net zero with gross above zero means the totals agree by accident.
That's a solid approach. I especially like keeping the gross difference next to the net one, because it exposes cases where opposite errors cancel each other out.
One additional edge case I'd watch for is join cardinality. In payment systems, a single provider transaction ID can sometimes have multiple capture, refund, or adjustment records. If you join those tables without accounting for the actual transaction grain, you can accidentally multiply rows and inflate the gross difference.
I'd probably validate key uniqueness on both sides first, then separate one-to-many payment events before comparing amounts. Otherwise, the reconciliation query itself can introduce inconsistencies that look like real data issues.
It's interesting how a dashboard can pass every aggregate check while the underlying records tell a completely different story.
Yeah, that one is sneaky because the inflated gross looks exactly like a real mismatch.
What I do now is roll each side up to one row per provider transaction id before the join (captures minus refunds minus adjustments as one amount, plus a count of events). And a check before that: count(*) minus count(distinct provider_txn_id) on each rolled-up side has to be 0, otherwise the query stops there instead of reporting differences.
Partial captures then show up as the event count differing between the two sides, which is usually the interesting bit anyway.