Find and delete duplicate rows

First find the duplicates, then keep one row per business key, then fix whatever loaded twice. The business key is the set of columns that define "the same record", for example order_id, not the technical row id.

Find them

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

If the copies might differ in some columns, group on the business key and look at the other columns, because "duplicates" that differ need a decision about which version is right.

Keep one copy

Number the rows inside each key, newest first, and keep number 1:

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Deleting in place

In Postgres you can delete using the physical row id ctid. SQL Server lets you delete from the CTE directly. Many warehouses have no row id, so rebuilding the table is simpler and safer:

CREATE OR REPLACE TABLE orders AS
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) = 1;

Take a copy or use time travel first, because this replaces the table. Check counts afterwards: the new count should equal the number of distinct order_id values.

Fix the cause

Duplicates after a rerun mean the load was not idempotent. Rerunning it should leave the table unchanged. Options:

• Load with MERGE on the business key instead of plain INSERT.
• Overwrite the partition for the run date instead of appending.
• Load into staging, deduplicate there, then swap.

A common follow-up is how to detect this early. Add a uniqueness test on the key (dbt unique, or a count check in the pipeline) so a double load fails the run instead of reaching the dashboard.