The Join That Silently Deleted Rows

A sales report was short. Not dramatically — enough that someone noticed a marketplace channel looked lower than expected and asked the question.

The orders were in the raw ingestion table. They were in the intermediate model. They were absent from the final aggregate everyone actually used. No job had failed. No test had flagged anything. Somewhere between two models, rows were being discarded, and the SQL doing it looked entirely reasonable.

There were two separate causes, and both are common enough to be worth describing precisely.

Cause one: an inner join on an incomplete dimension

The intermediate model enriched each order with product attributes by joining to a product dimension on a marketplace-specific identifier.

That identifier was missing from the dimension for some products. New listings, mostly — items that existed in the order stream before anyone had added them to the product catalogue.

An inner join drops those rows. Not with a warning; that is its defined behaviour. The join did exactly what it was told, and the result was that any order for a product the dimension didn't know about vanished from the report.

The fix was a fallback: join on the marketplace identifier where it exists, fall back to the internal product code where it doesn't. That recovered most of the missing orders and, more usefully, made the model resilient to the catalogue lagging behind the order stream — which it always will, because the catalogue is maintained by people and the order stream isn't.

Cause two: a null filter that looked like a quality check

The second cause was subtler. A downstream model filtered to rows where product category was not null.

That filter was written with good intentions. Null category rows had caused an ugly-looking report once, and excluding them made the output tidy.

But category was derived from the dimension join above. So any order whose product wasn't in the catalogue had a null category — and got filtered out a second time, even after the join was fixed.

Two independent mechanisms, both discarding the same rows, for different stated reasons. Fixing one didn't fix the symptom, which is exactly why this took a while to diagnose.

The second fix was to include null-category rows, with a separate validation that the product code itself was present. That distinguishes "we don't know this product's category" from "this row is malformed" — which are different things that a single null check conflates.

Why nothing caught it

Worth sitting with, because the testing failure is more instructive than either bug.

The final table passed every test it had. Rows were unique. Required fields were populated. Freshness was fine. All true — of the rows that survived.

Tests that run on the output cannot see what the output is missing. This is the same blind spot that makes reconciliation against the source valuable, and it applies just as much between two models as it does between a source system and a warehouse.

The check that would have caught it is a row-count relationship between adjacent models: if the intermediate model has more qualifying rows than the final one, something consumed the difference and should be able to say why. Sometimes that's legitimate — a deliberate filter. The point is that it should be asserted, not discovered eight months later by someone who thought a number looked low.

What we do differently now

Prefer left joins in enrichment models. If you're adding attributes to a fact, a left join preserves the fact. Missing attributes become nulls you can see and count, rather than rows that disappear. Reserve inner joins for cases where the absence of a match genuinely means the row shouldn't exist.

Assert the row-count relationship between models. A test that fails when a model loses more than an expected share of its input turns a silent leak into a build failure.

Treat null filters as suspicious. WHERE x IS NOT NULL is often a real constraint. It is just as often someone tidying up a symptom whose cause is upstream. Every one deserves a comment explaining which it is.

Distinguish "unknown" from "invalid". A missing category and a malformed row need different handling. Collapsing them into one null check guarantees you'll eventually drop data you wanted.

None of this is sophisticated. It's the difference between SQL that is correct on the data you happened to test and SQL that is correct on the data you'll actually get.