The same customer exists three times: how to find, merge and stop duplicate records

Two or three records for the same customer is rarely a data entry problem. It is the predictable result of an application where creating a new record is the path of least resistance. The cleanup itself breaks into three separate jobs, and confusing them is the most expensive mistake in these projects: first you produce a comparable key (normalization), then you decide which pairs of records are even worth comparing and score their similarity, and finally you decide which value survives and where the old identifier goes. None of those three is where the real win is. Clean up 4,000 duplicates today without changing how records get created, and you will run the same count again in six months.

The bill does not arrive as disk usage

Duplicate records cost nothing in storage. They cost in the places nobody connects back to the database. One company with two account records usually means one on 30 day terms and one on prepayment, and whichever a salesperson opens becomes the deal. The credit limit is effectively doubled, because each record carries its own. Statements come out short. In support, an agent sees half the history and asks the customer whether they have raised this before. In reporting, customer counts inflate and revenue per customer drops with no explanation anyone can give. When every report gives a different number, duplicates are almost always one leg of it.

The figure that circulates most here is the $12.9 million a year from Gartner's 2020 data quality research. Know where it comes from before you quote it: it is the self-reported estimate of 154 reference customers of data quality vendors, which is to say large organizations that had already decided they had the problem and gone shopping. It is not your number. Your number comes from running the query at the end of this post.

There are two different problems here, with two different fixes

The first is technical twinning. One real event got processed twice: a retried webhook, a double-clicked save button, an import file uploaded a second time. The two records are usually identical field for field. The fix is not matching, it is idempotent processing plus a uniqueness constraint in the database. We covered that side in data synchronization between systems.

The second is semantic duplication. Two different people, at two different times, wrote the same real world entity down differently. "Acme Food Trading Ltd", "Acme Food Ltd." and "ACME FOOD TRADING LIMITED" are one company, and no string comparison tells you that on its own. This is the actual matching problem.

Separate the two before you build anything. Standing up a fuzzy matching pipeline to solve the first problem is mopping the floor with the door open.

Make both records speak the same language first

This is the cheap part of matching and the part most often skipped. Leading and trailing whitespace, double spaces, punctuation, and legal suffixes (Ltd, Ltd., Limited, Inc, GmbH, A.Ş.) are noise for comparison purposes. Store the cleaned value in its own key column and leave the original untouched, because the original is what you show the user and what goes on documents.

Unicode has a trap waiting here. Two strings that look identical can be built from different code points, so normalization has to happen before comparison. In PostgreSQL, normalize(text, NFC) does this; NFC is the default form and the function only works when the server encoding is UTF8. The same page notes that checking with IS NORMALIZED is often faster than normalizing strings that are already normalized.

Case folding is worse than it looks, because it depends on locale. Unicode's SpecialCasing.txt maps "İ" (U+0130) to two code points in the language-neutral lowercase mapping, 0069 0307, that is "i" plus a combining dot above. Under the Turkish (tr) and Azerbaijani (az) locales the same letter folds to a plain "i", and "I" folds to "ı". Lowercase the same name on two servers with different locale settings and you get two different keys that render identically on screen. Microsoft's string comparison guidance calls this the Turkish-I problem outright, with a blunt example: under the Turkish culture, asking whether "Bill" starts with "BILL" returns False.

The rule that follows is simple. Do not build a matching key with a locale-dependent lower() call. Define the mapping yourself, explicitly, write down what you do with I and İ, store the result in a generated column and index it. Apply the same explicit treatment to accent folding, so that ş and s, ü and u, ç and c land in the same bucket. Any dataset with German, Turkish, Nordic or Central European names needs this long before it needs clever algorithms.

If you have a reliable key, the job is already done

Tax or company registration number, email, phone and web domain are the four candidates. Each has its own trap.

Registration numbers are rarely one format. In Turkey, for example, legal entities carry a 10 digit VKN while individuals use their 11 digit national ID as the tax number, so one column holds two formats with two different check digit algorithms. Whatever your country, store these as text rather than integers. Every registration number stored as a number eventually loses its leading zero, and the day you notice is the day matching silently breaks.

For phone numbers the target is E.164: a plus sign, a country code, 15 digits maximum in total. "0532 111 22 33", "+90 532 111 2233" and "905321112233" all have to collapse to one value. Use a library, do not write the regex yourself. One warning about the popular shortcut of comparing the last seven digits: it starts producing collisions as soon as you have more than one country in the data, and it merges switchboard numbers that differ only by extension.

On email, RFC 5321 section 2.4 is explicit: the local-part of a mailbox must be treated as case sensitive, while mailbox domains follow DNS rules and are therefore not case sensitive. The same paragraph adds that exploiting local-part case sensitivity impedes interoperability and is discouraged. In practice: always lowercase the domain, lowercase the local-part too but know that you are making an assumption. Do not generalize the dot-stripping rule, either. Google ignores dots only for @gmail.com addresses; on organizational domains, including Workspace, dots change the address.

Then there is the shared mailbox trap. In smaller companies three people share info@. HubSpot deduplicates contacts by email address and companies by domain name, so a team that puts a shared mailbox on a person record quietly collapses three people into one. Think that scenario through before you make email a unique key on the contact table.

No key? Generate candidates before you compare

The obvious approach is comparing every record with every other record, and it dies on the first run. Comparison count grows with n(n-1)/2. The example in the Splink blocking guide is the one to quote in planning meetings: a dataset of 1 million input records generates around 500 billion pairwise comparisons.

Blocking is the answer. A cheap rule narrows the candidate pool, and the expensive similarity calculation only runs inside that pool. Rules that work in practice: same city plus the first three letters of the company name, the first four digits of the registration number, the last seven digits of the phone, the email domain. Any single block misses something, so run several blocking rules and union the results. The guidance in the same documentation points the same way: a long list of strict blocking rules beats a short list of loose ones.

Phonetic matching stops working the moment you leave English

PostgreSQL ships two extensions for this, and the choice between them is clear on non-English data.

pg_trgm splits text into three character groups and scores similarity between 0 and 1. The docs give the default threshold as pg_trgm.similarity_threshold = 0.3, which is also what the % operator uses. The part that matters operationally is GIN and GiST index support, because an unindexed similarity query is unusable on a table of any size.

fuzzystrmatch offers levenshtein, soundex, metaphone and daitch_mokotoff, and its own documentation makes the decision for you: the soundex, metaphone, dmetaphone and dmetaphone_alt functions do not work well with multibyte encodings such as UTF-8, and you should use daitch_mokotoff or levenshtein with that data. Soundex carries a second caveat, that it is not very useful for non-English names. So on an international customer list, close the phonetic route early. What remains works well: trigram similarity over accent-folded text, and levenshtein on short fields, keeping its 255 character limit in mind for long legal names.

Packaged CRM rules inherit the same limitation. Salesforce's Fuzzy: Last Name method combines Exact, Keyboard Distance and Metaphone 3 at a threshold of 90, and Fuzzy: Company Name uses Acronym, Exact and Syllable Alignment at 70. Those defaults were tuned on English-language data. Measure them against your own records before you trust them.

Do not score on one field at a time

The maths behind matching was settled in 1969 by Ivan Fellegi and Alan Sunter, and most of today's tools still implement that model. The idea is approachable. For each field you estimate two probabilities: how often the field agrees when two records really are the same entity (m), and how often it agrees by coincidence when they are not (u).

The numbers in Splink's explanation make it concrete: m for date of birth is around 0.98, allowing roughly a two percent chance of a data error even within a true match, while u for surname is around 0.005, because two unrelated people rarely share a surname. Each field's contribution becomes a weight of log₂(m/u), and the weights add up. On the same scale, a total weight of 2 means a match probability of 0.8, and a weight of 7 means 0.99.

Translated into everyday terms: agreeing on city tells you almost nothing, agreeing on a registration number is decisive on its own, and a similar company name plus the same phone number is worth more than either alone. Do not write a scoring function that averages fields with equal weight.

Then use two thresholds rather than one. Above the upper threshold records merge automatically, the band in between goes to a human review queue, and below it nothing happens. The width of that middle band is set by how many pairs your team can actually review in a day, not by a statistic.

A wrong merge costs more than a duplicate

Put that sentence in the project brief. Merge the accounts of two genuinely different companies and their balances, open orders, contracts and support history interleave. Separating them again is far harder than merging was, and in most systems it is not possible at all.

So precision comes before recall. Keep the automatic threshold high, keep the review band wide, and accept leaving some duplicates in place. Answer one question up front: can we undo this merge? If the answer is no, do not merge automatically at all. Route it through a person.

Deciding which value survives

This is the real decision in a merge screen, and it is a business decision rather than a technical one. Set source precedence per field: legal name and tax details from the accounting system, delivery address from the ERP, email from whichever record was verified most recently.

Two rules hold in nearly every project. An empty value never overwrites a populated one. And "last updated wins" sounds fair but is wrong, because a blank field written by an overnight integration is technically the most recent update. Look at the source, not the timestamp.

Leave historical records alone. The company name printed on an issued invoice is the name as of that date and should not change because two records merged last week. If the data model never separated the current entity from its historical snapshots, expect the merge work to take considerably longer than estimated. Record the decision as well: which records, which rule, who approved it, when, and what the previous values were. A bulk merge without an audit trail turns into data nobody can explain six months later.

Tombstone, do not delete

Deleting the losing record is the easiest option and the most expensive one. That identifier lives outside your database too: in a marketplace integration, in the accounting system's mapping table, at the shipping provider, in a link someone emailed last week.

Keep the row and turn it into a tombstone. A merged_into_id column points at the surviving record, the status says merged, and API requests arriving with the old identifier get redirected to the new record or at least return the current one. It is the same advice as keeping legacy identifiers when migrating data from an old system.

The tools behave this way too. In Salesforce, the merge operation handles at most three records at a time, reparents related records to the surviving one and deletes the rest. The limit of three is not arbitrary. Making an irreversible operation awkward to run in bulk is a deliberate design choice.

The real work is preventing the next duplicate

Cleanup is a project. Prevention is a product feature, and it lives in four places.

In the database: a generated column holding the normalized key, with a unique index on it. Code that only checks in the application layer loses the race when two requests arrive at the same moment. Let the database enforce it.

In the interface: stop making "New customer" the first step. Search first, create only when the search comes up empty. The search box has to tolerate typos, otherwise users fail to find what exists and create a new record instead, which is one reason search relevance matters more than it looks.

In imports: require an external identifier column and make the load an upsert rather than an insert.

In integrations: every system carries the other system's identifier, and the mapping lives in one place. An integration that does not store the counterpart's record ID will eventually create a fresh record on every sync.

The first step that fits in this week

Write three queries, one line each. Group by the normalized company name, by the last nine digits of the phone number, and by email address, then count the groups with more than one record. Those three numbers replace the team's "we have a bit of a duplicate problem" with something real.

Next, pick 20 pairs at random from that output and look at them by hand. How many are genuinely the same customer? That ratio is your first precision estimate, and it is the only solid number you will have when the threshold conversation starts. The same 20 pairs will also write your merge rules for you, because arguing about which source to trust is much faster with concrete examples on the table.

Third, put a unique index on your clearest key. For most companies that is the registration number. The index will fail on the first attempt because conflicting rows already exist, and that error output is page one of your cleanup queue. Walking into a conversation with those three counts and a reviewed sample gets you to a decision far faster than starting with "we have duplicate records".