Before this: Find the row grain before removing duplicates
The idea
Entity resolution asks whether two records describe the same real-world thing. Exact, normalized identifiers (an email after trimming and lowercasing, a tax number) are strong evidence. Similar names are weak evidence: José Álvarez and Jose Alvarez may be one person, and two different people can share a name.
False merges are worse than missed ones. Merging two customers mixes their orders, addresses and consent, and it is hard to undo. When evidence is missing or weak, send the record to a review queue instead of guessing.
When records do match, a survivor rule decides which one is kept: usually the system of record first, then a deterministic tie-break such as the lowest id. The other records are kept as merged exceptions for audit.
Worked example
Input: crm c19 Ana López " Ana.Lopez@Example.org" / shop c07 Ana Lopez ana.lopez@example.org / shop c22 (no email)
- Visual
Trim email → lowercase → Blank to missing → Require a value (needs_review) → Remove duplicates on email, order by source asc, id asc (merged)
- SQL (DuckDB)
QUALIFY row_number() OVER (PARTITION BY email ORDER BY CASE source WHEN 'crm' THEN 0 ELSE 1 END, id) = 1
- pandas
known.sort_values(["email", "rank", "id"], kind="mergesort").duplicated("email")
Result: c19 survives (crm), c07 is merged, c22 goes to needs_review.
Common mistakes
- Merging on names or fuzzy similarity without review.
- Letting missing keys match each other.
- An unstated survivor rule that changes between runs.
Check yourself
Why not merge records whose names are similar?
Names are weak evidence: spellings vary and different people share names. A false merge mixes two customers' data.
What happens to all records with a missing email if you deduplicate without a review step?
They share the same (missing) key and collapse into one customer, which is a false merge.
Practice it
Merge customer records that share a normalized email; send records without an email to review.