Join / match group · All operations

Group matched records

Turn matched pairs into one group id per record, following matches transitively.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

Matched pairs become one group id per record

SQL
WITH RECURSIVE pairs(a, b) AS (VALUES (1, 2), (2, 3), (4, 5)),
     edges AS (SELECT a, b FROM pairs UNION SELECT b, a FROM pairs),
     nodes AS (SELECT a AS id FROM edges UNION SELECT 6),
     reach(id, member) AS (SELECT id, id FROM nodes UNION SELECT r.id, e.b FROM reach r JOIN edges e ON e.a = r.member),
     grouped AS (SELECT id, min(member) AS group_id FROM reach GROUP BY id)
SELECT id, group_id,
       CASE WHEN (id, group_id) IN ((1, 1), (2, 1), (3, 1), (4, 4), (5, 4), (6, 6)) THEN 'ok'
            ELSE error('record ' || id || ' was put in group ' || group_id) END AS verdict
FROM grouped ORDER BY id;
  • 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: “record ”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

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)
  • Make Groupread 2026-09-27

    Assemble records into groups by following pairwise key relationships transitively

Duckle (1)
Power Query M (1)
  • Table.FuzzyGroupread 2026-09-27

    Group rows whose key values approximately match and aggregate each group

Talend (1)
  • tMatchGroupread 2026-09-27

    Group similar records as duplicates using one or more match rules and blocking keys

Background

In Splink, clustering turns pairwise match predictions into groups of connected records, using the connected components algorithm.[1] In Splink, a cluster of linked people represents all of the records that refer to the same person, and can be given a new identifier.[2] In Alteryx's Make Group tool, the pairs X=A, B=X, Z=A and Y=B put A, B, X, Y and Z into one group, named A.[3]

When to use it

In Splink, every pair of records has a score, and a threshold has to be chosen at or above which links are treated as valid before clusters can be made.[2] In Splink, links between records with a match probability below the clustering threshold are excluded from clusters.[2] In a Splink example, raising the threshold from 0.95 to 0.99 discards one link and breaks a single cluster into two.[2] Record the threshold, or the rule, that admitted the pairs beside the group ids they produced. (Meridian's view)

In Splink's graph metrics, an edge is a bridge if its removal splits a cluster into two smaller clusters.[4] In Splink's cluster evaluation, bridges can be signals of false positive links, especially when they join two highly connected sub-clusters.[4] In Splink's cluster evaluation for linking people, a cluster of over 100 nodes probably means false positive links are being formed, unless one person can sensibly appear hundreds of times.[4] In Splink, cluster density is the number of edges in a cluster divided by the maximum possible number of edges, and a low density could indicate missed links.[4] Before trusting the group ids, look at the largest groups and at the pairs that hold them together. (Meridian's view)

When every record already carries the shared key that identifies its group, an ordinary GROUP BY on that key is enough, and following pairs adds nothing. (Meridian's view)

The method

In graph theory, a component of an undirected graph is a connected subgraph that is not part of any larger connected subgraph.[5] The components of any graph partition its vertices into disjoint sets.[5] Reachability in an undirected graph is an equivalence relation, being reflexive, symmetric and transitive, and the components are formed from its equivalence classes.[5] Finding the transitive closure of an undirected graph is an equivalent formulation of identifying its connected components.[5] In any graph, each isolated vertex forms a component of its own, with one vertex and zero edges.[5] Treat each matched pair as an undirected edge, and include records that matched nothing, so that every record gets exactly one group, even if that group holds only itself. (Meridian's view)

The components of a finite graph can be computed in linear time using either breadth-first search or depth-first search.[5] Hopcroft and Tarjan described essentially the search-based algorithm in 1973, stating that it was already well known.[5] Components can also be tracked as edges are added by using a disjoint-set data structure, which replaces two classes by their union when an edge connects them.[5] Disjoint-set forests were first described by Bernard A. Galler and Michael J. Fischer in 1964.[6]

DuckDB's WITH RECURSIVE clause can express graph traversal on arbitrary graphs, but on a graph with cycles the query must perform cycle detection to prevent infinite loops.[7] In DuckDB, a recursive common table expression (CTE) with USING KEY can compute connected components by tracking, for each node, the lowest ID to which it is connected.[7] Name each group by a rule that depends only on its members, such as the smallest member id, so the label does not shift when the pairs arrive in another order. (Meridian's view)

Compared with the alternatives

In Power Query M, Table.Group groups rows that have the same key, while Table.FuzzyGroup groups rows based on fuzzy matching of keys.[8] AWS Glue's FindMatches transform identifies matching records and assigns a unique identifier to each group of matching records.[9] Grouping stored pairs as a step of its own lets the matching rule and the grouping be checked one at a time. (Meridian's view)

Sources

  1. [1]Clustering - Splinkdocumentation
  2. [2]Linked Data as Graphs - Splinkdocumentation
  3. [3]Make Group Tooldocumentation
  4. [4]Graph metrics - Splinkdocumentation
  5. [5]Component (graph theory) - Wikipediasecondary source
  6. [6]Disjoint-set data structure - Wikipediasecondary source
  7. [7]WITH Clause – DuckDBdocumentation
  8. [8]Table functions - PowerQuery Mdocumentation
  9. [9]FindMatches class - AWS Gluedocumentation