Join / fuzzy · All operations

Find near-duplicate rows

A variant of Join on approximate matches

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Alteryx Designer (1)
  • Fuzzy Matchread 2026-09-27

    Find non-identical duplicates within or across sources by match fields and similarity thresholds

Duckle (2)
  • Fuzzy Deduplicateread 2026-09-25

    drop near-duplicate rows by Jaro-Winkler or Levenshtein similarity of a key

  • Record Matchread 2026-09-25

    self-join emitting row pairs whose key similarity meets a threshold, with score

Talend (1)
  • tFuzzyUniqRowread 2026-09-27

    Compare columns within one flow by a matching method and collect the duplicates found

Background

In Alteryx Designer, the Fuzzy Match tool identifies non-identical duplicates in a data set by match fields and similarity thresholds.[1] In Splink, the dedupe_only link type finds duplicates within a single input dataset.[2] Record linkage, the task of finding records that refer to the same entity, is also called duplicate detection and deduplication.[3]

When to use it

In public health, record linkage can examine data sources to eliminate duplicate records and to identify under-reporting and missing cases.[3] The Alteryx Fuzzy Match tool needs a unique identifier for each record, added with a Record ID tool upstream where the data has none.[1] Give every row a stable identifier before looking for near-duplicates, so each pair can be named once and every flag points at a row that still exists. (Meridian's view)

In Splink's cluster evaluation, an edge is a bridge if its removal splits a cluster into two smaller clusters.[4] In Splink's cluster evaluation, bridges can signal false positives, especially when a bridge joins two highly connected sub-clusters.[4] In Splink's cluster evaluation, a large and highly dense cluster is usually less suspicious than a large low-density cluster.[4] Read the largest groups by eye before dropping anything, and look hardest at rows held in a group by a single near match. (Meridian's view)

Flag near-duplicates before dropping them: a flag column can be read and reversed, while a dropped row has to be found again. (Meridian's view) Name which row survives in each group, such as the earliest or the most complete, so the result is the same on every run. (Meridian's view)

The method

In a Splink deduplication, the number of record pairs to compare grows as n(n-1)/2, so 1 million input records would generate around 500 billion comparisons.[5] The Alteryx Fuzzy Match tool's Purge mode compares all records from a single source to identify duplicates, while its Merge mode compares only records from different sources.[1]

Splink clusters pairwise match predictions into groups of connected records with the connected components graph clustering algorithm.[6] In the Alteryx Fuzzy Match tool, the option Don't Compare Records already in a Group means that if record 1 matches records 2 and 3, record 2 is not matched against record 3.[1] In Alteryx Designer, a Make Group tool downstream of Fuzzy Match links the matched groups together.[1]

Compared with the alternatives

In Splink, link_only finds links between datasets without looking for links within each one, while link_and_dedupe finds links within and between them.[2]

Sources

  1. [1]Fuzzy Match Tooldocumentation
  2. [2]Settings Dict - Splinkdocumentation
  3. [3]Record linkagesecondary source
  4. [4]Graph metrics - Splinkdocumentation
  5. [5]What are Blocking Rules? - Splinkdocumentation
  6. [6]Clustering - Splinkdocumentation