Remove Duplicates

Clean out repeated rows, either whole rows or matches on the columns you name.

What it does

Remove Duplicates deletes rows that repeat. It works in two ways. Leave the column picker empty to compare whole rows. Or pick a few columns, and the operation compares only those.

Remove exact duplicate rows

  1. Type duplicates in the Search transforms box, in the Pipeline panel.
  2. Select Remove Duplicates in the results list.
  3. Leave Select columns empty.
  4. Click Apply.

Every value in a row must match for the row to count as a duplicate. One different cell makes the row unique. It stays.

Match on the columns you choose

Often only one column decides identity. An email column is a good example.

  1. Click the Search and select columns... box under Select columns.
  2. Type part of a column name to narrow the list.
  3. Click a column to select it. Repeat for more columns.
  4. Click Apply.

Example: a contact export lists sam@example.com three times. Select the email column. Two of the three rows go away. The other columns come along for the ride.

Which row survives

  • When you pick columns, the first matching row stays. The later ones go.
  • Row order is the order in the grid at that point in your pipeline. The step numbers the rows before it compares them, so the survivor is the same on every run.
  • More columns make the match stricter. Fewer columns remove more rows.
  • With an empty picker, no row is favoured. Only one copy of each identical row remains.

Keep a different row

The first row is not always the one you want. Use the optional Keep by column for that.

  1. Pick your matching columns, for example email.
  2. Open Keep the row with the (optional) and pick a column to rank the duplicates on, for example updated_at.
  3. A Keep dropdown appears. Largest / latest value keeps the newest row in each group. Smallest / earliest value keeps the oldest.

Example: a contact export has the same email three times, with three different updated_at stamps. Match on email, rank by updated_at, keep the latest value. The most recent record is the one that stays.

For SQL users

An empty picker runs SELECT DISTINCT *. A picker with the email column ranks the rows inside each group and keeps rank one, so the result does not move between runs. The ranking is a window function:

ROW_NUMBER() OVER (PARTITION BY "email" ORDER BY "updated_at" DESC)

The ORDER BY is the ranking column you picked and its direction. Leave that field empty and the rows are ranked by their position in the grid.

Try Remove Duplicates with sample data →

Related Operations