Join / survivorship · All operations

Pick a surviving record

Collapse each group of matched records into one surviving record by per-field rules.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a new grain: each row stands for a group of input rows
Level
several clauses of SQL, or a window

The DuckDB step

One surviving record per group, by a rule per field

SQL
CREATE TEMP TABLE result AS
WITH recs(group_id, name, email, updated) AS (VALUES
       (1, 'Bob Okafor',  NULL,          DATE '2026-01-01'),
       (1, 'Bob  Okafor', 'bob@example.org',     DATE '2025-06-01'),
       (1, 'B. Okafor',   'bob.old@example.org', DATE '2024-01-01'),
       (4, 'Carla Díaz',  NULL,          DATE '2026-02-01'))
SELECT group_id,
       arg_max(name, updated) AS name_latest,
       arg_max(email, updated) FILTER (WHERE email IS NOT NULL) AS email_latest_known,
       max(updated) AS updated, count(*) AS merged
FROM recs GROUP BY group_id ORDER BY group_id;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT max(name_latest) FILTER (WHERE group_id = 1) = 'Bob Okafor' AND max(email_latest_known) FILTER (WHERE group_id = 1) = 'bob@example.org' AND max(merged) FILTER (WHERE group_id = 1) = 3 AND count(*) FILTER (WHERE group_id = 4 AND email_latest_known IS NULL) = 1 FROM result)) THEN 'as expected' ELSE error('the survivor did not take the latest name and the latest known email') 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 survivor did not take the latest name and the latest known email”.
  • 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).

Duckle (1)
Talend (1)
  • tRuleSurvivorshipread 2026-09-27

    Build one surviving record per group of duplicates by applying business rules

Background

Most master data management (MDM) products link records that represent the same real-world entity to create a golden master record of cleaned, reconciled data.[1] Data fusion is the final step of a typical data integration process, after duplicates have been correctly identified.[2] Stibo Systems' survivorship rules declare which values survive for each attribute, reference and data container when two records are merged.[3]

When to use it

Talend's tRuleSurvivorship component receives records whose duplicates, or possible duplicates, are already estimated and grouped together.[4] Picking a survivor comes after matching, so the result is only as good as the groups it is handed: a wrong match merges two people into one record. (Meridian's view) Reach for per-field survivorship when each duplicate holds part of the truth, such as one record with the latest name and another with the only email address. (Meridian's view) When one record in each group is simply the right one, keeping that whole row is the simpler choice. (Meridian's view)

In Semarchy, a multi-attribute survivorship rule governs related attributes as a unit, so that the street, city and postal code of an address stay consistent with each other.[5] A survivor assembled field by field can hold a combination of values no source ever held together, so keep fields that only make sense together under one rule. (Meridian's view)

Decide on purpose whether a newer blank should overwrite an older value, because the two choices give different survivors from the same group. (Meridian's view) In DuckDB, order-sensitive aggregates such as first and last can have non-deterministic results without ordering, and ordering the arguments makes them deterministic.[6]

The method

In Stibo Systems' software, the basic strategy for choosing surviving values is often to trust some sources more than others or to preserve the most recent updates.[3] Stibo Systems' rules can differ between attributes, so that one attribute follows a trusted-source rule while others follow a most-recent rule.[3] In Oracle's Customer Data Management, source system confidence scores are used in survivorship rules to preserve data from the most reliable source in the master record.[7]

Talend's tRuleSurvivorship rules include most common, most complete, longest or shortest, largest or smallest, and a match against a regular expression.[8] Talend's tRuleSurvivorship marks one record in each group as the survivor, and its CONFLICT column shows when more than one record meets a business rule.[8] Talend's tSurviveFields merges a flow on one or more key columns, with operations such as min, max, first, last and best rank, which takes the value with the greatest rank.[9]

The FuSem research system can resolve inconsistencies by grouping and aggregation, through the FuseBy statement, an SQL extension.[2] In DuckDB, a FILTER clause filters the rows fed into one aggregate function, in the same way that a WHERE clause filters rows.[10]

Compared with the alternatives

In Oracle's Customer Data Management, linking keeps duplicate records as unique records with their own identifiers, while merging combines them into one new master record.[7] Oracle's survivorship rules are of two kinds: Set Master Record Rules choose which duplicate becomes the master record, and Set Attribute Value Rules choose the values it holds.[7] Semarchy separates ID survivorship rules, which choose which master record's ID the golden record keeps, from standard rules, which consolidate attribute values.[5] In DuckDB, the QUALIFY clause filters the results of window functions, much as a HAVING clause filters the results of aggregate functions.[11] Keeping one row per key with a window function takes every field from one record, while survivorship can take each field from the record its own rule picks. (Meridian's view)

Sources

  1. [1]Record linkage - Wikipediasecondary source
  2. [2]FuSem – Exploring Different Semantics of Data Fusionsecondary source
  3. [3]Golden Records Survivorship Rulesdocumentation
  4. [4]tRuleSurvivorship | Talend Components for Jobs Helpdocumentation
  5. [5]Set survivorship rules | Semarchy Documentation Hubdocumentation
  6. [6]Aggregate Functions – DuckDBdocumentation
  7. [7]Overview of Duplicate Resolution Setupdocumentation
  8. [8]tRuleSurvivorship Standard properties | Talend Components for Jobs Helpdocumentation
  9. [9]tSurviveFields | Talend Components for Jobs Helpdocumentation
  10. [10]FILTER Clause – DuckDBdocumentation
  11. [11]QUALIFY Clause – DuckDBdocumentation