Shape / rows · All operations

Keep only the duplicates

A variant of Deduplicate rows

Keep only the rows whose whole row or key appears more than once.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep only the rows whose key or whole row appears more than once

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);

-- every row of an email that appears more than once, the first occurrence included
CREATE TEMP TABLE dup_emails AS
SELECT * FROM signups QUALIFY count(*) OVER (PARTITION BY email) > 1;
-- every row that appears more than once as a whole row (line left out)
CREATE TEMP TABLE dup_rows AS
SELECT * FROM signups QUALIFY count(*) OVER (PARTITION BY email, name, signed_up) > 1;
SELECT * FROM dup_emails ORDER BY line;
SELECT * FROM dup_rows ORDER BY line;
SELECT CASE WHEN ((SELECT list(line ORDER BY line) FROM dup_emails) = [1, 2, 3, 5, 6] AND (SELECT list(line ORDER BY line) FROM dup_rows) = [1, 2, 5, 6]) THEN 'as expected' ELSE error('the repeated emails were not lines 1, 2, 3, 5 and 6, or the repeated whole rows were not lines 1, 2, 5 and 6') 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 repeated emails were not lines 1, 2, 3, 5 and 6, or the repeated whole rows were not lines 1, 2, 5 and 6”.
  • 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).

EasyMorph (1)
  • Keep duplicatesread 2026-09-27

    Keep the rows that are duplicated on all or selected columns

Background

In EasyMorph, the Keep duplicates action keeps all duplicate rows and removes all unique rows.[1] In EasyMorph, the Keep duplicates action can compare all columns or only selected ones, ignoring the values in columns not selected.[1]

When to use it

In EasyMorph, the Keep duplicates action serves datasets where all records should be unique, pulling duplicates out for review to decide whether they warrant removal or modification.[1] Reach for it to audit a key that should be unique: an empty result is the evidence that it is. (Meridian's view)

In an EasyMorph example, keeping duplicates on the Continent column alone returns the Yangtze and Yellow River rows, two rivers that share the continent Eurasia.[1] Read a result keyed on some columns as rows that share a key, not as copies of one record. (Meridian's view)

The method

In Polars, DataFrame.is_duplicated returns a Boolean mask, and passing the mask to DataFrame.filter shows the duplicated rows.[2] In DuckDB, all aggregate functions can be used in a windowing context.[3] In SQL, one way to write it is to count the rows over a partition of the key and keep the rows where that count is above 1. (Meridian's view)

Compared with the alternatives

In Polars, DataFrame.unique with keep set to 'none' does not keep any of the duplicate rows.[4] Keeping only the duplicates and keeping only the rows that never repeat are complements, and running both is a quick check that the two counts add up to the table. (Meridian's view)

Sources

  1. [1]KEEP DUPLICATES [EasyMorph Help]documentation
  2. [2]polars.DataFrame.is_duplicated — Polars documentationdocumentation
  3. [3]Window Functions – DuckDBdocumentation
  4. [4]polars.DataFrame.unique — Polars documentationdocumentation