Deleting Duplicates in Place

Concepts covered: Window Deduplication, Membership Subqueries, Group Filtering, Storage Optimization

From a query to a write

'Clean the table' means the copies leave storage, so every downstream reader gets the right answer without knowing a dedup happened. SQL makes this harder than it sounds. DELETE removes every row its WHERE clause matches, and 2 identical copies match every predicate identically. To keep 1, something has to tell the copies apart.

The delete that keeps nothing

-- Removes every copy of every duplicated order, the keeper included
DELETE FROM orders
WHERE order_id IN (
  SELECT order_id
  FROM orders
  GROUP BY order_id
  HAVING COUNT(*) > 1
);

This is the most common wrong answer, and it reads as correct. On Thursday's data it deletes 2,800 rows, both copies of each of the 1,400 duplicated orders, and those orders vanish from revenue entirely. The total moves from 70,200.00 too high to 70,200.00 too low. A predicate on the key alone cannot keep 1 copy, and naming that before the interviewer does is worth more than any fix.

When each row has an identifier

Postgres gives every physical row a ctid and Oracle gives each a ROWID, and identical copies still carry different ones. Rank the copies, then delete by identifier every row that did not come first:

-- Postgres: ctid tells identical copies apart
DELETE FROM orders
WHERE ctid IN (
  SELECT ctid
  FROM (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY order_id
                              ORDER BY ctid) AS rn
    FROM orders
  ) AS ranked
  WHERE rn > 1
);

SQL Server can delete through a CTE that computes ROW_NUMBER, because the CTE resolves to the base table's rows. On any engine, a column the loader writes per row, such as a load id or a UUID, gives the same handle. Saying you would add one if the table lacks it is part of the answer. Where copies differ, rank on the ordering the business chose, so the row kept is the row the read-time query would have picked.

When nothing tells the copies apart

Snowflake exposes no physical row identifier to SQL, and neither do BigQuery or Redshift, so a table of identical copies offers nothing else to target. The pattern there is stage, delete, reinsert. Set aside 1 copy of each affected key, delete every copy of those keys, then put the single copy back.

-- Snowflake: DDL commits an open transaction, so stage first
CREATE TEMPORARY TABLE orders_keep AS
SELECT DISTINCT *
FROM orders
WHERE order_id IN (
  SELECT order_id FROM orders
  GROUP BY order_id HAVING COUNT(*) > 1
);

BEGIN;
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM orders_keep);
INSERT INTO orders SELECT * FROM orders_keep;
COMMIT;

Thursday's numbers make the check concrete. The temporary table holds 1,400 rows, the DELETE removes 2,800, the INSERT adds 1,400 back, and Thursday drops from 48,200 rows to 46,800. State those expected counts before running anything, then confirm afterwards that COUNT(*) equals COUNT(DISTINCT order_id). SELECT DISTINCT * collapses the copies only because they match in every column. If they differ anywhere, even in a load timestamp, it keeps both copies. Then the staging query needs a ROW_NUMBER filter instead.

The NOT IN version that deletes nothing

A popular variant keeps the lowest id per key: DELETE FROM orders WHERE row_id NOT IN (SELECT MIN(row_id) FROM orders GROUP BY order_id). Suppose every copy of any 1 order has a NULL row_id. That group's MIN is NULL, the list contains a NULL, and NOT IN evaluates to unknown for every row that matches no listed id. The statement succeeds and deletes 0 rows. NOT EXISTS, or the ranked IN form above, has no such hole.

What a delete costs on columnar storage

Columnar warehouses store a table as immutable files, Snowflake's micro-partitions among them, so a DELETE rewrites every file holding a deleted row. The 1,400 copies from 1 retried batch landed together and touch a handful of files. Duplicates scattered across 3 years of history can touch nearly all of them. At that point it costs about the same to rebuild the table or each affected date partition, and the rebuild is easier to verify. Snowflake's INSERT OVERWRITE INTO truncates and refills a table in 1 transaction and keeps its grants. BigQuery's CREATE OR REPLACE TABLE ... AS SELECT swaps the contents atomically, but the new table has only the options the statement declares.

About this lesson

This section is part of the Deduplication: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.