The definition, precisely
Deduplication takes a collection with repeats and returns one where each distinct item appears once. That definition hides the hard part, which is the word "distinct". Two rows are only duplicates relative to some rule about what makes them the same, and choosing that rule is the whole job. Deduping a list of email addresses is easy because the address is the identity. Deduping a list of people is not, because the same person can appear as three different strings.
Exact matching and fuzzy matching
Exact matching compares values character for character, usually after light normalization such as lowercasing and trimming whitespace. It is fast, deterministic, and never surprises you: half a million rows go through in well under a second. It also misses anything that differs at all, which in real data is a lot.
Fuzzy matching scores how similar two values are and calls them duplicates above a chosen threshold. That is what collapses "Smith, Jane", "jane smith", and "Jane Smyth" into one contact. The cost is that it can be wrong in both directions, so the threshold is a dial between missed duplicates and merged strangers, and the results deserve a look before you commit. Fuzzy comparison is pairwise, which makes it expensive at scale; on this site it runs server-side in the API.
Where deduplication shows up
- Mailing and marketing lists: the same subscriber signed up twice, so they get two of everything. Dedupe on the email column and the problem disappears. See the duplicate line remover for a plain pasted list.
- CRM records: the highest-value and messiest case, because a contact accumulates variants across imports, forms, and sales reps. Deduping CRM contacts walks through the export, clean, re-import workflow.
- Database rows: a duplicated row usually means a missing unique constraint. Deduping fixes today's data; the constraint stops tomorrow's.
- Storage and backups: this one is automatic and works on blocks of bytes rather than records. A deduplicating filesystem or backup product stores each unique block once and points at it from everywhere else, which is why a nightly full backup does not cost a full copy of the disk every night.
- Spreadsheets: the everyday version, covered in Excel, Google Sheets, and CSV files.
The real question: which copy do you keep?
Finding duplicates is the easy half. Deciding which one survives is where the data loss happens. Keeping the first occurrence is the safe default and what the tools on this site do, because it preserves order and is trivially predictable. Sometimes you want the newest record instead, or the most complete one, or a merge of the fields each copy filled in. Merging is a different and much riskier operation than removing, so it is worth doing deliberately, on a short list, after the removal pass has cut the problem down.
Dedupe is not the same as normalizing
Normalization makes values consistent without deleting anything: trimming stray spaces, unifying capitalization, standardizing phone numbers and country names. Dedupe removes records. The two are constantly confused, and the practical link between them is that normalizing first makes deduping dramatically more effective. Most pairs that exact matching fails to catch are identical except for formatting, so cleaning the format converts a hard fuzzy problem into an easy exact one.
FAQ
Frequently asked questions
What does dedupe mean?
Dedupe is short for deduplicate: to remove repeated entries from a set of data so that each thing appears once. It is used as a verb ("dedupe the list") and as a noun for the process itself. The full word deduplication means the same thing and shows up more in storage and backup contexts.
What is the difference between exact and fuzzy dedupe?
Exact dedupe treats two records as duplicates only when the compared values match character for character, usually after normalizing case and whitespace. Fuzzy dedupe scores how similar two values are and treats them as duplicates above a threshold, so "Smith, Jane" and "jane smith" collapse into one. Exact is fast and certain; fuzzy catches real-world mess but can be wrong.
What does dedupe mean in storage and backup?
Storage deduplication stores each unique block of data once and replaces repeats with a pointer to it. Because backups repeat almost everything night after night, this saves a large amount of space. It is the same idea as deduping rows, applied to blocks of bytes rather than records, and it happens automatically rather than being something you run.
How do you dedupe in SQL?
For whole rows, SELECT DISTINCT is enough. To keep one row per key while choosing which one survives, number the rows in each group and keep the first: a window function such as ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) gives every row a rank inside its group, and filtering to rank 1 keeps the oldest record per email. GROUP BY with MIN or MAX does the same job for simpler cases.
Is deduping the same as cleaning data?
No. Normalization makes values consistent (trimming spaces, fixing capitalization, standardizing a phone format) without removing anything. Dedupe removes records. Normalizing first makes deduping far more effective, because most "duplicates" that exact matching misses differ only in formatting.
Last updated