Why duplicates happen
Duplicates aren't a data-entry accident — they're the structural consequence of multiple systems. Why 'one customer, one row' requires actual engineering.
Harbor Lane has 320 customers and 460 customer records. Nobody made an error. The web platform mints web_ids; the POS mints pos_ids; each system is internally consistent and jointly oblivious. Duplication is not a hygiene problem — it's the default physics of having more than one system, which is to say, of being any company older than a year.
- Every system mints its own identity. CRM, e-commerce, POS, support desk, email platform: each has its own customer table and its own primary key. Five systems ≈ up to five records per human.
- Shared identifiers are unreliable on purpose. Email would be the natural join key — except it's optional at the register, people have several, families share one, and 'jsmith@gmail' vs 'j.smith@gmail' vs a typo are three different strings for one person.
- Humans enter data like humans. 'Elena Diaz', 'ELENA DIAZ', 'Elena Días'. Phones as
617-555-0142,6175550142,(617) 555-0142. All true. None equal. - Mergers multiply everything. Acquire a company, inherit their systems, double your duplicates in a day. Entity resolution is a permanent discipline, not a spring cleaning.
What duplication actually costs
- Every customer-level metric is wrong. Customer count inflated ~44% over the true count at Harbor Lane (460 records for 320 real people — about 30% of records are duplicates); repeat-purchase rate understated (the second purchase looks like a different person's first); lifetime value split across fragments. These aren't rounding errors — they change decisions.
- AI inherits and amplifies it. A support agent (the one built in Agentic AI Systems) looks up 'Elena Diaz' and finds the fragment without the order in question — then confidently acts on incomplete history. Personalization models treat one loyal customer as three lukewarm ones. RAG over customer notes retrieves half the story.
- Compliance breaks. A GDPR deletion request that removes two of three fragments is a violation, not a partial success. 'Find every record about this person' is entity resolution.
The fix has a name: entity resolution — deciding which records refer to the same real-world entity, and producing one golden record per entity plus a crosswalk from every source record to it. This module builds the full algorithm: normalize → block → match → cluster → survive. Five steps, each with real decisions in it.
The intern solution — JOIN ON email and call it done — is worse than nothing: it merges the 70% with emails, leaves the rest split, and reports the problem as solved. Partial resolution that claims completeness corrupts trust in every number downstream. Measure your match rate honestly; unresolved-but-known beats resolved-in-name-only.