Before this: Quarantine with reasons, in rule order
The idea
An inner join keeps only matched rows. Payments for unknown accounts disappear, the total shrinks, and nobody notices.
Use a left join from the table you must keep, then split: matched rows go to the clean output, unmatched rows to exceptions with a reason such as missing_account or unknown_account. Input rows then equal clean plus exceptions.
Check the master key is unique first, or the join can multiply rows instead.
Worked example
Input: payments: P1→A1, P2→A9 (not in accounts)
- Visual
Add step → Look up → table accounts → key account → no match: quarantine unknown_account
- SQL (DuckDB)
LEFT JOIN accounts a USING (account) ... CASE WHEN a.account IS NULL THEN 'unknown_account' END
- pandas
m = pay.merge(acc, on="account", how="left", indicator=True); orphans = m[m._merge == "left_only"]
Result: P1 clean; P2 in exceptions as unknown_account
Common mistakes
- Inner joins that hide orphans.
- Not checking master-key uniqueness.
- Inventing placeholder accounts.
Check yourself
How do you prove no payment was lost?
Payments in = clean payments + exception payments, in count and in total amount.
Practice it
Match payments to accounts with explicit exceptions.