Joins that lose rows, and how to prove they did not

1 · Learn the move · Fact-check list

Every bench dataset is really two tables - the measurements and the sample sheet - and the join between them is where rows go to die quietly. Keys fail for dull reasons: a trailing space, S1 versus S01, a well written B3 in one file and B03 in the other, an ID Excel turned into a date. An inner join drops every casualty without a word, and the analysis runs fine on the survivors. The discipline is a fact-check list of counts, stated every single time: rows in the left table, rows in the right, rows matched, unmatched-left with examples, unmatched-right with examples. The area's done-check is exactly this - counts stated and matched before and after every join. If unmatched is not zero, the next step is diagnosing the key mismatch, not proceeding; and the model can draft the diagnosis, but whether 3 lost rows is a bug or 3 genuinely absent samples is your call, made looking at the actual IDs.

Join [measurements table] to [sample sheet] on [key]. Before joining, report: rows and distinct keys in each table, and any duplicate keys. After joining, report the reconciliation: matched rows, unmatched-left (count + up to 10 example keys), unmatched-right (same). If either unmatched count is nonzero, stop and diagnose: compare unmatched keys across tables for whitespace, case, zero-padding, and format differences, and show likely pairs. Do not proceed to analysis on a join with unexplained losses.