Shape / columns · All operations

Add a matching key column

A variant of Merge near-duplicate values

Add a column holding a phonetic or functional key for each value, leaving the values themselves unchanged, for later matching.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Add a fingerprint key and a Soundex key column and leave the values unchanged

SQL
CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, 'Alice Ng'), (2, 'ng alice'), (3, 'Ng,  Alice.'), (4, 'Bob Okafor'), (5, 'bob  okafor'), (6, ''), (7, NULL),
  (8, 'Robert'), (9, 'Rupert'), (10, 'Smith'), (11, 'Smyth'), (12, 'Ashcraft'), (13, 'Tymczak'), (14, 'Pfister')
) t(id, name);
-- Soundex: keep the first letter; code the rest with 1 to 6 (translate); H and W are dropped before coding so they do not
-- separate two letters of one code, a vowel does; adjacent repeats of a code collapse to one; the first position (the first
-- letter's own code) and the vowels are dropped; the digits are padded with zeros to three.
CREATE TEMP TABLE result AS
WITH letters AS (SELECT *, regexp_replace(upper(strip_accents(name)), '[^A-Z]', '', 'g') AS l FROM raw),
coded AS (SELECT *, string_split(translate(l[1] || replace(replace(l[2:], 'H', ''), 'W', ''),
                                          'BFPVCGJKQSXZDTLMNR', '111122222222334556'), '') AS chars FROM letters WHERE l <> ''),
deduped AS (SELECT *, list_filter(chars, lambda c, i: i = 1 OR c <> chars[i - 1]) AS d FROM coded)
SELECT r.id, r.name,
       array_to_string(list_sort(list_distinct(
         regexp_split_to_array(regexp_replace(trim(lower(strip_accents(r.name))), '[^a-z0-9\s]', '', 'g'), '\s+'))), ' ') AS fingerprint,
       d.l[1] || left(rpad(regexp_replace(array_to_string(d.d[2:], ''), '[^0-9]', '', 'g'), 3, '0'), 3) AS soundex
FROM raw r LEFT JOIN deduped d USING (id);
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'name', 'fingerprint', 'soundex'] AND (SELECT list(name ORDER BY id) FROM result) = (SELECT list(name ORDER BY id) FROM raw) AND (SELECT list(fingerprint ORDER BY id) FROM result WHERE id <= 7) = ['alice ng', 'alice ng', 'alice ng', 'bob okafor', 'bob okafor', '', NULL] AND (SELECT list(soundex ORDER BY id) FROM result WHERE id >= 8) = ['R163', 'R163', 'S530', 'S530', 'A261', 'T522', 'P236'] AND (SELECT count(*) FROM result WHERE id IN (6, 7) AND soundex IS NULL) = 2) THEN 'as expected' ELSE error('the key columns were not added with one fingerprint for every spelling of a name and the Soundex codes R163, S530, A261, T522 and P236, while the names stayed as they were') 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: “the key columns were not added with one fingerprint for every spelling of a name and the Soundex codes R163, S530, A261, T522 and P236, while the names stayed as they were”.
  • 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).

RapidMiner Studio (1)
Talend (1)
  • tGenKeyread 2026-09-27

    Build a functional key per row by applying key algorithms to columns, for blocking and matching

Background

In Talend, tGenKey generates a functional key from the input columns and outputs this key with the input columns, for example to narrow down matching results.[1] In the United States census Soundex, surnames that sound the same but are spelled differently, like SMITH and SMYTH, have the same code.[2] Every census Soundex code consists of a letter and three numbers, and the letter is always the first letter of the surname.[2]

When to use it

Record linkage uses keys for blocking: only pairs of records that agree on one or more selected identifiers, called blocking keys, are compared.[3] Add a key column, rather than rewriting the values, when the merge is not yet yours to make: equal keys propose a match, and a later step or a person decides which values really are the same. (Meridian's view)

Two common Soundex algorithms differ in when vowels are removed, so "Tymczak" is coded T522 by one and T520 by the variant most SQL implementations follow.[4] Build every key with one implementation, and never compare codes produced by two different systems. (Meridian's view) In PostgreSQL, an index on a separate column of Soundex codes, such as a stored generated column, avoids recalculating the codes during index rechecks.[5]

The method

Soundex keeps the first letter of a name and codes the remaining consonants as digits, with consonants at a similar place of articulation sharing a digit.[4] Under Soundex, "Robert" and "Rupert" both return R163, while "Rubin" yields R150.[4] Soundex was developed by Robert C. Russell and Margaret King Odell and patented in 1918 and 1922.[4] The United States census Soundex is a surname index based on how a surname sounds rather than how it is spelled, so that a surname can be found under various spellings.[2]

Daitch-Mokotoff Soundex codes the first six meaningful letters rather than four, and emits several codes when a letter or combination of letters may sound more than one way.[5] PostgreSQL's daitch_mokotoff function therefore returns an array, holding one or more codes depending on how many plausible pronunciations there are.[5]

Compared with the alternatives

PostgreSQL's difference function converts two strings to their Soundex codes and reports how many code positions match, from zero to four.[5] Equal keys give a yes or no, while a graded comparison such as a count of matching positions leaves a threshold to choose; the key column is the plainer of the two, and the easier to explain. (Meridian's view)

Sources

  1. [1]tGenKey | Talend Components for Jobs Helpdocumentation
  2. [2]Soundex Systemorganisation
  3. [3]Record linkagesecondary source
  4. [4]Soundexsecondary source
  5. [5]PostgreSQL: Documentation: 18: F.16. fuzzystrmatch — determine string similarities and distancedocumentation