Before this: Quarantine with reasons, in rule order, Find the row grain before removing duplicates
The idea
A take-home cleaning task, or a real handoff, is judged on more than the clean table. Reviewers want to see the rules you applied, the rows you rejected with a reason for each, and proof that nothing disappeared.
Reconciliation is that proof: input rows = clean rows + quarantined rows + rows filtered as out of scope. If the numbers don't add up, a step dropped or duplicated rows somewhere.
Finish with the trade-offs you made and the questions that remain open: for example, "timestamps without an offset were quarantined, not assumed to be UTC; the source team should confirm the zone". Interviewers ask you to defend exactly these choices.
Worked example
Input: 43 order lines
- Visual
Change summary tab: the reconciliation line, e.g. "43 input rows = 20 remaining + 23 quarantined ✓" for the reference rules on the published dataset
- SQL (DuckDB)
SELECT (SELECT count(*) FROM orders) AS input, (SELECT count(*) FROM clean) AS clean, (SELECT count(*) FROM exceptions) AS exceptions
- pandas
assert len(orders) == len(out["clean"]) + len(out["exceptions"])
Result: Every input row is either in clean or in exceptions with exactly one reason.
Common mistakes
- Delivering a clean table without the exceptions.
- Rows that vanish without a reason.
- Not stating the assumptions you made.
Check yourself
What does reconciliation prove?
That every input row is accounted for: clean, quarantined with a reason, or filtered as out of scope.
Why list open questions in a handoff?
Rules you had to assume should be confirmed by the data owner; stating them prevents silent wrong assumptions.
Practice it
Solve the order-line export and write a three-line handoff: rules applied, exception counts by reason, open questions.