An element-wise subtraction between two tables of 50,000 rows each returns almost entirely absent values, though both hold the same records — why?

## Read the symptom precisely Two things are true at once and they look contradictory: the two tables are the same length and hold the same records, and yet the subtraction produced almost nothing. The contradiction dissolves as soon as you notice that **the operation never compared the records**. It compared identifiers. **Row labels** are the per-row identifier a tool carries beside the columns, present in some designs and absent entirely in others. **Label pairing** is the tool lining two operands up on that identifier before it computes anything. Where a label exists on one side only, the output cell holds the **absent-value marker** — the tool's representation of a value that is not there. So a result that is overwhelmingly absent values is not a statement about the numbers. It is a statement about the identifiers, and it says they disagree. ## What the symptom rules out | Hypothesis | Why the symptom does not fit it | |---|---| | The data really is missing | Both inputs were inspected and hold values; the holes were manufactured by the operation | | The numeric type is wrong | A type problem raises, or produces wrong numbers, rather than a near-total column of absent values | | The subtraction is the wrong way round | That inverts signs; it produces values, not holes | | The tables are different sizes | They are not — and equal size is precisely what made the bug invisible | ## What actually happened upstream The identifiers stopped corresponding because a step changed one side and not the other. The recurring shapes: - **A filter on one side.** The rows that survive keep the identifiers they already had rather than closing up, so the two sides now agree only on the rows the filter kept. - **A rebuild or re-read of one side.** Reading a table afresh assigns identifiers from scratch, so a side that was filtered earlier no longer lines up with a side that was not. - **Putting the rows in a different order and then renumbering.** Renumbering after a reorder is a new set of identifiers that happen to reuse the same values in different places — the worst case, because the overlap is total and the pairing is wrong for nearly every row. - **Two sources that never shared an identifier scheme at all**, combined on the assumption that both were simply counted from zero. Note the sharp difference between the last case and the first three. Where the label sets barely overlap you get holes and you find out. Where they overlap completely but mean different rows, you get a full result of confidently wrong numbers. ## How to diagnose it in one step 1. Count the labels the two sides have in common, and compare that with the row count of each side. This one number explains the symptom immediately; reading rows on screen will not, because the screen shows you a hole and says nothing about why. 2. Look at a handful of labels present on one side only. Their shape usually names the upstream step — a contiguous run points at a filter, an off-by-a-constant pattern points at a renumbering. 3. Walk back to the first step where the two sides were still derived from the same rows, and ask what changed after it. ## Two fixes, and which one to pick - **Discard the labels and pair by position.** This is correct only if position genuinely is the correspondence — both sides came out of the same ordered step over the same rows and nothing between reordered or filtered either one. Say which step guarantees that, and assert the two lengths are equal in the same breath. Reach for this when the answer is genuinely *these are two computed outputs of one pipeline over one row set*. - **Match the two tables on a key column.** Put the value that actually identifies a record — the account number, the reading identifier, the timestamp and sensor together — in a column on both sides and pair on it. This is the durable answer, because it survives any later filter, reorder or re-read on either side, and because the correspondence is now written in the code where a reviewer can see it. The tempting third option — filling the holes with zero and shipping — turns a visible failure into a silently wrong report, and is the answer that ends an interview. ## The same bug in a design without labels A tool that carries no identifiers at all pairs strictly by position, so this specific symptom cannot occur; instead the operation either refuses two operands of different lengths or, when the lengths happen to match, quietly subtracts the wrong rows from each other and returns a full result of plausible numbers. That is worse, not better. The lesson generalises across both designs: the number of rows on each side is not evidence that the rows correspond, and the only durable protection is to make the correspondence explicit rather than inherit whichever default the tool implements.