A monthly report grouped on two key columns lost one of its rows this month though nobody changed the code — what happened?

## Why a row can leave without anything failing When rows are grouped on two keys, the identity of a group is the **pair** of key values carried by the same row. The grouped operation forms a group for each distinct pair it encounters and emits one result row per group. A pair that no row carried was never formed, so no row was emitted for it — and because the emission is driven entirely by what occurred, the result's row count moves with the data from run to run. That is the whole mechanism, and its consequence is that a report which is stable for a year can change shape in a month with no code change, no configuration change and no error. The vanished pair is the one that mattered precisely because nothing announced it: a category with rows is visible, a category with a zero is visible, and a category that produced no rows is not on the page at all. ## Two causes, one symptom Before reaching for a repair, separate the two situations that look identical from the output: 1. **The activity genuinely stopped.** No record this month carried that pair of values. The data is correct; the report is simply silent about a real, newsworthy fact — that a pair which used to be busy is now empty. This is the case the business most wants to hear about, and it is the one the report is worst at telling them. 2. **The rows exist but one part of the key has no value.** The records were produced, but a field that forms half the key came through empty, so those rows carry no complete pair. Depending on the tool's behaviour they either arrive as a group whose identity is partly absent, or take no part in the computation at all. Here the data is wrong upstream, and the missing row is a symptom of that rather than of anything in the business. The cheap way to tell them apart is the arithmetic you already have: compare the number of input rows against the rows accounted for by the groups. If they agree, every row reached a group and cause 1 is the live one. If they fall short, some rows never got a key and cause 2 is in play. ## Making the gap explicit instead of silent The mechanism that turns the silence into a visible row belongs to the key columns, not to the report. If each key column carries a **declared set of allowed values** — a list attached to the column of every value it may take — and the grouped operation is asked to emit the unobserved members, then a pair with no activity comes back as a real row whose group has no rows in it. The report keeps a fixed shape month to month, and "no activity" becomes something a reader can see instead of something they must notice is not there. Two conditions, both required, and designs differ on both: some emit every declared combination, some emit only what occurred even with the declaration present, and some carry no notion of a declared set at all. State the assumption rather than assert the behaviour. ## What the complete shape costs This is not a free improvement, and a senior answer says so: - **The row count becomes the product of the two declared sets.** Fifty of one and two hundred of the other is ten thousand rows, most of which may be empty; the result can dwarf a table whose occurring pairs numbered a few hundred. - **Most of the emitted rows carry nothing.** The reader now has to find the informative rows among the empty ones, which is its own kind of hidden. - **The declared sets must be maintained.** A value that starts occurring but is not in the declaration is a new problem: it either fails, or it is quietly not emitted, depending on the design. - **Empty rows need readable aggregates.** A pair with no rows has a group size of zero; any other reduction over it is either absent or a fold's identity, so the report must be prepared to display that honestly rather than as a measured value. Because of the cost, the usual sensible position is not "always emit the complete shape" but "emit it where the shape is the contract" — a fixed set of regions by a fixed set of product lines, where a consumer reads positions or compares two runs side by side, is worth the completeness; an open-ended key with thousands of values is not. ## What a strong answer covers Name the mechanism first: identity is the pair, only occurring pairs are emitted, the shape follows the data. Then separate the two causes and say how you would tell them apart with the row arithmetic you already have. Then offer the declared-set repair with its cost stated, rather than presenting it as a free fix. Finally, treat the vanished pair as information: a pair that stopped is usually the single most interesting line the report could have carried this month, and the version of the report that shows it as an explicit empty row is more useful than the one that quietly shrinks.