How would you detect and remove duplicate records safely?

Detailed explanation

“Duplicate” must be defined before it can be removed. Two rows can be exact copies (a file loaded twice), or they can share a business key but differ in other columns (a reload with a correction). Each needs a rule, and the rule is a business decision: usually “the most recently loaded version wins”.

Sample data

Order 1 was loaded twice, unchanged. Order 3 was corrected the next day. Order 2 is clean. Every load row gets its own load_id, which later serves as a tiebreaker.

CREATE TABLE raw_orders (
  load_id   INT PRIMARY KEY,
  order_id  INT NOT NULL,
  customer  TEXT NOT NULL,
  amount    NUMERIC(10, 2) NOT NULL,
  loaded_at TIMESTAMP NOT NULL
);
INSERT INTO raw_orders VALUES
  (1, 1, 'asha', 40.00, '2026-10-01 02:00'),
  (2, 2, 'ben',  25.00, '2026-10-01 02:00'),
  (3, 3, 'chen', 90.00, '2026-10-01 02:00'),
  (4, 1, 'asha', 40.00, '2026-10-02 02:00'),
  (5, 3, 'chen', 95.00, '2026-10-02 02:00');

Step 1: find the duplicates

SELECT order_id, COUNT(*) AS copies
FROM raw_orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

order_id	copies
1	2
3	2

SELECT DISTINCT * does not help: the copies differ in load_id and loaded_at (and order 3 in amount), so all five rows are distinct. DISTINCT only removes rows that are identical in every selected column.

Step 2: keep the latest version of each order

CREATE TABLE orders_clean AS
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY loaded_at DESC, load_id DESC) AS rn
  FROM raw_orders
)
SELECT order_id, customer, amount, loaded_at
FROM ranked
WHERE rn = 1;

SELECT * FROM orders_clean ORDER BY order_id;

order_id	customer	amount	loaded_at
1	asha	40.00	2026-10-02 02:00:00
2	ben	25.00	2026-10-01 02:00:00
3	chen	95.00	2026-10-02 02:00:00

load_id DESC is the tiebreaker: if two copies share a loaded_at, the same row still wins on every run. Without it, ROW_NUMBER may pick a different row each time and the pipeline is not reproducible. ROW_NUMBER (not RANK) is required, because RANK would give both tied copies rank 1 and keep both.

Step 3: verify before swapping

SELECT COUNT(*) AS rows_kept,
       COUNT(DISTINCT order_id) AS distinct_keys,
       (SELECT COUNT(DISTINCT order_id) FROM raw_orders) AS keys_in_source
FROM orders_clean;

rows_kept	distinct_keys	keys_in_source
3	3	3

The key is now unique and no order was lost. Then rename or swap the clean table in (in PostgreSQL, two ALTER TABLE ... RENAME statements in one transaction).

Deleting in place

When rebuilding is not an option, delete every row that is not the winner, in a transaction so you can check the count before committing:

BEGIN;
DELETE FROM raw_orders r
USING (
  SELECT load_id,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY loaded_at DESC, load_id DESC) AS rn
  FROM raw_orders
) d
WHERE r.load_id = d.load_id AND d.rn > 1;
SELECT load_id, order_id, amount FROM raw_orders ORDER BY order_id;
COMMIT;

load_id	order_id	amount
4	1	40.00
2	2	25.00
5	3	95.00

The DELETE needs a column that identifies one physical row. If the table has none (true exact duplicates with no id), PostgreSQL’s system column ctid can play that role inside one statement, but it changes when rows are updated or the table is rewritten, so never store it. In warehouses without row ids, rebuild the table with CREATE OR REPLACE TABLE ... AS SELECT (Snowflake, BigQuery, Databricks) and QUALIFY ROW_NUMBER() OVER (...) = 1.

Why rebuild is usually safer

A DELETE is irreversible once committed, and an error in the rule removes real data. Building a clean table, checking it, then swapping keeps the raw table for audit and makes the step easy to rerun. On large tables a rebuild can also be cheaper than a delete, which in MVCC engines leaves dead rows that need vacuuming.

Large tables: deduplicate incrementally

Rebuilding a billion-row table every day is wasteful. Deduplicate only the new batch, then MERGE it into the target on the business key, updating when the incoming row is newer. Each run touches only the new data, and a rerun of the same batch changes nothing. The SCD and upsert patterns are in the SCD Type 2 merge question.

Preventing recurrence

• Enforce uniqueness at the target: a primary key or unique constraint where the engine enforces it. Snowflake and BigQuery accept primary key declarations but do not enforce them, so test instead.
• Make loads idempotent (MERGE on the key, or delete-and-insert by partition) so retries do not append copies.
• Add a data-quality check (the HAVING COUNT(*) > 1 query) that fails the pipeline when the key is not unique.

Common mistakes

1. Deduplicating without agreeing which copy is correct.
2. Using DISTINCT when copies differ in metadata columns.
3. A non-deterministic ORDER BY in ROW_NUMBER (ties in loaded_at), so a different row wins on each run.
4. Using RANK or DENSE_RANK, which keep every tied copy.
5. Deleting in place without a transaction and a count check.