Deduplication is the process of identifying records that represent the same real-world entity or event and then removing, merging or linking them so that each is counted only once. In data processing, it is a core step of data cleaning and a precondition for correct counts, sums, joins and machine learning features.
Duplicates arise from repeated loads, delivery retries and manual re-entry, as well as system migrations and merged sources. Some are exact copies, but many are near-duplicates that differ in spelling, formatting or missing fields, which makes deduplication a matching problem rather than a simple filter.
How deduplication works
A deduplication process has three stages. First, records are standardized: whitespace is trimmed, case is normalized, phone numbers and addresses are formatted consistently and dates are parsed. Second, candidate pairs are compared using exact or fuzzy rules. Third, matched records are resolved by deleting copies or merging them into a single surviving record.
Comparing every record with every other record grows quadratically, so a table of one million records would require roughly 500 billion comparisons. Blocking limits comparisons to records that share a key, such as postal code or the first letters of a surname, and reduces the work by several orders of magnitude.
| Stage | Purpose | Typical techniques |
|---|---|---|
| Standardization | Make equal values look equal | Trimming, case folding, format rules |
| Blocking | Limit candidate pairs | Shared keys, sorted neighborhoods, hashing |
| Comparison | Score similarity of pairs | Exact match, edit distance, phonetic codes |
| Classification | Decide match or non-match | Thresholds, rules, trained models |
| Resolution | Produce clean output | Delete, merge with survivorship rules, link |
Exact and fuzzy deduplication
Exact deduplication treats records as duplicates only when the chosen key or the entire row is identical. It is fast, deterministic and implemented with DISTINCT, GROUP BY, window functions such as ROW_NUMBER() or hash comparisons. It is the right tool for technical duplicates created by pipelines.
Fuzzy deduplication, also called record linkage or entity resolution, scores similarity between fields and accepts pairs above a threshold. Probabilistic methods, notably the Fellegi-Sunter model, weight each field by how strongly agreement indicates a true match.
| Method | How it decides | Strength | Weakness |
|---|---|---|---|
| Exact key match | Identical key values | Fast and predictable | Misses spelling variants |
| Full-row hash | Identical hash of all fields | Catches repeated loads | Any small difference breaks it |
| Rule-based fuzzy match | Similarity rules per field | Transparent decisions | Rules need careful tuning |
| Probabilistic linkage | Weighted field agreement | Handles noisy data well | Requires parameter estimation |
| Machine learning matching | Trained classifier on labeled pairs | High accuracy on complex data | Needs labeled examples |
Similarity measures and thresholds
Fuzzy matching depends on field-appropriate similarity measures. Choosing the wrong measure for a field type is a common cause of both missed duplicates and false merges.
| Measure | Suited for | Example behavior |
|---|---|---|
| Levenshtein distance | Short strings with typos | Smith and Smyth differ by one edit |
| Jaro-Winkler similarity | Personal names | Rewards matching prefixes |
| Phonetic codes | Names that sound alike | Soundex groups similar pronunciations |
| Jaccard on tokens | Addresses, product titles | Ignores word order |
| Numeric tolerance | Amounts, coordinates | Accepts differences within a set range |
The threshold controls the main trade-off. A strict threshold yields high precision but leaves duplicates behind, while a loose threshold raises recall but risks merging distinct people or products. Many systems use two thresholds, automatically merging clear matches and sending the uncertain band to human review.
Deduplication in pipelines and streams
In data pipelines, the most frequent duplicates are technical copies from retries and reprocessing. The robust defense is an idempotent design: each record carries a stable unique identifier, and loads use upserts or merge statements keyed on that identifier, so repeating a load does not create new rows.
Streaming systems with at-least-once delivery deduplicate by remembering recently seen event identifiers within a time window, for example several minutes to a few days. The window must exceed the maximum expected redelivery delay, but larger windows require more state memory.
Risks and common mistakes
Deduplication changes data irreversibly when copies are deleted, so caution and traceability matter. The table summarizes frequent problems.
| Problem | Consequence | Remedy |
|---|---|---|
| False merges | Two customers share one record | Stricter thresholds and review queues |
| Missed duplicates | Inflated counts and repeated mailings | Better standardization and blocking keys |
| No survivorship rules | Best values lost during merge | Define which source wins per field |
| Deleting without audit | Merges cannot be undone | Keep links from merged to surviving IDs |
| Dropping legitimate repeats | Real repeat purchases disappear | Deduplicate on a true event identifier |
Stripped to the essentials
Deduplication means finding the same thing written down more than once and keeping a single, correct version. A contact list might contain “Jon Smith” and “John Smith” with the same phone number. They are almost certainly the same person.
Cleaning up these doubles matters because counts and totals become wrong when something appears twice. A company would think it has more customers than it really does, or it would send the same letter to one person twice.
Where it is applied
A hospital network merges patient registries from three clinics into one system and finds about 410,000 records. Many patients visited more than one clinic and were registered separately, sometimes with misspelled names or old addresses.
The data team standardizes names, dates of birth and addresses, blocks candidates by date of birth and postal code, and scores pairs with Jaro-Winkler similarity on names plus exact matches on birth date. Pairs above a high threshold merge automatically, while a middle band of about 6,000 pairs goes to medical records staff for review.
The final registry contains about 352,000 unique patients. Every merge is logged with links to the original identifiers, so a wrong merge can be reversed, and clinicians now see one complete medical history per patient instead of fragments.