Green checks, wrong number: the bug your tests can’t see

The dangerous bug isn’t the one that breaks the report. It’s the one that shrinks it. Broken gets fixed in an hour. Shrunk gets presented to the board.

TL;DR — the short version

Do any of these sound familiar? A rep changes their last name. A company rebrands mid-quarter. Someone retypes a CRM picklist with a trailing space. Each one just silently deleted a row from a report you present. No error — just a smaller number.

On a client’s GTM warehouse, a funnel report had been quietly under-counting for weeks. No error. No failed test. No alert. Nobody noticed, because there was nothing to notice — the number was plausible, the chart was smooth, and the dashboard loaded in under a second.

The cause was one shortcut, and it’s hiding in most reporting stacks:

The report skipped the governed data layer and read a spreadsheet-backed convenience table instead — one that was never meant to be complete.
It matched people by name instead of by ID. Anyone the spreadsheet didn’t know about didn’t show up as “unknown.” They vanished from the numbers entirely.
Every standard check still passed, because every check looked only at the rows that survived.

Animated: two paths from the same source to the same report. The governed path joins on a stable ID and keeps all 1,000 records. The shortcut path joins a roster spreadsheet on names; 388 records silently fall out at the join and the report renders a clean-looking chart with 612. — Neither path errors. Only one is complete — and nothing tells you which one you’re looking at.

Here’s how one spreadsheet shortcut deleted 14 reps — and 388 of their records — from a funnel report — and the three cheap rules that would have caught it on day one. ↓

What it was doing

To show performance by team, the report needed to know which team each rep belonged to. Instead of reading that from the governed reps model, it read a spreadsheet-backed reference table that had been created to seed a leaderboard.

That reference table only contained rostered members of the leaderboard. Anyone not on the leaderboard resolved to no team. And because the join was an inner join, those reps didn’t show up as “unknown.” They vanished.

Two bugs, one shape

That incident is a specific instance of a general failure, and once you see the shape you find it everywhere. Both variants produce fewer rows, silently:

Reaching around the governed layer. A seed file or staging table exists to feed the models downstream of it. It has no contract, no tests worth the name, and no obligation to be complete — only complete enough for the thing it was built for. The moment a report reads it directly, it inherits every limitation nobody documented and skips every transform that was supposed to sit on top.
Joining on text instead of IDs. Names, emails, and labels drift — every renamed rep, rebrand, and retyped picklist from the list up top silently drops a row from an inner join. Stable IDs don’t drift. That is the entire reason they exist.

Nothing in the lower path fails. The rows that fall out leave no trace — which is exactly why this survives code review, passes tests, and gets caught by a rep asking why they aren’t on the list.

Why review doesn’t catch it

Both bugs look completely fine in a pull request. join reps on rep_name is readable — arguably more readable than joining on an opaque ID. Reading from a reference table is a one-line change that returns data immediately. The code isn’t wrong in any way a reviewer can see. It’s wrong about the world, and the world isn’t in the diff.

Tests don’t catch it either — at least not the ones most teams write. A row-count test passes; there are rows. A not-null test passes; the rows that survived are fully populated. Uniqueness passes. Everything you would naturally assert is true of the subset that made it through.

What actually works

Three rules to adopt:

Consumers read marts. Always. Reports, apps, CRM syncs, and ad-hoc queries read the governed modeling layer — never seeds or staging. If a value isn’t on a mart yet, add it to the mart. The five minutes you save by reaching around is the cheapest debt you’ll ever take on and the most expensive to service, because it doesn’t come due until someone is presenting.
Join on stable IDs, and make the exception expensive. Text matching is occasionally unavoidable — some sources have no shared key. Fine, but require a comment at the join site listing every table and column searched and the conclusion that no ID exists. Not to create paperwork: to make “no ID exists” a claim someone actually verified instead of a guess nobody checked. Most of those claims dissolve on contact with a real search.
Test for the rows that should be there, not just the rows that are. This is the habit that would have caught the funnel bug on day one. Assert coverage: every active rep resolves to a team, every open opportunity resolves to an owner, the count entering a join equals the count leaving it.

Test the join’s survival rate, not just the output’s shape. An anti-join with an expected count of zero is worth a dozen not-null checks.

The audit is worth doing today

When we swept a set of production reporting scripts for this pattern on a client engagement, we found ten places reading raw source tables where a governed mart existed. Repointing them lifted coverage on one key attribute by roughly two thirds — meaning a third of the population had been resolvable all along and simply wasn’t being resolved.

Nothing had been broken. Everything had been under-reported, consistently, for as long as anyone could remember. The trend lines had been beautiful the whole time.

The ten-minute version of that audit:

List every dashboard, app, and sync that touches your warehouse.
For each one, write down which tables it reads. Anything pointing at a seed, a staging table, or a raw source when a modeled equivalent exists is a candidate.
Then check the join keys.

You will find something. Everyone does.

I audit and rebuild GTM data layers — governed models, coverage tests, and the freshness monitoring that keeps them honest. If you suspect a number your team reports on is quietly incomplete, I can tell you in a week.