You recovered the 140 lost records — why is grouping them by source or day more useful than their count, and what could mislead you?

## Size is one question; composition is the useful one Once the missing records exist as rows rather than as the number 140, you can ask the question that actually decides what happens next: is this loss **systematic** or **scattered**? - **Systematic** — the residue is entirely or overwhelmingly one source, one day, one region, one ingestion batch, one file, one format version. Something about that slice is different, the blast radius is known, and the repair is usually to fix or re-process that slice. - **Scattered** — the residue is spread across every slice in roughly the proportions the input has. Then the cause is a property of individual records, and the repair is to find what those records have in common at the value level rather than at the batch level. These two findings lead to completely different afternoons, and a count cannot tell them apart. This is the whole reason the previous step recovered rows instead of reporting a difference. ## Group the residue by every candidate Grouping the rows you lost by some column — tallying how many of the residue fall into each value of that column — is cheap, and the residue is small, so do it for every column that could carry a systematic cause rather than for the one you suspect: - the source system, feed or file the records arrived in; - the calendar day, and separately the hour, since a boundary effect concentrates at an edge; - the region, tenant, account type or any other partitioning attribute; - the batch or run identifier, if the input was assembled from several; - a version or schema marker, if the input carries one; - whether a particular column is absent, which is an ordinary two-value grouping and often the sharpest signal of all. The column whose tally concentrates is the lead. The ones that stay flat are evidence too — they eliminate hypotheses cheaply. ## The base-rate trap, which is what makes this a senior question The residue's tallies are meaningless on their own. A random loss reproduces the input's composition, so the largest group in the input will be the largest group in the residue whether or not it has anything to do with the defect. The comparison that carries information is each group's **share of the residue against its share of the step's input**: | Group | Share of input | Share of residue | Reading | |---|---|---|---| | Source A | 80% | 79% | Exonerated — the loss tracks the base rate | | Source B | 5% | 61% | Strongly implicated — twelve times its share | | Day 14 | 3% | 3% | Nothing here | | Region with a column absent | 2% | 38% | Implicated, and suggests what to look at in the records | A useful habit is to compute the ratio directly — share of residue divided by share of input — so the number you read is "this group is over-represented ten to one" rather than a raw tally that flatters whatever is biggest. ## Groupings that cannot carry the signal Not every column is a candidate, and grouping by the wrong one wastes the pass: - a **near-unique** column (the record identifier, a timestamp to the millisecond) produces 140 groups of one and tells you nothing; - **free text** behaves the same way unless you bucket it first; - a column the step itself **derives** may say more about the step than about the input; - a column with a single value across the whole input carries no variation to detect. Aim for columns with a handful to a few dozen distinct values — enough to separate slices, few enough that concentration is visible. ## Small residues and honest conclusions With 140 records, a group holding 3 of them supports no conclusion at all. Concentration is only interesting when it is large relative to both the residue and the group's base rate. Say so explicitly: "61% of the residue is source B, which is 5% of the input" is a finding; "source B appears in the residue" is not. And where the evidence is genuinely weak, the honest output is that the loss looks scattered, which is itself a direction — it sends the next question at the records rather than at the batches. ## What this pass does not do Characterising the residue narrows the cause; it does not identify it. Knowing the loss is entirely one day tells you where to look, and the mechanism that removed those records is still a property of whatever the step does. Hand over the characterisation with the residue attached, and let the step's own behaviour supply the explanation.