Zero rows is an unverified result, not an answer

Zero is the one answer a counting query can return that is simultaneously a legitimate finding and a symptom of total failure, and nothing on the outside separates the two. The statement parsed. It touched only tables the asker was allowed to read. It aggregated at the right grain. It ran inside its time budget, raised no error, and produced a well-formed number. Every signal a system has for deciding that a query worked is exactly the same in the case where the answer is genuinely nothing and in the case where a filter has quietly excluded everything.

Two shapes are worth separating before going further, because a check keyed to the wrong one misses the common case. A query with no aggregate returns an empty row set when nothing matches. An aggregate with no GROUP BY returns one row whatever happens, holding a count of zero, a sum of null, or an average of null — so a system that tests whether any rows came back will conclude that this second kind of query succeeded. Both are the same failure and both are meant here by a result that reports nothing.

A count of zero and a fact about the business#

Here is a question about refunds in one country over one month, rendered into SQL against a warehouse whose curated tables sit in an analytics_marts schema.

SELECT COUNT(*) AS refunds
FROM analytics_marts.refunds r
JOIN analytics_marts.customers c ON c.customer_id = r.customer_id
WHERE c.country = 'Germany'
  AND r.refunded_at >= '2025-07-01' AND r.refunded_at < '2025-08-01'
LIMIT 1000;

The country column holds ISO-3166 alpha-2 codes. There are 41 distinct values in it and Germany is not one of them; DE is, with 88,412 customers behind it. The true answer was 1,904 refunds. The database did exactly what it was asked and returned an empty result, and the system converted an empty result into a fact about the business.

Those are different things. COUNT(*) returning 0 means no rows satisfied the predicate, which has at least two explanations: the world contains no such events, or the predicate does not describe the events you meant. SQL cannot distinguish them, and neither can a model looking at its own query, because from the inside the query looks correct.

That last clause is the mechanism and deserves unpacking rather than assertion. A generator sees the user's words and the column names it was given. It does not see the values inside those columns, because nothing put them in front of it. Rendering "Germany" as country = 'Germany' is then the correct translation of the words available, and it stays correct as a translation no matter how good the generator becomes. The information that would have made it wrong — that this particular column stores two-letter codes — was never in the input. So the error survives every improvement in SQL skill, which is a different situation from a malformed join or a misplaced aggregate.

The tempting belief here is that a query which runs is a query that has been checked. It has been, by two earlier stages, and neither of them can see this. Parsing and structural validation confirm that the statement is a single read-only SELECT with a row cap; grant enforcement confirms that the role executing it can read the tables it names. Both return the same verdict for country = 'Germany' as for country = 'DE', because the two statements differ only in a string literal and a literal is opaque to both. The dangerous property — whether the literal names anything — moves freely while every approval signal holds still.

The row cap makes the same point at close range. COUNT(*) with no GROUP BY collapses to a single row, so LIMIT 1000 in that statement was unreachable and did nothing. A cap constrains how much comes back, and this query's problem is about what went in.

Probing the literals against the domain#

The profiling job from chapter 2, which walks every column after each load and records what it contains, already computes the value domain for every low-cardinality column, and the same data answers the question the empty result raises. Before an empty or suspiciously small result is reported, each equality and IN predicate is checked against the domain of the column it filters.

def probe_literals(tree, catalog, conn) -> list[dict]:
    findings = []
    for pred in tree.find_all(exp.EQ, exp.In):
        col, literal = column_and_literal(pred)
        if col is None or literal is None:
            continue
        profile = catalog.profile(col)          # nightly, per column
        if profile.n_distinct <= 50:            # domain is known exactly
            if literal not in profile.domain:
                findings.append({
                    "column": col.sql(), "literal": literal,
                    "reason": "not in column domain",
                    "domain_sample": profile.domain[:8],
                    "nearest": nearest_values(literal, profile.domain, k=3),
                })
        else:                                    # too wide to enumerate: one probe
            n = conn.scalar(
                f"SELECT COUNT(*) FROM {col.table} WHERE {col.name} = %s LIMIT 1",
                (literal,))
            if n == 0:
                findings.append({"column": col.sql(), "literal": literal,
                                 "reason": "no rows carry this value"})
    return findings

The branch that this column takes is decided by a comparison worth reading alongside the figure two paragraphs up. n_distinct for country is the count of distinct values recorded by the profiler, and the code takes the exact-domain path whenever that count sits at or below its threshold. The country column's cardinality is comfortably inside it, which is why the lookup costs nothing: the whole domain is already in memory, so the check is a set membership test rather than a query. A column with thousands of distinct values falls to the other branch and pays for one probe against the database.

That probe carries a detail worth naming, because it looks like the mistake chapter 3 exists to prevent. The table and column names are interpolated straight into the SQL string while the literal is bound as a parameter. Those are interpolated from the parse tree and the catalogue — names the system chose, which have already been resolved against tables that exist — whereas the literal is the fragment that came from the user's question. Binding the one that came from outside and interpolating the ones that came from inside is the distinction that makes the line safe. Its LIMIT 1 is inert for the same reason as the row cap above: COUNT(*) was always going to return one row.

For country = 'Germany' the domain lookup is free — it is a dictionary in memory, refreshed nightly — and returns a finding with DE, DK and DZ as nearest values. Mapping Germany to DE in the answer would be the wrong repair, because the system does not know the user meant the country of the customer's billing address rather than the shipping destination or the store locale, all three of which exist in this warehouse. What it can do is say what happened:

I found no refunds matching country = 'Germany', but the country column holds ISO-2 codes and has no value Germany. Did you mean DE (88,412 customers)? Columns shipping_country and store_country also exist and may be the one you want.

That refusal to guess is a design decision rather than a limitation. The requirement is that a report of nothing be distinguishable from a report of none; substituting DE and answering would satisfy it in this instance and would also invent a choice among three country columns that the asker never made. Naming the alternatives discharges the requirement without making the choice.

The specificity is the point. A generic "I couldn't find an answer" teaches users to rephrase at random until something comes back, which selects for whichever phrasing happened to produce non-empty output rather than for the correct one.

The empty result has siblings#

Once you are inspecting the rows before composing an answer, several distinct failures show up in the same place, and they are worth handling as one check with several rules rather than as separate features.

The third row rewards a careful reading, because its cause and its message do not describe the same event. A twelve-in-a-hundred orphan rate does not empty a result on its own; it empties one only when the filter happens to select inside the orphaned rows. What the message reports is the orphan rate the probe measured, which is evidence for the hypothesis in the middle column rather than proof of it, and a reader who takes the two columns as a single sentence will expect the check to fire far more often than it does.

The NULL row deserves a note because it is the one that produces a plausible number rather than an empty one. WHERE status != 'REFND' excludes every row whose status is NULL, since NULL != 'REFND' evaluates to NULL rather than true, and in this warehouse 4,102 orders have a NULL status because of a partial migration in 2023. A count that should be 2,984,116 comes back as 2,980,014, which is 0.14% low and will never be questioned by anyone. Checking whether a negated predicate touches a nullable column is a two-line rule that catches all of them.

Those three figures close on each other exactly, and the closure is what makes the diagnosis usable rather than merely plausible. Subtract the returned count from the count that should have come back and the difference is the NULL-status row count, to the last row, with nothing left over. Divide that same difference by the true count and the quotient is the shortfall quoted in the sentence. So the NULL rows are not one contributor among several; they are the entire gap, and there is no second exclusion hiding behind the first. That is a stronger statement than "NULLs may be involved", and it is the kind of statement an exact residual of zero licenses and an approximate agreement never does.

It is also the sharpest version of the belief this chapter is about. The magnitude of the answer barely moves — a fraction of a percent — while its correctness moves completely, so every instinct calibrated on how big a number looks reports that nothing happened. An empty result at least announces itself. A count short by a fraction of a percent announces nothing, survives review, and is the reason the rule is written against the shape of the predicate rather than against the size of the result.

The mixed-currency case is the one that most often ends in an abstention rather than a caveat. Summing 2.6M EUR orders with 390k GBP orders and 98k USD orders produces a number with no unit, and there is no honest way to present it. The check counts distinct currency codes in the result and refuses to report a monetary total across more than one, offering either a per-currency breakdown or a conversion through fx_daily joined on both currency and date. Reading those three counts as a gap rather than as a list explains why the failure is quiet: the euro rows outnumber the other two together by more than five to one, so a mixed sum lands close to the euro-only figure and looks entirely reasonable. The error is largest in proportion exactly when the minority currencies are worth most per order, which is a fact about the basket and not about the query.

A partial period is worse than an empty one#

Freshness deserves its own treatment because it produces a number rather than a gap. The events pipeline in this warehouse lands hourly with a lag that is usually two to three hours and occasionally, after a failed run, eighteen. A question asked on the 7th about "this month" gets six days of data presented as a month, and a question asked at 09:00 about "yesterday" gets a day that is 92% loaded. Both return plausible figures with no indication that anything is missing, and both are wrong in the direction of understating, which is the direction that generates a false alarm in a leadership channel.

The direction is worth deriving rather than accepting, because it is what makes the failure asymmetric. Missing rows can only be rows that have not landed yet, so a count over an incomplete period is a count over a subset of the true period. A subset cannot be larger than the set. Every metric built by counting or summing non-negative quantities therefore reads low, and a metric read low against a target or a prior period looks like a decline. The alarm is generated by data that has not arrived rather than by behaviour that has changed, and the people it reaches have no way to tell those apart from the number alone.

Every mart view therefore publishes a watermark — the maximum loaded timestamp and the load's completion time — and the result check compares the query's requested range against it. Three outcomes follow: entirely inside the watermark and the answer is delivered plainly; extending past it by less than a period and the answer is delivered with the coverage stated ("through 06 Aug 14:00 UTC, so 6 of 31 days"); extending well past it, or the watermark itself being more than four hours stale, and the system declines and says when data is expected. The rule that stops most of the damage is refusing partial periods by default: a question about "this month" is answered with last month plus an explicitly labelled month-to-date figure, rather than with one number whose period the reader will assume is complete.

Plausibility is a weak check that catches strong errors#

Comparing a result against its own history is crude and finds things nothing else does. If last month's refund count was 1,904, the month before 1,847, and this month's query returns 12, something is wrong with the query rather than with the business, and the system does not need to know what. A band built from the previous six values of the same metric and grain — flag anything outside three times the interquartile range, or a factor of five from the median where history is short — fires on about 1.2% of answers and is right roughly two thirds of the time.

The reason to keep it despite that hit rate is what it catches: errors with no structural signature at all. A filter on the wrong date column, a segment code that changed meaning, an upstream table that stopped loading — none of these produce an empty result, a grain violation or a missing literal, and all of them produce a number that is obviously wrong to anyone who has seen last month's. The check is not a gate; it attaches a line to the answer that says the figure is 96% below the recent range and asks the reader to confirm, which costs a sentence and converts a silent error into a question.

What the band cannot do follows from how it is built. It is assembled from previous values of the same metric at the same grain, so it can only ever compare a number against its own history, and it fires when a value sits far outside that history. Two consequences follow. An error that moves a figure by less than the historical spread is inside the band by construction and will never fire, which covers the NULL exclusion above and most unit confusions below a factor of ten. And an error that has been present since before the six values were collected is not an outlier at all, because the history itself carries it.

Abstention itself needs a budget. A system that declines 30% of questions is not cautious, it is unusable, and users respond by going back to the analysts with the additional belief that the tool does not work. Tracking the abstention rate as a first-class metric alongside accuracy — 8% here, with a target ceiling of 15% — keeps the checks honest, since every new rule has to justify its share of that budget against the errors it prevents. Reading those three figures as gaps rather than levels is what makes the budget operational: the distance from where the system sits to its own ceiling is roughly the size of what it has already spent, so the next rule of the same weight as everything shipped so far is affordable and the one after it is not.

What the second check costs#

Two extra round trips in the worst case — the join-elimination probe and the watermark query — plus about 900 tokens for the model to compose an abstention or a caveat. Measured on this system, the added latency is a p50 of 180ms against a p50 answer time of 2.4 seconds, and the added token cost is roughly $0.003 per question. The domain lookups are memory reads and cost nothing measurable. Set the added latency against the answer time it is added to and the check consumes under a tenth of the budget, which is the comparison that decides whether a pre-answer check is affordable at all.

On the 300-question evaluation set the check fired on 8% of questions: 6.3 percentage points where the answer would otherwise have been wrong or unsupported, and 1.7 points where it was over-cautious and an analyst confirmed the original result was fine. Those 1.7 points are the same kind of residue as the previous chapter's, handled the same way — a caveat attached to a delivered answer rather than a refusal.

Those two shares add to the firing rate exactly, and reading what that does and does not license matters more than the shares themselves. It licenses treating the split as a partition: every firing was adjudicated one way or the other, and there is no third bucket of firings nobody resolved. It licenses nothing about the questions the check stayed silent on. The denominator throughout is firings, so these figures bound how often the check is wrong when it speaks and say nothing whatever about how often it should have spoken and did not. The miss rate is the number a reader most wants here, and it is not in this paragraph or anywhere near it.

The teammate who wants to delete it#

The proposal arrives about four months in, usually after a model upgrade and a good week. Current models are strong at SQL, the argument goes, check 2 fires on 8% of questions of which a fifth are false alarms, it adds latency to every answer, and it is code someone has to maintain. Chapter 3's check is the security-relevant one; this is quality assurance for a component that has stopped failing.

That "a fifth" is worth checking rather than waving through, and it survives the check: the over-cautious share of the firing rate, taken against the firing rate itself, is a fifth to the precision anyone would quote it at. The argument is built on the system's own published numbers and it reads them correctly.

The premise is right and the conclusion does not follow from it. Model quality moves the rate at which wrong-but-valid queries are produced; it does not move the fact that they exist, because the failure is not a lack of SQL skill. country = 'Germany' is not a mistake about SQL. It is a correct rendering of the user's words into a predicate over a column whose contents the model could not see, and no amount of capability fixes an input that was never provided. The same holds for a fiscal quarter that starts in February and for a status value retired in 2021.

The cost asymmetry is what settles it. A false alarm costs one analyst two minutes and a mildly annoying message. A wrong number costs whatever decision it informed, and the loss is unbounded and undetectable at the point of delivery: the person receiving it has no way to check it, which is why they asked. The support director above had no path to noticing. She would have found out when someone else pulled the number a different way, if that ever happened.

There is also a direction-of-travel argument that runs opposite to the intuition. As models improve, the errors that remain are the subtle ones — the plausible date column, the almost-right segment filter, the metric defined slightly differently from finance's — while the obvious errors that a human spots at a glance disappear. The value of an automated check rises as the residual error becomes harder to see by eye.

Keeping the check is not the same as trusting it, and the limit is easiest to see through a question an interviewer would ask: name a query that passes both the grain check and this one and still produces a wrong answer. The honest answer is not exotic. Consider a question about last month's active users, answered by a query over analytics_marts.events that counts distinct user_id where an event was recorded in the calendar month. Every literal exists. Every join preserves grain. The rows are non-empty, the currency is not in play, no NULLs are dropped, nothing is truncated. The number is 133,912 and it is presented without a caveat.

Finance's dashboard says 141,700 and growth's says 128,400, because one of them counts any authenticated session and the other requires a qualifying action, and one uses account-local time while the other uses UTC. Read as gaps, the three sit in a narrow band: the queried figure falls between the two published ones, and the outer two differ from each other by under a tenth of the middle. Every instrument in this chapter is defeated by that arrangement at once. The literals exist, so the domain probe is silent. The result is non-empty, so the sibling rules are silent. And a plausibility band built from the history of this metric would contain all three figures comfortably, because they are all plausible — the disagreement is about what was counted, and a spread that narrow is exactly what a band is built to ignore.

A third check cannot read the returned rows to settle this, since the rows are correct for the query that was asked. It has to look at something upstream of the query, which is the definition the question invoked and the query silently chose among.