Deduplicate Fuzzy-Matched Records Without Losing Real Rows

1. Normalize the obvious noise before any similarity work.

duckdb -c "SELECT lower(trim(regexp_replace(name, '\\.|,|Inc|LLC|Ltd', '', 'g'))) AS norm, count(*) FROM accounts GROUP BY norm HAVING count(*) > 1 ORDER BY count(*) DESC LIMIT 8"

Expected output: Exact duplicates surface after normalization: punctuation and legal-suffix stripping collapses Acme LLC and acme llc for free.

2. Generate scored candidate pairs with trigram similarity.

psql -d crm -c "SELECT a.id a_id, b.id b_id, round(similarity(a.norm, b.norm)::numeric, 3) score, a.norm, b.norm FROM accounts a JOIN accounts b ON a.id < b.id AND similarity(a.norm, b.norm) > 0.3 ORDER BY score DESC LIMIT 12;"

Expected output: Candidate pairs print with scores from 1.0 down; the interesting band is where real duplicates and lookalike distincts mix.

3. Choose the threshold from labeled examples, not vibes.

-- label 100 pairs by hand first (or from past merges)
CREATE TABLE dedupe_labels (a_id int, b_id int, is_dup bool);
-- INSERT ... (20 min of work, one-time)

-- threshold sweep: how does each cutoff perform?
WITH scored AS (
  SELECT l.a_id, l.b_id, l.is_dup,
         similarity(a.norm, b.norm) AS s
  FROM dedupe_labels l
  JOIN accounts a ON a.id = l.a_id
  JOIN accounts b ON b.id = l.b_id
)
SELECT
  count(*) FILTER (WHERE is_dup) AS total_dups,
  count(*) FILTER (WHERE NOT is_dup) AS total_distinct,
  count(*) FILTER (WHERE is_dup AND s >= 0.55) AS caught,
  count(*) FILTER (WHERE NOT is_dup AND s >= 0.55) AS false_merges
FROM scored;

Expected output: The sweep shows the trade: threshold 0.55 catches 92 percent of duplicates with 2 false merges, while 0.45 catches 97 with 11, making the cutoff a deliberate choice.

4. Merge through a logged, reversible process.

BEGIN;

CREATE TABLE dedupe_merge_log (
  merged_at timestamptz DEFAULT now(),
  survivor_id int, absorbed_id int, score numeric, rolled_back bool DEFAULT false
);

-- record every merge decision with its score
WITH pairs AS (
  SELECT a.id survivor, b.id absorbed,
         similarity(a.norm, b.norm) score
  FROM accounts a JOIN accounts b ON a.id < b.id
  WHERE similarity(a.norm, b.norm) >= 0.55
)
INSERT INTO dedupe_merge_log (survivor_id, absorbed_id, score)
SELECT survivor, absorbed, score FROM pairs;

-- re-point child rows, then deactivate (never DELETE)
UPDATE orders SET account_id = m.survivor_id
FROM dedupe_merge_log m WHERE orders.account_id = m.absorbed_id;
UPDATE accounts SET active = false WHERE id IN (SELECT absorbed_id FROM dedupe_merge_log);

COMMIT;

Expected output: Duplicates merge inside one transaction with every decision logged; absorbed rows are deactivated, not deleted, so a wrong merge is one UPDATE away from reversal.

5. Audit the result and spot-check the survivors.

psql -d crm -c "SELECT count(*) merges FROM dedupe_merge_log WHERE NOT rolled_back;" -c "SELECT survivor_id, count(*) absorbed FROM dedupe_merge_log GROUP BY 1 ORDER BY 2 DESC LIMIT 5;"

Expected output: Merge counts and the biggest survivor groups print, giving reviewers a queue of the riskiest merges to spot-check before announcing completion.

Problem

Similarity scores miss swapped word order

Solution

Trigram similarity is order-sensitive; add a sorted-token variant (word-split, sort, rejoin) as a second normalized column and take the max of both scores.

Problem

Two distinct companies share a name

Solution

No string metric can separate Acme Inc (Ohio) from Acme Inc (Nevada); include region or phone in the pair key, and let the label set teach the threshold where fields must agree.

Problem

A reviewed merge turns out wrong next week

Solution

The merge log plus deactivate-instead-of-delete makes reversal mechanical: re-point child rows back and reactivate the absorbed account, all inside a new logged transaction.