Find duplicate rows in SQL and schedule it as a data quality check

A duplicate row is the quietest way to get a wrong number. Nothing errors, the load succeeded, the chart renders, and the sum is a bit too high. Usually someone notices when finance says the total is off by a few percent and nobody can say which few percent.

Here is how to find duplicates in Postgres SQL, how to tell harmless ones from dangerous ones, how to remove them in a query without deleting data, and how to turn the whole thing into a check that tells you the next morning.

The basic query

Pick the column or columns that should be unique, group by them, and keep the groups with more than one row:

SELECT event_id, count(*) AS copies
FROM events
WHERE created_at >= now() - interval '7 days'
GROUP BY event_id
HAVING count(*) > 1
ORDER BY copies DESC, event_id;

On a table with 20 events where three were loaded twice and a fourth was retried, this returns four keys, each with copies = 2. On a clean table it returns nothing, which is exactly what you want from a check: silence when healthy, a row per problem when not.

Two choices in that query matter. The WHERE limits the scan to recent rows, so it stays cheap on a large table and only reports new problems. And it returns the keys, not just a count, so whoever gets the alert can open the actual rows.

If the key is several columns, group by all of them:

SELECT customer_id, created_at, count(*) AS copies
FROM events
GROUP BY customer_id, created_at
HAVING count(*) > 1;

Be sure the combination really is unique by design. Two different events from one customer in the same second would show up here and be fine. If the natural key is shaky, that is the first finding.

How big is the problem?

For a quick size, compare the row count with the distinct key count:

SELECT count(*) AS total_rows,
       count(DISTINCT event_id) AS distinct_keys,
       count(*) - count(DISTINCT event_id) AS extra_rows,
       round(100.0 * (count(*) - count(DISTINCT event_id))
             / NULLIF(count(*), 0), 2) AS dup_pct
FROM events;

In my test table that gave 24 rows, 20 distinct keys, 4 extra rows and 16.67%. The extra_rows figure is how many rows a sum over this table counts that it should not. If every row carries an amount of 10, the total is 40 too high.

One caution: count(DISTINCT event_id) ignores nulls and GROUP BY groups them together. A key column that can be null will behave differently in the two queries, so check null keys separately with the null rate check.

Exact repeats or changed retries?

Not every duplicate is the same problem. An exact repeat (same key, same values) is a double load, and dropping either copy is safe. A key that appears twice with different values is a retry or an update that landed as a new row, and which copy is right is a question about the source.

Count distinct versions per key to separate them:

SELECT event_id,
       count(*) AS copies,
       count(DISTINCT (customer_id, amount, created_at)) AS distinct_versions
FROM events
GROUP BY event_id
HAVING count(*) > 1
ORDER BY event_id;

Three keys come back with distinct_versions = 1 (exact repeats) and one with distinct_versions = 2 (the value changed between loads). The first kind you can usually fix in the pipeline. The second needs a decision, and you want to know about it before you pick a winner by accident.

Where duplicates come from

• Retries. A job times out after writing, runs again, and writes the same rows twice. The fix is an idempotent load (an upsert on the key) rather than insert.
• Replays and backfills. Someone re-runs a day to fix a bug and the old rows are still there.
• Change data capture. Updates arrive as new rows. That is by design when the table is a history, and a bug when a dashboard treats it as current state.
• Join fan-out. The table itself is fine, but a join repeated its rows. That is a different check, covered in join fan-out in SQL. If the duplicate check passes on the base table and the number is still too high, look there.

To see which load made them, number the rows per key and count the repeats by load day:

SELECT loaded_at::date AS load_day,
       count(*) AS rows_loaded,
       count(*) FILTER (WHERE rn > 1) AS repeats
FROM (
  SELECT *, row_number() OVER (PARTITION BY event_id ORDER BY loaded_at) AS rn
  FROM events
) x
GROUP BY 1
ORDER BY 1;

A spike of repeats on one day points at one run. This only works if the table records when each row was loaded, so add a loaded_at column if you do not have one. It pays for itself the first time you need it.

Dedupe in a query, not with DELETE

Deleting rows from a table you do not own is a bad first move. Read the clean version instead. In Postgres, DISTINCT ON keeps one row per key, and the ORDER BY decides which:

SELECT DISTINCT ON (event_id) *
FROM events
ORDER BY event_id, loaded_at DESC;

This keeps the latest load for each key and returned 20 rows from my 24. Use it as a view or a subquery that your dashboards read, and leave the raw table alone until you know the cause. In other dialects the same idea is row_number() = 1 over a partition. And note that SELECT DISTINCT * only removes rows that are identical in every column, so a loaded_at column that differs makes it do nothing.

Run it as a check

The first query is already the check. Wrap it so it returns a single number, which is what an alert condition needs:

SELECT count(*) AS duplicated_keys
FROM (
  SELECT event_id
  FROM events
  WHERE created_at >= now() - interval '7 days'
  GROUP BY event_id
  HAVING count(*) > 1
) t;

Run it once a day, shortly after the load finishes, and alert when duplicated_keys > 0. In arcodash that is a scheduled monitor on a saved query, set up as in the data quality monitors guide. The same SQL works in cron with psql and a webhook if you prefer to wire it yourself.

Some practical notes on thresholds:

• Start at zero. A natural key that is meant to be unique should have no duplicates at all.
• If the source legitimately produces a few (some event streams do), alert on a share instead, using the dup_pct query, and set the threshold just above your normal.
• Alert on the recent window only. Old duplicates you have accepted should not page you every day.

Limits

This finds rows that share a key. It does not find the same real-world thing entered under different keys, like two customer records for one person, and it cannot tell you which copy is correct. It also only checks the key you chose, so a wrong key gives a green check on a table full of duplicates.

For a wider set of daily checks, see lightweight data observability for small teams, and the SQL data check generator writes a duplicate check for any table and dialect.