skip to content

140 records vanished at one step — why is the difference of two counts not enough, and how do you recover the records?

level: middleimportance: should knowfreq 52%

answer

  1. a number cannot be grouped
  2. survivors out, residue remains
  3. the key must be unique and unchanged
  4. absent keys are their own pile
  5. never line the sides up by position

basics

~20 s

A difference of counts is a magnitude you cannot inspect or group. Recover the rows: keep the input rows whose key value is absent from the output, using a key unique in the input and unchanged by the step.

solid answer

~50 s

The number 140 cannot be looked at, grouped or shown to anyone, and it is net, so it may be hiding both departures and arrivals. What you want is the residue as rows. Pick a key that is unique in the step's input and that the step does not rewrite, collect the distinct key values present in the step's output, and keep the input rows whose key value is not among them. Then balance the arithmetic — surviving keys plus recovered rows should account for the input — so you know the residue is complete rather than merely plausible. Two traps: never pair the two sides up by position, because nothing promises row order across an operation; and pull aside rows whose key value is absent before you start, since a row with no key cannot be shown to have survived and will land in the residue by construction.

code

pseudocode · 17 lines
pseudocode
// precondition: key_of(row) is unique across input_rows
//               and the step does not rewrite it

no_key      = [ r in input_rows where key_of(r) is absent ]
traceable   = [ r in input_rows where key_of(r) is present ]

survivors   = distinct( key_of(r) for r in output_rows )
lost_rows   = [ r in traceable where key_of(r) not in survivors ]
new_keys    = [ k in survivors where k not in distinct(key_of(r) for r in traceable) ]

// balance over DISTINCT keys before trusting the residue
count(traceable) - count(lost_rows) + count(new_keys) == count(survivors)

// report, do not merge:
//   lost_rows  -> the residue to characterise
//   no_key     -> untraceable, counted separately
//   new_keys   -> rows the step produced, which a net count hid

go deeper

for a junior

The takeaway is that a missing-rows investigation ends with records you can read, not with a number. Getting there means keeping the input rows whose identifier does not appear in the output.

for a middle

Be able to state the conditions the key must meet — unique in the input, untouched by the step, present, and held the same way on both sides — and explain what a residue equal to the whole input is really telling you.

for a senior

Demonstrate the reconciliation habit: departures, arrivals and untraceable rows accounted for separately, so the residue is provably complete, and a marker carried through the step when the same investigation will recur.

for a principal

The judgment is how much traceability a pipeline should carry permanently — an identifier attached at the boundary and preserved through every step — against the column and the discipline it costs on every run.

## Why a difference of two counts is a dead end "140 rows went missing at step four" is a magnitude. You cannot group it, sort it, show it to the person who owns the source, or tell from it whether the loss is one bad input file or a property scattered through the whole dataset. It is also a **net** figure: if the step removed 200 rows and produced 60 more from elsewhere, the number you are staring at is 140 and the real departure count is 200. The investigation only becomes tractable when the residue exists as **rows**. ## Recovering the residue by exclusion The procedure is the same in every design: 1. **Choose a key.** One column, or a small set of columns, that identifies a record. It must be unique in the step's input and must survive the step unchanged. 2. **Collect the survivors.** Take the distinct key values present in the step's output. 3. **Keep what is not among them.** The input rows whose key value is not in that set are the residue — the actual records that left, with all their columns still attached. 4. **Balance the arithmetic** before trusting it, so you know you recovered all of them and not a subset. Step four is the one people skip and the one that catches a bad key. If the surviving keys plus the recovered rows do not account for the input, your key is not doing what you assumed. ## What the key has to satisfy | Requirement | Why | What goes wrong without it | |---|---|---| | Unique in the input | The residue is defined per record | Two input records share a key; one survives and both look like survivors | | Unchanged by the step | Survivors are recognised by their key | A step that rewrites or reformats the key makes every row look lost | | Present on every row | Absence cannot be matched | Rows with no key fall into the residue whether they left or not | | Stable in representation | The two sides are compared as values | The same identifier held as text on one side and as a number on the other never matches | The last row is worth dwelling on. If a step widened or narrowed how the key column is held, the two sides can carry the same identifier and still fail to line up, and the residue will come back suspiciously close to the whole input. A residue of nearly 100% is almost never a real loss; it is a broken key. ## Absent key values are a separate pile Rows whose key value is absent need handling before the exclusion, not after. How a comparison behaves when one side is an absent-value marker — whatever a tool puts in a cell where no value exists — genuinely differs between designs: in some, an absent value compares unequal to everything including another absent value; in others, a comparison involving absence yields absence rather than a verdict, so it is neither a match nor a non-match. Either way you cannot demonstrate that such a row survived. So: - separate the rows with an absent key **first**, and count them; - report them as their own line in the reconciliation — "31 rows carried no key and could not be traced"; - resist the temptation to invent a substitute key for them silently, which converts an honest unknown into a confident wrong answer. ## Position is not a key The shortcut everyone reaches for is to line the two sides up row by row as they come and look for the gaps. Do not. Nothing promises that rows come back in the same order across an operation: grouped results have an order several designs leave unspecified, partitioned or parallel execution combines results in whatever order they arrive, and a step may legitimately reorder. A positional comparison in any of those cases reports differences that are measuring order rather than loss. ## Cheaper: keep the record as the step runs Reconstructing the residue afterwards costs another pass and needs a good key. The alternative is to keep, or arrange to reconstruct, the record of which rows survived the step at the moment it runs — a per-row marker of which side or which branch a row came from, carried through the step and dropped once the check is done. It costs a column for the duration of one step and turns a forensic exercise into a selection. Some operations offer this directly; where they do not, adding a temporary marker column to the input before the step does the same job. ## What you have when you are done A set of complete records, not a number: something to group, to show to the owner of the input, and to characterise. Whether the loss is systematic or scattered is the next question, and it is only askable because the residue is now rows.

  • The residue comes back as almost the entire input. What is the most likely explanation?
    The key is broken rather than the data. Either the step rewrote or reformatted it, or the two sides hold it differently — the same identifier as text on one side and as a number on the other — so nothing matches and every input row looks lost. Check a handful of surviving records by hand before believing a residue that large.
  • Why count the rows the step produced as well as the rows it lost?
    Because the drop you are chasing is a net figure. A step can remove 200 records and generate 60, showing a fall of 140. Counting arrivals separately turns one ambiguous number into two unambiguous ones, and it is also what makes the reconciliation balance.
  • The records have no natural identifier at all. What do you do?
    Attach one before the step — a sequence number assigned to the input rows in the run that is under investigation — and carry it through. It is only meaningful within that run, which is enough, because the residue is only defined within that run. Do not derive a key by combining value columns that the step itself might change.

saying these in an interview costs you the question

  • Reports the size of the loss and calls the investigation finished.
  • Pairs the two sides up row by row, assuming order is preserved.
  • Picks a key the step itself rewrites, then declares everything lost.
  • Ignores records whose key value is absent instead of counting them apart.
  • Treats the net difference as the number of records that left.
  • Never reconciles, so a partially recovered residue passes as complete.