Finding Duplicate Keys in a CSV File Before an Import

A primary key constraint is a good last line of defense and a bad way to find out what is wrong with a file. When a bulk load fails on the first repeated key, it tells you one key and one line, rolls everything back, and leaves you to run it again to find the next. Looking for the repeats before the load takes seconds, tells you all of them at once, and lets you decide what each one means. This article does that with SQL, with pandas and with the shell, on a small file where I can show every row and on 193,708 rows where I cannot. It is one of the checks in the validation guide and a prerequisite for the keeper decisions in removing duplicate rows.

Three kinds of duplicate

"Duplicate" covers three different problems, and the fix differs.

1. An exact repeat: the whole row appears twice. Usually harmless: a retry, an overlapping export. Drop one.

2. A conflict: the key appears twice with different content. Somebody has to decide which row is true, and the answer is a rule, not a guess.

3. A near-duplicate: the key differs by case, whitespace or formatting, so a strict comparison calls them different. These are invisible to the database and to uniq.

This is the fixture, six rows with one of each:

id,email,name
1,[email protected],Ana
2,[email protected],Bo
2,[email protected],Bo
3,[email protected] ,Ana S
4,[email protected],Cy
4,[email protected],Cyrus

Id 2 appears twice with the same content (an exact repeat). Id 4 appears twice with Cy and Cyrus (a conflict). And [email protected] on line 5, with a capital A and E and a trailing space, is the same email as line 2, with a different id.

In SQL: group, count and keep the lines

The query groups on the key, keeps the groups with more than one row, and lists the file lines. Counting the distinct versions of the rest of the row is what separates the first two kinds:

duckdb -box -c "
SELECT id, count(*) AS n, count(DISTINCT (email, name)) AS distinct_versions, list(line ORDER BY line) AS lines
FROM (SELECT *, row_number() OVER () + 1 AS line FROM read_csv('lab/data/val/keys.csv', all_varchar=true))
GROUP BY id HAVING count(*) > 1 ORDER BY id"

┌────┬───┬───────────────────┬────────┐
│ id │ n │ distinct_versions │ lines  │
├────┼───┼───────────────────┼────────┤
│ 2  │ 2 │ 1                 │ [3, 4] │
│ 4  │ 2 │ 2                 │ [6, 7] │
└────┴───┴───────────────────┴────────┘

Id 2 has two rows and one distinct version: an exact repeat, on lines 3 and 4. Id 4 has two rows and two versions: a conflict, on lines 6 and 7. The line number comes from row_number() OVER () over the scan plus one for the header. That matched the file here, since DuckDB keeps insertion order by default (the preserve_insertion_order setting), but a line number derived from a scan is only as good as that setting. If you cannot rely on it, have the producer add a row number column and group on that, and see the DuckDB CSV documentation for the reader's options.

In pandas

The same split, with duplicated(keep=False) so that every member of a group is marked and not only the later ones:

python - <<'PY'
import pandas as pd
df = pd.read_csv('lab/data/val/keys.csv', dtype=str, keep_default_na=False)
df.index += 2
print(df[df.duplicated('id', keep=False)])
print()
print('exact duplicate rows:', df.index[df.duplicated(keep='first')].tolist())
print('same id, different content:', df[df.duplicated('id', keep=False) & ~df.duplicated(keep=False)].index.tolist())
PY

  id            email   name
3  2  [email protected]     Bo
4  2  [email protected]     Bo
6  4  [email protected]     Cy
7  4  [email protected]  Cyrus

exact duplicate rows: [4]
same id, different content: [6, 7]

Setting df.index += 2 makes the index the file line, so the printed frame says what to open. duplicated() with no column list tests the whole row, which finds the exact repeat at line 4. Rows whose key is repeated but whose content is not are the conflicts: lines 6 and 7. Keep the key columns as text, as in the pandas dtype article, since a key read as a number loses the distinction between 00123 and 123.

In the shell

For one unquoted key column, sort | uniq -d is the fastest check there is:

cut -d, -f1 lab/data/val/keys.csv | tail -n +2 | sort | uniq -d
echo ---
tail -n +2 lab/data/val/keys.csv | cut -d, -f1 | sort | uniq -c | sort -rn | head -n 3

2
4
---
      2 4
      2 2
      1 3

uniq -d printed the keys that appear more than once, 2 and 4. The second command, uniq -c, shows how many times each key appears. Neither says where, and neither says whether the rows differ. Use it as the first look, and use SQL when you need the lines. It also depends on cut -d, being safe for the file, which means no quoted commas in the key column, as discussed in the shell guide.

Near-duplicates: normalize the key first

The email on line 5 is the same address as line 2 and neither of the checks above sees it. Normalizing the key before grouping does:

duckdb -box -c "
SELECT lower(trim(email)) AS email_key, count(*) AS n, list(line ORDER BY line) AS lines
FROM (SELECT *, row_number() OVER () + 1 AS line FROM read_csv('lab/data/val/keys.csv', all_varchar=true))
GROUP BY 1 HAVING count(*) > 1 ORDER BY 1"

┌──────────────────┬───┬────────┐
│    email_key     │ n │ lines  │
├──────────────────┼───┼────────┤
│ [email protected] │ 2 │ [2, 5] │
│ [email protected]  │ 2 │ [3, 4] │
│ [email protected]  │ 2 │ [6, 7] │
└──────────────────┴───┴────────┘

With lower(trim(email)) as the key, three groups appear: [email protected] on lines 2 and 5, and the two pairs found before (they have the same emails as well as the same ids). The third is a customer entered twice under different ids. Whether it is the same person is a business question, not a data question; the query finds the candidates. Choose the normalization to match how the data is used. Lowercasing and trimming is a safe start for emails. For names it would be a guess, and for identifiers with leading zeros it would be wrong.

Composite keys

Some files have no single-column key. A table of order lines is identified by the order and the line number:

duckdb -box -c "
SELECT order_id, line_no, count(*) AS n, count(DISTINCT sku || '/' || qty) AS versions
FROM read_csv('lab/data/val/order-lines.csv', all_varchar=true)
GROUP BY ALL HAVING count(*) > 1"

┌──────────┬─────────┬───┬──────────┐
│ order_id │ line_no │ n │ versions │
├──────────┼─────────┼───┼──────────┤
│ 5001     │ 2       │ 2 │ 1        │
│ 5002     │ 1       │ 2 │ 2        │
└──────────┴─────────┴───┴──────────┘

GROUP BY ALL groups on the two non-aggregated columns. Order 5001, line 2 appears twice with the same content (an exact repeat). Order 5002, line 1 appears twice with a different sku and qty: a conflict. When a file's key is composite, test uniqueness of the combination and not of each column. Each column of a composite key is expected to repeat; only the pair must not.

At the size of a real file

On the faulty customer export (193,708 rows, one repeated id planted at line 402):

time duckdb -box -c "
SELECT id, count(*) AS n, list(line ORDER BY line) AS lines
FROM (SELECT *, row_number() OVER () + 1 AS line FROM read_csv('work/val/customers-bad.csv', all_varchar=true, ignore_errors=true))
GROUP BY id HAVING count(*) > 1"
time (tail -n +2 work/val/customers-bad.csv | cut -d, -f1 | sort | uniq -d)

┌─────┬───┬────────────┐
│ id  │ n │   lines    │
├─────┼───┼────────────┤
│ 460 │ 2 │ [401, 402] │
└─────┴───┴────────────┘

real	0m0.153s
user	0m0.129s
sys	0m0.046s
460

real	0m0.049s
user	0m0.042s
sys	0m0.029s

DuckDB reported id 460 on lines 401 and 402 in 0.15 s. The shell pipeline found the key 460 in 0.05 s and no line. Both are fast enough that checking is never the reason to skip it. The exact same check on a billion rows is a different job, because uniqueness needs a hash table of every key; use a database for that.

What the database does if you do not look

PostgreSQL's COPY into a table with a primary key fails at the first repeat:

psql -v ON_ERROR_STOP=1 -q <<'SQL'
DROP TABLE IF EXISTS k;
CREATE TABLE k (id int PRIMARY KEY, email text, name text);
\copy k FROM 'lab/data/val/keys.csv' WITH (FORMAT csv, HEADER)
SELECT count(*) FROM k;
SQL
echo "exit status: $?"
psql -q -c "SELECT count(*) AS rows_after_failed_copy FROM k"

NOTICE:  table "k" does not exist, skipping
ERROR:  duplicate key value violates unique constraint "k_pkey"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  COPY k, line 4
exit status: 3
 rows_after_failed_copy 
------------------------
                      0
(1 row)

The message names the constraint, the key (Key (id)=(2) already exists) and the file line (COPY k, line 4). That line is correct, and it is a help. But it is only the first. COPY is a single transaction, so the table has 0 rows afterwards, and the next error would only show up after you fix this one and run again. In a file with 3,000 repeats that is a long afternoon. DuckDB's message is shorter still: PRIMARY KEY or UNIQUE constraint violation: duplicate key "2", with no line.

The pattern that avoids both is to load into an unconstrained staging table and then move the rows with a rule:

psql -v ON_ERROR_STOP=1 -q <<'SQL'
DROP TABLE IF EXISTS k_stage, k2;
CREATE TABLE k_stage (id int, email text, name text);
\copy k_stage FROM 'lab/data/val/keys.csv' WITH (FORMAT csv, HEADER)
CREATE TABLE k2 (id int PRIMARY KEY, email text, name text);
INSERT INTO k2 SELECT DISTINCT ON (id) id, email, name FROM k_stage ORDER BY id, name;
SELECT * FROM k2 ORDER BY id;
SQL

NOTICE:  table "k_stage" does not exist, skipping
NOTICE:  table "k2" does not exist, skipping
 id |       email       | name  
----+-------------------+-------
  1 | [email protected]  | Ana
  2 | [email protected]   | Bo
  3 | [email protected]  | Ana S
  4 | [email protected]   | Cy
(4 rows)

DISTINCT ON (id) with ORDER BY id, name loaded four rows. It resolved id 2 trivially, as they are identical, and resolved id 4 by choosing Cy over Cyrus because Cy sorts first. That rule is an accident, and it is the dangerous part: the database will take whatever order you give it, and the result looks authoritative. The loader chose the first row alphabetically, and nobody decided that. The honest options are to reject the conflict and ask the source, or to pick by a column that means something (the latest update time, the highest version) and write the reason down. The staging-table article shows the loading side.

How to tell it worked

Run the check on a file you know has no repeats and expect an empty result. Plant one exact repeat, one conflict and one case variant, and confirm that each is reported at the right line. Compare the number of distinct keys with the number of rows: they should differ by exactly the number of surplus rows in your repeats. After the load, run SELECT count(*) and compare it with the file's row count minus the rows you deliberately dropped, and make the difference something you can explain.

When a repeated key is legitimate

Not every repeated key is a defect. A file of events has a customer id on every row, many times. An export of changes holds each id once per revision. A line-item file repeats the order number for every line. If a column is meant to repeat, it is not the key, and the rule you want is about a different column or a combination. Before treating repeats as errors, find out from the provider what identifies a row, and write it into the contract. A uniqueness check against the wrong column produces thousands of false alarms, and people switch the check off.