# Find near-duplicate rows

Join / fuzzy

A variant of [Join on approximate matches](https://meridian.online/operations/join-on-approximate-matches).

Compare the rows of one table with each other and flag or drop those whose keys nearly match.

Page: <https://meridian.online/operations/join-on-approximate-matches/find-near-duplicate-rows>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

flag, list and drop near-duplicate rows of one table

```sql
CREATE TEMP TABLE people AS SELECT * FROM (VALUES
  (1, 'Alice Ng'), (2, 'alice ng'), (3, 'Bob Okafor'), (4, 'Bob  Okafor'), (5, 'Carla Díaz'), (6, 'Carla Diaz'),
  (7, 'Dev Patel'), (8, 'Dev Patil'), (9, 'Dev Patel'), (10, 'Eve Larsen'), (11, NULL), (12, NULL), (13, 'Frank Moore')) t(id, name);

CREATE TEMP TABLE keyed AS SELECT id, name, lower(strip_accents(name)) AS norm FROM people;
-- pairs: rows of one table compared with each other, once each (a.id < b.id); a NULL name compares as NULL, which is not a match
CREATE TEMP TABLE pairs AS
SELECT a.id AS first_id, b.id AS second_id, round(jaro_winkler_similarity(a.norm, b.norm), 3) AS score
FROM keyed a JOIN keyed b ON a.id < b.id AND jaro_winkler_similarity(a.norm, b.norm) >= 0.95;
-- flag: each row names the earliest row it nearly matches
CREATE TEMP TABLE flagged AS
SELECT k.id, k.name, min(p.first_id) AS duplicate_of FROM keyed k LEFT JOIN pairs p ON p.second_id = k.id GROUP BY k.id, k.name;
-- drop: keep the rows that nearly match no earlier row
CREATE TEMP TABLE kept AS
SELECT id, name FROM keyed b WHERE NOT EXISTS (SELECT 1 FROM keyed a WHERE a.id < b.id AND jaro_winkler_similarity(a.norm, b.norm) >= 0.95);
-- the same pairs by edit distance of one
CREATE TEMP TABLE pairs_by_edit AS
SELECT a.id AS first_id, b.id AS second_id FROM keyed a JOIN keyed b ON a.id < b.id AND levenshtein(a.norm, b.norm) <= 1;
SELECT * FROM pairs ORDER BY first_id, second_id;
SELECT * FROM flagged ORDER BY id;
SELECT CASE WHEN ((SELECT list((first_id, second_id) ORDER BY first_id, second_id) = [(1, 2), (3, 4), (5, 6), (7, 8), (7, 9), (8, 9)] FROM pairs)
  AND (SELECT list((first_id, second_id) ORDER BY first_id, second_id) = [(1, 2), (3, 4), (5, 6), (7, 8), (7, 9), (8, 9)] FROM pairs_by_edit)
  AND (SELECT list(id ORDER BY id) FILTER (WHERE duplicate_of IS NOT NULL) = [2, 4, 6, 8, 9] AND count(*) = 13 FROM flagged)
  AND (SELECT list(id ORDER BY id) = [1, 3, 5, 7, 10, 11, 12, 13] FROM kept)) THEN 'as expected' ELSE error('six pairs should nearly match (the three-way Dev Patel cluster gives three), the rows 2, 4, 6, 8 and 9 should be flagged as duplicates of an earlier row and dropped, the NULL names should never match, and edit distance one should find the same pairs') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Fuzzy Match`](https://help.alteryx.com/current/en/designer/tools/join/fuzzy-match-tool.html) (read 2026-09-27)
- Duckle: [`Fuzzy Deduplicate`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`Record Match`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Talend: [`tFuzzyUniqRow`](https://help.qlik.com/talend/en-US/components/8.0/fuzzy-matching/fuzzy-matching-component) (read 2026-09-27)