Duplicate records occur when the same real-world entity appears more than once in a dataset. A customer listed twice under slightly different names, a transaction recorded in two systems, a location appearing with two different spellings — all are duplicates.
Why Duplicates Are Harmful
Duplicates inflate counts, skew analysis, and cause operational problems. If a customer appears twice in a mailing list, they receive two copies of every communication. If a product appears twice in an inventory system, stock counts are wrong.
Types of Duplicates
Exact duplicates — identical rows in every field. These are easy to detect and remove.
Near-duplicates — records that represent the same entity but differ in small ways. "John Smith, 123 Main St" and "Jon Smith, 123 Main Street" are near-duplicates. These require fuzzy matching to detect.
Finding Duplicates
For exact duplicates, sort the data and look for consecutive identical rows, or use a GROUP BY query to find rows with the same key fields appearing more than once.
For near-duplicates, techniques include:
- Phonetic matching (Soundex, Metaphone) for names
- Edit distance (Levenshtein distance) for strings
- Blocking — grouping records by a shared attribute before comparing
When you find duplicates, you need to decide which record to keep (or how to merge them). This is called deduplication or record linkage. The "golden record" is the merged, authoritative version that combines the best information from all duplicates.
Learn about Formatting Issues.