A join to order_items counts each order once per line item

A generated SQL statement can be correct in every respect a machine is currently checking and still return a number that is wrong by a multiple. It can name real tables, join them on real keys, filter on real values, read only what it is allowed to read, and come back in a fraction of a second with a single figure that is several times too large. Nothing in it errors, and nothing about the figure looks strange.

Two terms carry most of the weight here. A table's grain is the column set that uniquely identifies one of its rows: an orders table has one row per order, so its grain is the order identifier, while a line-items table has one row per line of one order, so its grain is the order identifier together with a line number. A fan-out is a join that repeats each row of one table once per matching row of another, which happens whenever the join condition does not cover the full grain of the table on the many side. Those two ideas are enough to explain the failure, to write a check that catches every version of it, and to say exactly where that check stops working.

A query that every current check approves#

Here is a statement asking for one quarter of enterprise revenue out of a warehouse whose curated tables sit in an analytics_marts schema.

SELECT SUM(o.order_total) / 100.0 AS revenue_eur
FROM analytics_marts.orders o
JOIN analytics_marts.order_items i ON i.order_id = o.order_id
JOIN analytics_marts.customers c ON c.customer_id = o.customer_id
WHERE c.segment = 'ENT'
  AND o.order_date >= '2025-04-01' AND o.order_date < '2025-07-01'
LIMIT 1000;

It is a single SELECT with a LIMIT, inside the granted schema, running as bot_ro, returning in 200ms. Every table, every join key and every filter in it is correct. The tables are the ones an analyst would use, the segment code is the right one, and the date range is the quarter. The number is wrong by a factor of 3.2.

The join to order_items produces one row per line item, so an order with four lines appears four times, each carrying its own copy of o.order_total. Summing that column over line-item rows adds each order's total once for every line it has. The order_items join contributes nothing to the query — no column from it is selected, filtered or aggregated — which is what makes the bug easy for a reviewer to miss and easy for a generator to produce, since joining a fact to its detail table is a shape that appears in thousands of legitimate queries.

The tempting belief to name is that this statement has already been examined. It has: the structural validation of chapter 3, which parses the statement and insists it is a single read-only SELECT carrying a row cap, passes it, and the grant enforcement of chapter 4, which runs it under a role holding read access to nothing but the mart schema, passes it too. What those checks return is the same for this query as for the corrected one a few paragraphs below, and it stays the same however many detail tables are joined in. The property that differs between a right query and this one — the grain of the rows the aggregate consumed — is not a property either check measures. That is the shape worth learning to spot: a set of signals that holds steady while the dangerous quantity moves underneath it.

The row cap is the sharpest instance. SUM with no GROUP BY collapses everything to one row, so LIMIT 1000 was never going to be reached no matter how many line items the join emitted. A cap bounds what comes back; it does not bound what went in, and an aggregate is precisely the operation that separates those two. A reader who takes the LIMIT as a guard against runaway joins has read it as a guard on the wrong side of the aggregation.

Counting an order once per line#

The arithmetic is worth doing on a case small enough to hold in one line before it is stated in general, because the general statement is easy to believe once one instance has been seen and hard to believe on its own.

Take two orders. The first is worth a and has a single line item. The second is worth b and has three. The correct revenue is a + b, because revenue is a property of orders and there are two of them. Now join to the line-item table on the order identifier. The first order matches one line, so it produces one row. The second matches three lines, so it produces three rows, and each of the three carries the same b, because order_total is a column of the orders table and the join copies the parent's columns onto every child row rather than splitting them. Adding the total column down those four rows gives a + b + b + b.

The gap between that and the truth is two copies of b, which is one fewer than the second order's line count, times its value. Nothing about the number b changed; what changed is how many times it is present. That is the whole mechanism, and the general form is the same sentence with sums in it.

Write t for an order's total and n for how many line items it has. The true revenue is the sum of t over orders. The joined query computes the sum of n × t over the same orders, because the join emits exactly n rows for that order and each row carries the identical t, so summing over rows is summing n copies of t. Substituting one for the other is allowed for exactly that reason and for no other: it holds because the copied column is constant across an order's rows. A column that varied per line — a line's own price, say — would not survive the substitution, and summing that column is not a bug at all.

Subtract the true total from the joined one and every order contributes (n − 1) × t. That form says three things at once. It is zero for any order with a single line, since n − 1 is then zero, so single-line orders are invisible to this bug. Where order totals are non-negative, every remaining term is non-negative as well, so the joined total can only ever be at least the true one — a fan-out inflates and never deflates. And the two totals are equal exactly when every order carrying a non-zero total has exactly one line, which is the condition under which the join was harmless to begin with.

The non-negativity step carries a condition worth stating rather than assuming. It holds while order totals are non-negative, and a warehouse that stores credit notes or reversals as orders with negative totals breaks it: a heavily multi-line credit note is inflated in the negative direction and pulls the joined total below the true one. The direction of the error is a fact about the data, and only the equality condition survives without one.

Dividing instead of subtracting gives the ratio, and the ratio is where the diagnostic value sits. The joined total over the true total is the sum of n × t over the sum of t, which is an average of the line counts with each order weighted by its own value — a value-weighted mean rather than a plain one. It coincides with the ordinary average number of lines per order when line count and order value are uncorrelated across the orders in scope, and it runs above that average when larger orders tend to carry more lines. So the multiplier is not a property of the join. It is a property of the data the join met, which is why it will not be a round number like 2 or 10; it will be something like 3.2 or 1.7 that happens to equal a statistic about the data. When the ratio is 1.0004, the cause is somewhere else entirely — a handful of rows, a timezone boundary, a late-arriving batch.

Deduplication, and the two ways it fails#

The reflex fix is to deduplicate, and it is worth working through carefully because it appears to work.

-- still wrong: rows differ by line-item columns, so nothing is a duplicate
SELECT SUM(DISTINCT o.order_total) FROM ...   -- also collapses two orders of equal value

-- correct for this query, and only this query
SELECT SUM(o.order_total) / 100.0
FROM analytics_marts.orders o
JOIN analytics_marts.customers c ON c.customer_id = o.customer_id
WHERE c.segment = 'ENT' AND o.order_date >= '2025-04-01' AND o.order_date < '2025-07-01';

SELECT DISTINCT on the fan-out query removes nothing when the selected rows carry line-item columns, since those columns differ. SUM(DISTINCT o.order_total) collapses two genuinely different orders that both totalled €45.99, which turns an overcount into an undercount and is harder to notice.

Both failures come from the same mismatch. What DISTINCT does is fixed: it drops rows that agree in every selected column, or values that agree with an earlier value. Whether the repeated rows agree in every selected column is not fixed at all, and it depends on the select list, which sits elsewhere in the statement. So a reader inspects the deduplication, finds it doing exactly what it always does, and never sees the property that decides whether it helps. On a select list carrying line-item columns it removes nothing; on a bare column of values it removes too much, because two orders of equal value are not duplicates of each other in any sense the question cares about.

The version that works removes the useless join, which is right and is a fix to one query. Separating the requirement from the decision is what makes that visible: the requirement is a total at order grain, and dropping the join is one design decision that meets it. Aggregating with COUNT(DISTINCT order_id), or collapsing to order grain in a subquery before summing, are others that meet the same requirement while keeping the join for a column that actually needs it. Ship the deletion on its own and the same class returns next week as a count of customers joined through orders, or an average basket size joined through refunds.

Deriving the grain of a result set#

The check that covers the class needs one piece of metadata per table — its grain, the column set that uniquely identifies a row — and one piece per join edge. Both live in the same catalogue that feeds the table cards, and both are things a data team either knows or ought to.

A join multiplies when the join condition does not cover the full grain of the table on the many side. orders JOIN order_items ON order_id covers order_id but not line_number, so the result carries order_items' grain and each order row is repeated. orders JOIN customers ON customer_id covers all of customers' grain, so it adds columns without adding rows. orders JOIN fx_daily ON currency_code alone, without the date, is the same bug wearing different clothes and multiplies by the number of days of rates.

That table is more useful read across than read down, because the interesting quantities are the gaps between its columns rather than the levels in them. Divide the line-item row count by the order row count and the quotient is the average number of lines per order — and it agrees with the multiplier declared one column to the right, to the two significant figures both are quoted at. Those two figures share no step. One is a pair of COUNT(*)s over two tables; the other is whatever the profiling job computes. Their agreement licenses a specific claim and no more: the declared multiplier is a real average over this whole table, not a figure carried over from a sample or a stale load. What it does not license is applying that multiplier to any particular query, because the ratio a query suffers is the value-weighted average derived above, and the weighting is invisible to both instruments.

The refunds row looks like it contradicts the same arithmetic, and it does not. There are far fewer refund rows than order rows, so refunds per order across the whole orders table is a small fraction, nowhere near the 1.08 in the last column. The two numbers are measuring different populations. An inner join to refunds drops every order that has no refund, and 1.08 is the average number of refund rows among the orders that survive that drop — a multiplier conditional on matching. It reads as a near-harmless figure and it hides a second effect: a selective inner join both multiplies the rows it keeps and deletes the rows it does not, so a total computed across it is wrong in two directions at once. order_items has no such gap between the two readings for the plain reason that every order has at least one line, so nothing is dropped and the conditional average is the unconditional one.

Two more gaps in that table are worth naming. The order_items row carries a mean and a maximum, and the distance between them is the point: the multiplier is a distribution rather than a constant, so the average is the right figure for a quarter's aggregate and the wrong figure for any single order, one of which is repeated dozens of times over. And the customers row records a multiplier of one because the join runs from the many side to the one side. Turn the query round — sum a customer-level column across order rows — and the same pair of tables fans out in the other direction. Direction is part of the fact, and the column records it for the direction the queries actually take.

The rule the checker enforces: if the grain of the result set is finer than the grain of the table owning a column, that column may not be aggregated with SUM, AVG, COUNT(*) or COUNT(col). COUNT(DISTINCT order_id) is permitted, because it is grain-aware by construction, as is any aggregate over a subquery that has already collapsed to the coarser grain. The exemption for COUNT(DISTINCT order_id) is not a special case bolted on; it falls out of the mechanism. Repetition creates copies of an existing key value and never a new one, so the set of distinct order identifiers in the joined result is the same set as in the orders table, and counting a set is unaffected by how many times each member was handed over.

from sqlglot import exp

MULTIPLYING_AGGS = (exp.Sum, exp.Avg, exp.Count, exp.Min, exp.Max)

def result_grain(tree, catalog) -> set[str]:
    """Union of the grains of every table joined on an incomplete key."""
    base = tree.find(exp.From).this
    grain = set(catalog[qualified(base)].grain)
    for join in tree.find_all(exp.Join):
        table = qualified(join.this)
        keys = {c.name for c in join.args["on"].find_all(exp.Column)}
        table_grain = set(catalog[table].grain)
        if not table_grain <= keys:            # join key misses part of the grain
            grain |= table_grain
    return grain

def check_grain(tree, catalog) -> list[str]:
    grain = result_grain(tree, catalog)
    problems = []
    for agg in tree.find_all(*MULTIPLYING_AGGS):
        if isinstance(agg, exp.Count) and agg.args.get("distinct"):
            continue
        for col in agg.find_all(exp.Column):
            owner = catalog.owner_of(col, tree)
            owner_grain = set(catalog[owner].grain)
            if owner_grain < grain:            # strictly coarser than the result
                problems.append(
                    f"{agg.sql()} aggregates {owner}.{col.name}, defined at grain "
                    f"{sorted(owner_grain)}, over rows at grain {sorted(grain)}"
                )
    return problems

The check is static: it reads the AST and the catalogue, needs no database round trip, and runs in about 3ms, which means it belongs in the pipeline immediately after the structural validation of chapter 3 and before execution. Running it before execution rather than after is what makes repair possible — the message it emits is precise enough to feed back to the generator, and in this system a single repair attempt with that message attached rewrote the query correctly 78% of the time, usually by dropping the unnecessary join.

The failure message is also the answer the user should see when repair does not work: naming the column, the two grains and the join responsible is a diagnosis, where "I could not answer that" is an invitation to ask the same question again in different words.

Grain is a claim, so test it nightly#

The whole check rests on the catalogue's grain declarations being true, and a declaration that nobody verifies drifts. A table declared at [order_id] that acquires a second row per order after an upstream change — a correction row, a currency restatement, a late amendment — turns the verifier from a check into a source of false confidence, because it will now approve exactly the aggregation that has become wrong.

-- one assertion per declared grain, run after each load
SELECT 'analytics_marts.orders' AS table_name,
       COUNT(*) AS rows,
       COUNT(*) - COUNT(DISTINCT order_id) AS grain_violations
FROM analytics_marts.orders
HAVING COUNT(*) <> COUNT(DISTINCT order_id);

That subtraction is the same identity as the one derived above, with every order total set to one. COUNT(*) counts rows, COUNT(DISTINCT order_id) counts orders, and their difference is the sum over orders of one fewer than the number of rows carrying that order — surplus copies and nothing else. It is zero exactly when every order occupies exactly one row, which is what the declared grain asserts. So the assertion is not an approximation of the grain claim; it computes the claim's violation count directly, which is why a single row of output is enough to act on.

The assertion returns no rows when the grain holds and one row naming the table when it does not, which fits the shape of every data-quality framework and takes about 90 seconds across 220 mart tables. Failing the load on a grain violation is stricter than most teams want; failing the verifier open on that table — refusing to approve aggregations over it until someone looks — is the version that gets accepted, and it is the correct behaviour anyway. Those two are worth keeping apart. Detecting the violation is the requirement. Which of the two ways of responding to it gets wired up is a decision about who absorbs the cost, and a team that never separates them ends up arguing about the check when it disagrees about the consequence.

Reproduce the bug before you patch the checker#

The workflow that keeps this from decaying is to hold a corpus of wrong-but-valid queries with their gold counterparts, and to add to it before touching the verifier. Each entry is the question, the query that was generated, the number it returned, the correct number, and a one-line statement of the class.

{
  "id": "fanout-001",
  "question": "total revenue from enterprise customers last quarter",
  "bad_sql": "SELECT SUM(o.order_total) FROM orders o JOIN order_items i ...",
  "returned": 13108422,
  "expected": 4096382,
  "class": "grain: order-level column summed over line-item rows",
  "verifier_should": "reject"
}

Two things about that entry are worth reading rather than skimming. Divide the returned figure by the expected one and the quotient is the multiplier from the top of this chapter, agreeing not to two figures but to as many as the two integers can carry. That is a third instrument landing on the same number, and it licenses a narrow claim: the entry is a faithful record of one event rather than a later re-measurement that happened to come out near it. And both figures are euros, matching the headline totals rather than the cents that order_total holds, while the bad_sql field is abbreviated with an ellipsis and has lost the division that converts them. Replaying that string literally returns a figure a hundred times larger than the one stored beside it, which is a property of the record and not of the bug it records.

Twenty-two entries covering four classes is a useful corpus, and the four classes here were fan-out on detail tables, fan-out through an incomplete composite key on fx_daily, ambiguous date columns (order_date versus fulfilled_at versus invoice_date, which differ by up to 11 days at quarter boundaries and produced a 2.8% discrepancy that survived three rounds of review), and a metric defined differently by two teams. The grain checker addresses the first two. The third needs a different check. The fourth is not a verifier problem at all.

Running the corpus on every change to the verifier is what stops a later loosening — added because something was rejected during a demo — from reopening a bug that was fixed in March. On the 300-question evaluation set, the checker rejected 22 queries, of which 19 were genuinely wrong.

The other shapes that multiply#

Detail tables are the obvious fan-out and not the most common one in practice. Three others produce the same arithmetic with less visible cause.

A slowly-changing type-2 dimension keeps one row per entity per validity interval, so dim_customers_scd at grain [customer_id, valid_from] multiplies any join on customer_id alone by however many times that customer's attributes changed. A customer who moved country twice and upgraded plan once appears four times, and a revenue sum joined to it for a country breakdown is 4x for that customer and 1x for everyone else, which produces a discrepancy that is neither a round number nor a stable ratio and resists diagnosis for days. The correct join carries the interval predicate — AND o.order_date >= c.valid_from AND o.order_date < c.valid_to — and the grain checker catches its absence for the same reason it catches the missing line_number.

The reason that case resists diagnosis is worth stating in terms of the ratio derived earlier. A fan-out on a detail table multiplies almost every parent row, so the value-weighted average of the line counts is close to the table-wide average and the ratio is stable enough to recognise. A type-2 dimension multiplies only the entities whose attributes happened to change, so the value-weighted average depends on whether those particular customers were big spenders in the period asked about. Ask about a different quarter and the ratio moves. A stable ratio is a clue; the absence of one removes the clue without removing the bug.

A bridge table resolving a many-to-many — accounts to contracts, orders to promotions — multiplies on both sides simultaneously, so a sum over either fact is wrong by the mean cardinality of the other. A date spine joined to fill gaps in a time series multiplies by the number of dates when the join condition is on month rather than on the full key. In every case the shape is identical: the join key does not cover the grain of the thing being joined, so rows repeat, and any aggregate over a column belonging to the coarser side is inflated.

That uniformity is the argument for writing the check against grain rather than against the fan-out pattern you happened to see. The four cases would otherwise be four separate rules, three of which get written after an incident.

When it rejects work that was correct#

The other three were the beginning of a bigger number. Two weeks after shipping, analysts reviewing the rejection log confirmed that 9% of rejected-and-resubmitted queries had been correct. The wrong response is to relax the rule, because a verifier that passes everything has the same value as no verifier and costs more to maintain. The right response is to find out why it was wrong, which in this case had three distinguishable causes.

The middle column adds up to the figure in its own header with nothing left over, and a column that closes exactly is making a claim about the classification rather than about the causes. It says the three rows partition the reviewed rejections: every one was assigned, none was assigned twice, and there is no unexplained remainder sitting outside the table. That is what licenses reading the shares as priorities. Where the exactness stops is the denominator. These are rejected-and-resubmitted queries, so an analyst who read a rejection, agreed with it and moved on never entered the sample at all, while an analyst convinced the query was fine is exactly the one who resubmits. The sample is enriched for disagreement, so 9% is an upper bound on the false rejection rate across all rejections rather than an estimate of it.

The same caution applies in the other direction to the evaluation figures above. Three of 22 rejections on a fixed 300-question set is a larger share than 9%, and the two do not contradict each other, because they count different things: one is every rejection on a curated set, the other is the subset of production rejections somebody bothered to challenge. Reading either as the system's error rate requires saying which population is meant.

Most of that 9% was the catalogue being wrong rather than the check being wrong, which is a useful thing to discover, because the same incorrect grain declaration was also making the table cards misleading. The second cause is a real gap in the implementation above: a subquery that groups to order level and is then joined produces rows at order grain, and a checker that assumes the physical table's grain will reject a correct query written the careful way. Fixing it means computing grain recursively over the tree rather than looking it up per table.

The last percentage point is the residue that every verifier has, and it is handled by changing the failure mode rather than the rule. Below a confidence threshold the system answers, attaches a plain sentence — "this result joins to order_items, so orders with multiple lines are counted more than once; if you wanted order-level revenue, ask again without a per-item breakdown" — and shows the SQL. That is verifier-first design from topic 5.7, where a check's verdict is carried into the answer instead of gating it, arriving at its normal endpoint: the check's output becomes part of the answer rather than a gate that either opens or slams.

A grain check is a static check over structure, so it has no view of the data. It reads a parse tree and a catalogue, and both describe shapes rather than values. A query that filters order_date >= '2025-04-01' when the fiscal quarter began on 1 February is structurally impeccable, because every column exists, every join covers its grain and every aggregate sits at the right level; the only thing wrong with it is a date, and a date is a value. The same blindness covers a filter on a status value that no row holds. That query passes every rule here and returns nothing at all.

An empty result is not a number that can be wrong, which is precisely why it gets treated as an answer. Every instrument in this chapter compares the shape of a statement against a description of the tables it touches, and none of them looks at what came back. That boundary is deliberate and it is what buys the millisecond cost and the placement before execution, at the price of leaving every failure whose evidence lives in the returned rows to a later check.