Exact duplicate rows
The same row appears more than once, identical in every column.
What it looks like
Before
| order_id | customer | amount |
|---|---|---|
| 1001 | Ana Ruiz | 40.00 |
| 1001 | Ana Ruiz | 40.00 |
| 1002 | Ben Cho | 15.00 |
| 1003 | Ben Cho | 15.00 |
After
| order_id | customer | amount |
|---|---|---|
| 1001 | Ana Ruiz | 40.00 |
| 1002 | Ben Cho | 15.00 |
| 1003 | Ben Cho | 15.00 |
How it happens
- A file was loaded twice, or the same export was appended to itself.
- A join to another table matched more than one row and multiplied the results.
- A form was submitted twice after a slow response.
What it breaks
- Totals and counts are too high by exactly the duplicated amount.
- Loading into a table that requires unique keys fails partway through.
- People are contacted, billed, or shipped to twice.
How to spot it
- Compare the number of rows with the number of distinct rows.
- Group by the column that should be unique and list any value that occurs more than once.
- Check whether duplicates arrive in blocks. A whole block repeated points to a double load.
How to fix it
- Decide first whether identical rows can be legitimate. Two identical rows can be two real purchases, and without an order ID there is nothing to tell them apart.
- Where a unique key exists, keep one row per key and remove the identical copies.
- Record how many rows were removed, and find the step that created them so it does not happen on the next load.
- Near-duplicate recordsTwo rows describe the same real thing but differ enough that an exact comparison misses them.
- Duplicate keys with conflicting valuesTwo rows share an identifier that should be unique, but disagree on other fields.
- Stray whitespace and invisible charactersTwo values look identical on screen but differ by a trailing space, a non-breaking space, or a hidden character.
TermsDeduplication, Duplicate, Uniqueness, Primary key