Shape / rows · All operations

Flag duplicates

A variant of Deduplicate rows

Keep every row and add a column marking the rows that repeat another.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep every row and flag the ones that repeat an earlier row

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

-- repeats by key: a later row with the same email as an earlier one. The other convention, count(*) OVER (PARTITION BY email) > 1,
-- flags every member of a repeated group, the first included. PARTITION BY treats NULL keys as equal, so rows with no email repeat each other.
-- repeats as a whole row: a later row equal to an earlier one in every column but line
CREATE TEMP TABLE flagged AS
SELECT *,
       row_number() OVER (PARTITION BY email ORDER BY line) > 1 AS repeats_email,
       row_number() OVER (PARTITION BY email, name, signed_up ORDER BY line) > 1 AS repeats_row
FROM signups;
SELECT * FROM flagged ORDER BY line;
SELECT CASE WHEN ((SELECT count(*) FROM flagged) = 7 AND (SELECT list(line ORDER BY line) FROM flagged WHERE repeats_email) = [3, 5, 6] AND (SELECT list(line ORDER BY line) FROM flagged WHERE repeats_row) = [5, 6] AND (SELECT list(column_name) FROM (DESCRIBE flagged)) = ['line', 'email', 'name', 'signed_up', 'repeats_email', 'repeats_row']) THEN 'as expected' ELSE error('all seven rows should survive with two flag columns: lines 3, 5 and 6 repeat an email and lines 5 and 6 repeat a whole 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: “all seven rows should survive with two flag columns: lines 3, 5 and 6 repeat an email and lines 5 and 6 repeat a whole 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).

AWS Glue (2)
  • FlagDuplicateRowsread 2026-09-27

    Add a column that flags rows that exactly match an earlier row

  • FlagDuplicatesInColumnread 2026-09-27

    Add a column that flags rows whose value in a source column appeared in an earlier row

Polars (2)
Tableau Prep (1)
  • Identify Duplicate Rowsread 2026-09-27

    Add an Is Duplicate Row? field over all or selected fields, then keep the unique rows

Background

In pandas, DataFrame.duplicated returns a Boolean Series indicating whether each row is a duplicated row.[1] AWS Glue's FlagDuplicateRows transform returns a new column marking each row that exactly matches an earlier row, and the initial occurrence is not flagged.[2] KNIME's Duplicate Row Filter can remove duplicate rows or instead mark the rows with additional information about their duplication status.[3]

When to use it

Flag rather than remove when someone needs to see the repeats before deciding what happens to them. (Meridian's view) In Tableau Prep, Identify Duplicate Rows creates a calculated field, Is Duplicate Row?, and the Data Grid shows which rows are unique and which rows are duplicates.[4] In Tableau Prep, duplicate rows may not appear in sampled data.[4]

In pandas, the keep parameter of DataFrame.duplicated sets which duplicates are marked: first marks all but the first occurrence, last all but the last occurrence, and False marks all of them.[1] In a pandas example of five rows in which only the first two repeat, duplicated() marks one row as True and duplicated(keep=False) marks two.[1] In Polars, DataFrame.is_duplicated gets a mask of all duplicated rows, and in its example both copies of a repeated row are marked true in the mask.[5] Check which rows a flag marks before counting them, because whether the first copy is flagged changes the count. (Meridian's view)

In Tableau Prep, the Is Duplicate Row? field sorts rows by a Source Row Number field where one is available, and otherwise by the first field in the table schema.[4] In Tableau Prep, changing the sort order of fields in the calculation specifies which duplicate row is identified as Unique.[4] Where the first copy is left unflagged, fix the order that makes it first, so a rerun flags the same rows. (Meridian's view)

The method

In pandas, the subset parameter of DataFrame.duplicated limits the comparison to certain columns, and by default all of the columns are used.[1] AWS Glue's FlagDuplicatesInColumn transform flags a row when the value in its source column matches a value in an earlier row of that column.[6]

In Tableau Prep, the Is Duplicate Row? calculation identifies the first row of each set of matching fields as Unique and the remaining rows as Duplicate.[4] Tableau Prep's example calculation numbers the rows within each PARTITION of the fields with ROW_NUMBER, in the order its ORDERBY gives, and tests whether the number is 1.[4] In Polars, the is_first_distinct expression returns a Boolean mask indicating the first occurrence of each distinct value.[7]

KNIME's Duplicate Row Filter, set to keep duplicate rows, appends columns with additional information to the input table.[3] In KNIME's Duplicate Row Filter, the row chosen in each set of duplicates can be the first, the last, or the first row with the minimum or maximum value in a selected column.[3]

Compared with the alternatives

In pandas, DataFrame.drop_duplicates takes the same subset and keep parameters as DataFrame.duplicated, but returns the DataFrame with duplicate rows removed.[1][8] In Tableau Prep, keeping only the Unique rows of the Is Duplicate Row? field and excluding its Duplicate rows produce the same result.[4] A flag is the removal's test kept as a column: once it is computed, the rows a removal would drop can still be shown, counted or set aside. (Meridian's view)

Sources

  1. [1]pandas.DataFrame.duplicated — pandas 3.1.1 documentationdocumentation
  2. [2]FlagDuplicateRows class - AWS Gluedocumentation
  3. [3]Duplicate Row Filterdocumentation
  4. [4]Remove Duplicate Rows - Tableaudocumentation
  5. [5]polars.DataFrame.is_duplicated — Polars documentationdocumentation
  6. [6]FlagDuplicatesInColumn class - AWS Gluedocumentation
  7. [7]polars.Expr.is_first_distinct — Polars documentationdocumentation
  8. [8]pandas.DataFrame.drop_duplicates — pandas 3.1.1 documentationdocumentation