Before this: Joins where missing keys never match
The idea
Deleting bad rows hides problems. Quarantining them, in a separate table with a reason, keeps the evidence and lets someone fix the source.
When rules are numbered, a row gets the FIRST reason that applies. A payment with a missing account is missing_account, even though it also has no matching account. Check the rules in order and stop at the first failure.
Reconcile at the end: clean rows + quarantined rows = input rows. If the numbers don't add up, a row was lost or duplicated.
Worked example
Input: payments.account: "1", "9" (unknown), missing
- Visual
Require a value (missing_account) → Look up name (no match → quarantine, unknown_account)
- SQL (DuckDB)
CASE WHEN account IS NULL THEN 'missing_account' WHEN a.account IS NULL THEN 'unknown_account' END- pandas
reason = reason.mask(df["account"].isna(), "missing_account") reason = reason.mask(reason.isna() & ~df["account"].isin(known), "unknown_account")
Result: 1 clean row, 2 exceptions with different reasons; 1 + 2 = 3
Common mistakes
- Dropping invalid rows.
- Assigning every applicable reason or the last one.
- Skipping the reconciliation check.
Check yourself
A row fails rules 1 and 2. Which reason?
Rule 1, the first one that applies.
What does reconciliation prove?
That no row was lost or counted twice.
Practice it
Quarantine missing and unknown accounts with the right reasons.