Duplicates: finding them, and deciding which row survives

Three kinds of duplicate

The earlier cleaning lesson made the point that whether two rows are “the same” is a business question. This lesson is about the mechanics once the question is answered, and there are three different mechanics because there are three different kinds of duplicate.

Exact duplicates are rows identical in every column. They come from a file pasted twice or a job that ran twice. Key duplicates share the identifying columns and differ elsewhere: the same payment reference with two timestamps, because a retry recorded a second row. Near duplicates share nothing exactly and are still the same thing: ada@example.com and Ada@Example.com, or +91 98765 43210 and 9876543210.

Finding exact and key duplicates

-- exact: group by everything
SELECT reference, paid_on, amount, city, count(*)
FROM raw_payments
GROUP BY reference, paid_on, amount, city
HAVING count(*) > 1;

-- by key: group by what should be unique
SELECT reference, count(*), min(loaded_at), max(loaded_at)
FROM raw_payments
GROUP BY reference
HAVING count(*) > 1;

The second query’s min and max of the load time tell you whether the duplicates came in one file or across two, which is usually the whole diagnosis. Look at ten of them before deciding anything.

Keeping one, by a rule you can state

The rule is nearly always “the latest” or “the first”, and a window function applies it:

CREATE VIEW payments_deduped AS
SELECT reference, paid_on, amount, city
FROM (
  SELECT *,
         row_number() OVER (PARTITION BY reference
                            ORDER BY loaded_at DESC, ctid DESC) AS rn
  FROM raw_payments
) t
WHERE rn = 1;

Within each reference, rows are numbered from the most recently loaded, and only number 1 survives. The ctid tiebreaker (the physical row address in Postgres; use a serial id elsewhere) makes the choice deterministic when two rows share a load time, which the ranking lesson explained is not optional. DISTINCT cannot do this job, because it keeps rows that differ in any column and has no notion of “latest”.

The rows that did not survive are WHERE rn > 1, and a count of them, by load file, goes into the load log. A deduplication that cannot say how many rows it removed is a filter, not a decision.

Deleting in place

If the duplicates are in a real table rather than a raw staging table, and the decision is to remove them permanently:

DELETE FROM payments
WHERE id IN (
  SELECT id FROM (
    SELECT id, row_number() OVER (PARTITION BY reference ORDER BY created_at DESC, id DESC) AS rn
    FROM payments
  ) t WHERE rn > 1
);

Run the inner SELECT first and count it. Then run the DELETE inside a transaction, compare the row count it reports with that count, and only then commit. The module on changing data safely has the full discipline; the short version is that a DELETE without a rehearsal is how a year of payments disappears.

Near duplicates

Normalising the key, as the previous lesson showed, turns most near duplicates into exact ones: lower-case the email, strip the phone to digits, and the GROUP BY finds them. What remains is fuzzy: Priya Sharma and Priya Sharmaa, Bombay Traders and Bombay Trading Co. Postgres’s pg_trgm extension gives similarity(a, b), a score from 0 to 1 based on shared three-letter fragments, and levenshtein(a, b) from fuzzystrmatch counts the edits between them.

SELECT a.name, b.name, similarity(a.name, b.name)
FROM customers a JOIN customers b ON a.id < b.id
WHERE similarity(a.name, b.name) > 0.6;

This is a self-join over every pair, which is a million comparisons for a thousand customers; on a hundred thousand it needs the extension’s index or a blocking step that only compares names sharing a first letter and a city. And the threshold is a judgement with a cost on both sides. Set it high and Sharma and Sharmaa stay apart; set it low and two different people called Priya Sharma are merged, which loses a customer’s history into somebody else’s and is far harder to undo than a missed match. Review a sample of the proposed merges by hand, log every merge with both original ids, and merge fewer rather than more.

The habit

Say the rule out loud before the query: “one row per reference, the most recently loaded one wins, ties broken by id”. If you cannot say the rule, you are not deduplicating; you are guessing with a window function.