# Flag duplicates

Shape / rows

A variant of [Deduplicate rows](https://meridian.online/operations/deduplicate-rows).

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

Page: <https://meridian.online/operations/deduplicate-rows/flag-duplicates>

## Status

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.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- AWS Glue: [`FlagDuplicateRows`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-pyspark-transforms-FlagDuplicateRows.html) (read 2026-09-27)
- AWS Glue: [`FlagDuplicatesInColumn`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-pyspark-transforms-FlagDuplicatesInColumn.html) (read 2026-09-27)
- Polars: [`DataFrame.is_duplicated`](https://docs.pola.rs/api/python/stable/reference/dataframe/descriptive.html) (read 2026-09-27)
- Polars: [`DataFrame.is_unique`](https://docs.pola.rs/api/python/stable/reference/dataframe/descriptive.html) (read 2026-09-27)
- Tableau Prep: [`Identify Duplicate Rows`](https://help.tableau.com/current/prep/en-us/prep_remove_duplicates.htm) (read 2026-09-27)