Shape / rows · All operations

Separate unique rows from duplicates

A variant of Deduplicate rows

Send the first row per key to one output and the repeats to a second output.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Send the first row of each key to one output and its repeats to another

SQL
CREATE TEMP TABLE signups AS SELECT * FROM (VALUES
  (1, 'ada@example.com', 'Ada',    DATE '2026-01-03'),
  (2, 'bo@example.com',  'Bo',     DATE '2026-01-04'),
  (3, 'ada@example.com', 'Ada L.', DATE '2026-01-09'),
  (4, 'cy@example.com',  'Cy',     DATE '2026-01-10'),
  (5, 'bo@example.com',  'Bo',     DATE '2026-01-04'),
  (6, 'ada@example.com', 'Ada',    DATE '2026-01-03'),
  (7, 'di@example.com',  'Di',     DATE '2026-01-15')
) t(line, email, name, signed_up);

CREATE TEMP TABLE numbered AS
SELECT *, row_number() OVER (PARTITION BY email ORDER BY line) AS occurrence FROM signups;
CREATE TEMP TABLE unique_rows AS SELECT line, email, name, signed_up FROM numbered WHERE occurrence = 1;
CREATE TEMP TABLE repeat_rows AS SELECT line, email, name, signed_up FROM numbered WHERE occurrence > 1;
SELECT * FROM unique_rows ORDER BY line;
SELECT * FROM repeat_rows ORDER BY line;
SELECT CASE WHEN ((SELECT list(line ORDER BY line) FROM unique_rows) = [1, 2, 4, 7] AND (SELECT list(line ORDER BY line) FROM repeat_rows) = [3, 5, 6] AND (SELECT count(*) FROM unique_rows) + (SELECT count(*) FROM repeat_rows) = (SELECT count(*) FROM signups) AND (SELECT count(*) FROM unique_rows u JOIN repeat_rows r USING (line)) = 0 AND (SELECT count(DISTINCT email) FROM unique_rows) = 4) THEN 'as expected' ELSE error('the first rows were not lines 1, 2, 4 and 7, or the repeats were not lines 3, 5 and 6, or the two outputs overlap or miss a row') 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 first rows were not lines 1, 2, 4 and 7, or the repeats were not lines 3, 5 and 6, or the two outputs overlap or miss a row”.
  • 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)
  • Uniqueread 2026-09-27

    Split records into unique and duplicate outputs by grouping on chosen fields

Apache NiFi (1)
  • DetectDuplicateread 2026-09-27

    Route a FlowFile as duplicate when a value computed from its attributes is already cached

Talend (1)
  • tUniqRowread 2026-09-27

    Compare rows on key columns and separate unique rows from duplicates

Background

Alteryx Designer's Unique tool sends the first record of each group to its U output and the remaining records to its D output.[1]

When to use it

Split when the repeats need somewhere to go: a quarantine table to inspect, a count to report, or a later step that must know which records were set aside. (Meridian's view) In Apache Hop, the Unique Rows transform's Redirect duplicate row option processes duplicate rows as errors and redirects them to the transform's error stream.[2]

Alteryx Designer's Unique tool sorts the data on the unique columns, so a specific sort order has to be assigned with the Sort tool before the Unique tool.[1] Set the order that decides which row of each group counts as first before splitting. (Meridian's view)

In Apache NiFi, the DetectDuplicate processor's Age Off Duration property sets a time interval to age off cached FlowFiles.[3] Match DetectDuplicate's Age Off Duration to how late a repeat can arrive. (Meridian's view)

The method

Talend's tUniqRow component carries out deduplication on the columns selected in its Unique key area.[4] In Talend's tUniqRow, the Only once each duplicated key option sends only the first duplicated entry of each key to the output flow for duplicates.[4] Talend's tUniqRow returns NB_UNIQUES and NB_DUPLICATES, the numbers of unique and duplicate rows, as After variables.[4] Talend's tUniqRow has a Case sensitive check box to differentiate upper case and lower case.[4]

Sources

  1. [1]Unique Tooldocumentation
  2. [2]Unique Rows | Apache Hopdocumentation
  3. [3]DetectDuplicate - Apache NiFidocumentation
  4. [4]tUniqRow Standard properties | Talend Components for Jobs Helpdocumentation