Join / fuzzy · All operations

Join on approximate matches

Approximate string match join.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one table from two or more
Level
several clauses of SQL, or a window

The DuckDB step

Match names by string similarity

It reads the orders and customers tables the proofs share. Create them first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT DISTINCT o.customer AS as_written, c.customer AS matched, round(jaro_winkler_similarity(lower(o.customer), lower(c.customer)), 3) AS score
FROM orders o JOIN customers c ON jaro_winkler_similarity(lower(strip_accents(o.customer)), lower(c.customer)) >= 0.92
ORDER BY score;
SELECT * FROM result ORDER BY ALL;
SELECT CASE WHEN ((SELECT count(*) = 6 AND count(*) FILTER (WHERE as_written IN ('Dev Patel', 'Eve Larsen')) = 0 AND max(matched) FILTER (WHERE as_written = 'Bob  Okafor') = 'Bob Okafor' AND max(matched) FILTER (WHERE as_written = 'Carla Díaz') = 'Carla Diaz' FROM result)) THEN 'as expected' ELSE error('a misspelt name did not match, or a stranger did') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a misspelt name did not match, or a stranger did”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Generate candidate pairs

Limit comparisons to pairs of records that share a blocking key before scoring them.

Join on similar keys

Join two tables where key values are similar enough by a string similarity measure and threshold.

Score the similarity of two strings

Add a column with the similarity or distance between two string values.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

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

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

Apache Hop (1)
  • Fuzzy matchread 2026-09-27

    Match a field to a lookup stream with Levenshtein, Jaro, soundex and similar algorithms

Duckle (5)
  • Candidate Pairs (Blocking)read 2026-09-25

    generate candidate pairs by blocking rules for entity resolution

  • Fuzzy Deduplicateread 2026-09-25

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

  • Record Linkageread 2026-09-25

    link records across two inputs by string similarity of key columns

  • Record Matchread 2026-09-25

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

  • Text Similarityread 2026-09-25

    string similarity between two columns: levenshtein, damerau, jaccard, jaro-winkler

Pentaho Data Integration (1)
  • Fuzzy matchread 2026-09-27

    Find approximate matches for a field in a lookup stream with string-matching algorithms

Power Query M (2)
  • Table.FuzzyJoinread 2026-09-27

    Join two tables on keys that approximately match, by a similarity threshold

  • Table.FuzzyNestedJoinread 2026-09-27

    Fuzzy-match two tables on keys and put the matching rows in a new nested-table column

Talend (6)
  • tBlockedFuzzyJoinread 2026-09-27

    Fuzzy-join two flows on several columns; output matches, possible matches and rejects

  • tFuzzyJoinread 2026-09-27

    Fuzzy-join two flows on several columns, comparing main columns with lookup reference columns

  • tFuzzyMatchread 2026-09-27

    Compare a main-flow column with a lookup reference column and output the match distance

  • tFuzzyUniqRowread 2026-09-27

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

  • tMatchPairingread 2026-09-27

    Compute pairs of suspect duplicates by blocking key, setting aside unique rows and exact duplicates

  • tRecordMatchingread 2026-09-27

    Fuzzy-join a main flow to a lookup flow on several columns; output matches, possible matches and rejects

Background

Approximate string matching finds strings that match a pattern approximately rather than exactly.[1] Record linkage is necessary when joining data sets on entities that may or may not share a common identifier.[2] Record linkage is a common application of approximate matching, where records from two disparate databases are matched.[1] In Power Query, Table.FuzzyJoin joins two tables on a comparison based on similarity of text rather than equality of text.[3]

When to use it

Many key identifiers for the same entity can be presented quite differently between data sets, and even within one.[2] Most, if not all, record linkage strategies give more accurate links when values are first standardised into a consistent format, such as every name written surname first.[2] In Power Query, fuzzy matching works best when every string in a column holds only the text to be compared, with no extra components.[4] Clean what can be cleaned exactly first, such as case, accents and spacing, and keep the approximate comparison for the differences that remain. (Meridian's view)

Power Query's fuzzy merge uses a default similarity threshold of 0.80, and a threshold of 1.00 is the same as an exact match.[5] In Power Query's fuzzy merge, Grapes matches Graes, which is missing the letter p, only if the threshold is set below 0.90.[5] Choose the threshold by reading the pairs that score just above and just below it, rather than taking a tool's default on trust. (Meridian's view)

Table.FuzzyJoin in Power Query returns every matching row for each input row unless its NumberOfMatches option sets a maximum.[3] Decide before the join whether a row may take several matches or only its best one, and count the rows on each side afterwards to see which you got. (Meridian's view)

Deterministic record linkage matches two records when all or some of their identifiers are identical.[2] Deterministic record linkage is a good option when the entities share a common identifier, or several identifiers whose data quality is relatively high.[2] Where a clean shared key exists, an exact join is cheaper and easier to defend than an approximate one. (Meridian's view)

The method

In approximate string matching, the closeness of a match is measured by the edit distance, the number of primitive operations needed to convert a string into an exact match.[1] The usual primitive operations of edit distance are insertion, deletion and substitution, and some approximate matchers also treat the transposition of two letters as one.[1] The Levenshtein distance between two strings is the minimum number of single-symbol insertions, deletions or substitutions needed to turn one into the other.[6]

The Jaro–Winkler similarity is a variant of the Jaro distance metric of Matthew A. Jaro (1989), proposed in 1990 by William E. Winkler.[7] Jaro similarity is 0 when two strings do not match at all and 1 when they are an exact match.[7] Jaro–Winkler similarity gives more favourable ratings to strings that match from the beginning, over a common prefix of up to four characters.[7] In Power Query, fuzzy merge measures the similarity between pairs of values with the Jaccard similarity algorithm.[5]

In DuckDB, jaro_winkler_similarity returns a number between 0 and 1, and levenshtein returns the minimum number of single-character edits between two strings.[8] DuckDB's text similarity functions treat characters of different cases, such as a and A, as different.[8] Lower-case both sides before scoring them in DuckDB, and strip accents where they are noise rather than meaning. (Meridian's view)

In record linkage, comparing every record in one file with every record in another quickly becomes computationally infeasible: two files of one million records each yield 10^12 candidate pairs.[2] Blocking reduces the cost of record linkage by comparing only the pairs that agree on one or more selected identifiers, called blocking keys.[2] The cost of blocking is that true matches that disagree on a blocking key are never compared.[2] Record linkage systems often use several blocking passes with different keys, so that a match missed by one pass can be found by another.[2] In Splink, blocking on first name and surname is equivalent to joining the input records on the condition that first name and surname are equal.[9] Where a column can be trusted exactly, such as a postcode or a year of birth, put it in the join condition beside the similarity test, and run a second pass on another key for the matches it misses. (Meridian's view)

Ivan Fellegi and Alan Sunter formalised the probabilistic foundations of record linkage in 1969, in A Theory for Record Linkage.[2] Probabilistic record linkage weighs the evidence from several identifiers, scoring each comparison by how much more likely it is among true matches than among non-matches.[2] Approximate string matches fit the probabilistic framework too: Smith and Smyth have a Levenshtein edit distance of 1, and that degree of similarity can be weighed in the same way.[2] Other string comparators, such as Jaro–Winkler, can be used in probabilistic record linkage in the same way.[2]

Compared with the alternatives

The Hamming distance allows only substitution, so it applies only to strings of the same length, while the Levenshtein distance allows deletion, insertion and substitution.[7] The Damerau–Levenshtein distance allows insertion, deletion, substitution and the transposition of two adjacent characters.[7]

In record linkage, running names through a phonetic algorithm such as Soundex or Metaphone can help resolve differences in names, though it may still stumble over surname changes.[2] In Apache Hop's Fuzzy match transform, the Metaphone, Double Metaphone and Soundex algorithms are based on the English language and would not be useful to compare other languages.[10] In Alteryx Designer, the Name with Nicknames match style checks a nicknames table, so that the name Andrew may match Andy or Drew.[11] Map known nicknames, synonyms and abbreviations through a lookup table rather than lowering the threshold until they match. (Meridian's view)

Sources

  1. [1]Approximate string matchingsecondary source
  2. [2]Record linkagesecondary source
  3. [3]Table.FuzzyJoin - PowerQuery M | Microsoft Learndocumentation
  4. [4]How fuzzy matching works in Power Query - Power Query | Microsoft Learndocumentation
  5. [5]Fuzzy merge - Power Query | Microsoft Learndocumentation
  6. [6]Levenshtein distancesecondary source
  7. [7]Jaro–Winkler distancesecondary source
  8. [8]Text Functions – DuckDBdocumentation
  9. [9]What are Blocking Rules? - Splinkdocumentation
  10. [10]Fuzzy match | Apache Hopdocumentation
  11. [11]Fuzzy Match Tooldocumentation