Zero row load detection when an empty file is sometimes correct

A nightly test on a registration table asserted that the table was not empty for the current day. It was written after an outage, it was correct, and it fired every single night for the seven months between one edition closing and the next one opening. Somebody deleted it in the second month. Fourteen months later the same feed broke on the Wednesday of show week and nothing noticed for nine hours.

Zero row load detection is worth getting right precisely because the naive version gets deleted. The rule that survives is the one that knows when zero is the right answer.

When is an empty result the correct answer?

More often than a general purpose testing framework assumes, because a data pipeline built for a continuously trading business assumes continuous trade.

Registration for next year's edition produces nothing at all until the on sale date, which on many shows is the week after the previous edition closes and on some is four months later. Badge scan tables hold nothing until the doors open, then take everything they will ever take in three or four days. Exhibitor contract rows arrive in a burst around the space draw. Lead retrieval files exist once. Survey responses appear for a fortnight after the show and then stop.

For each of those the empty state is the accurate description of the world for most of the year. An alert that fires on it teaches the team that the channel is wrong, and the cost of that lesson is paid later at an inconvenient moment.

The arithmetic of the alert fatigue

Put numbers on the nightly test above and the case makes itself.

A portfolio of nine shows, each with a registration table that is legitimately empty for roughly 280 days a year, running one nightly not empty test per show, generates 9 times 280 alerts, which is 2,520 a year. Against that, suppose the genuinely broken loads across the whole portfolio number three in a year.

The precision of the check is 3 divided by 2,523, which is 0.12 per cent. Slightly more than one alert in a thousand carries information. No human being maintains attention at that ratio, and no amount of insisting that people should read their alerts changes it.

Gate the same check on the calendar and the arithmetic inverts. If each show has 85 days a year on which registration rows genuinely must appear, the check evaluates 9 times 85, which is 765 times, and fires only when something is wrong. Three real incidents against a handful of false positives is a channel somebody reads.

Gate the check on the show calendar

The mechanism is a condition attached to the assertion, so the test carries its own precondition rather than depending on somebody remembering to switch it off.

dbt supports this directly through a where config on a data test, documented as filtering the resource being tested, whether that is a model, a source, a seed or a snapshot. dbt Labs describes the implementation plainly: the condition is templated into the test query by replacing the reference to the resource with a subquery, so a not_null test with a where clause runs against a filtered version of the table rather than the whole thing.

For zero row detection the condition names the window rather than the rows. The check should assert that rows exist for today only when today falls inside a period where the event calendar says rows are expected, which means the condition references the event dimension rather than a literal date. Hard coding the on sale date works once and then quietly stops working when the next edition opens on a different day.

The second control worth adding from the same documentation is severity. dbt lets a test declare severity of error or warn, with error_if and warn_if conditions expressed against the integer count of failures, so a test can warn at one failure and stop the build at a larger number. Applied here, a zero row day in the shoulder of the on sale window warns, and a zero row day inside show week errors, and both are the same test with two thresholds.

Which zero are you actually looking at?

Three different incidents produce an empty table and they need three different people to fix them.

The file never arrived. The supplier's export did not run, the transfer credentials expired, or the scheduler skipped. Evidence lives outside your warehouse, in the landing area, and the diagnostic is whether a file exists at all.

The file arrived and was empty. The supplier ran the export against the wrong date range or the wrong event. Evidence is a file with a header row and nothing else, which is why the landing process should record the byte count and the row count of every file it receives.

The load ran and inserted nothing. Your own transformation filtered everything out, usually because a join key changed or a date filter is comparing against the wrong column. Evidence is a non empty landing table beside an empty target.

Record all three states explicitly in a load log, with the file name, the landing row count, the inserted row count and the exit status. Then the alert can say which of the three happened, and the first hour of every investigation stops being spent establishing it.

Great Expectations defines an expectation as a verifiable assertion about data, and that framing is the useful one here. An assertion that rows exist is only verifiable if the conditions under which it should hold are part of the assertion. A bare not empty test is an assertion about the world that happens to be false for most of the year.

Where the calendar gate itself can fail

Every calendar driven control moves the risk into the calendar, and it is worth being explicit about how it breaks.

A show that opens registration early, because a keynote was announced sooner than planned, produces real rows outside the expected window. That direction is harmless: the check simply does not run and nothing is lost except the monitoring you did not have anyway. The dangerous direction is a show whose dates move later without the event dimension being updated, which leaves the gate open across a period when the table is legitimately empty and returns you to the alert fatigue you were escaping.

The mitigation is an assertion about the calendar itself. Every event whose planned on sale date has passed must have an actual on sale date recorded, and every event whose show close date has passed must have an actual close date. Two small tests, and they protect a whole family of checks that read the same dimension, including the expected range built from prior editions that K18 uses and anything else in the unified data record that is indexed on days to show open.

Where this stops

Zero row detection catches the total failure and nothing short of it.

A feed that delivers 10 per cent of its usual volume passes a not empty test cleanly, and 10 per cent is a far more common failure than zero, because partial failures come from filters and joins while total failures come from infrastructure. The check that catches a partial shortfall is a range test or a remainder test, and testing the remainder of a decomposed series in K19 is the version that works across a season. Zero row detection is the floor beneath those, worth having because it is cheap and unambiguous, and worth nobody's confidence as a complete control.

There is a second limit that shows up on portfolios with many small events. Some tables are genuinely sparse rather than seasonal, taking a handful of rows on unpredictable days, and for those the empty state carries no information at all. A specialist conference with 300 registrations across a nine month campaign will have empty days inside its own on sale window, so the gate has to be looser and the check becomes weak. Those tables are better watched with a rolling count over a fortnight than with a daily assertion, and pretending otherwise produces a control that looks like coverage without providing any.

Start this week by listing every not empty or row count test currently running against your event tables, and for each one look up how many times it fired in the last year and how many of those times somebody did something. If the second number is zero, that test is already off in practice, and adding a calendar condition is what turns it back on.