Before this: Merge on evidence, review the rest
The idea
Strong evidence is a shared, reliable identifier: a customer id, a verified email after normalization. Weak evidence is a similar name or the same city.
Merge automatically only on strong evidence. Route pairs with only weak evidence to review, and never merge records whose key is missing.
Record why each merge happened, so a wrong merge can be undone.
Worked example
Input: A: Ann Lee, ' ann@x.com' (crm); B: Ann Lee, 'ANN@x.com' (shop); C: Ann Lee, missing email
- Visual
Steps → Trim + lowercase email → Require email (needs_review) → Remove duplicates by email, crm first
- SQL (DuckDB)
QUALIFY ROW_NUMBER() OVER (PARTITION BY lower(trim(email)) ORDER BY source = 'shop', id) = 1
- pandas
df.assign(k=df.email.str.strip().str.lower()).sort_values(["source", "id"]).drop_duplicates("k")
Result: A and B merge (crm record kept); C goes to review
Common mistakes
- Merging on names.
- Matching missing keys.
- No record of why records were merged.
Check yourself
Do two records with missing emails match?
No; missing never matches missing. They go to review.
Practice it
Resolve customer records by normalized email.