Six Ways to Deduplicate a Table: What Each Costs and Which Row Survives

Duplicates arrive in three flavors and each needs a different answer. Exact copies from an at-least-once delivery retry. Near copies from an upstream system that resends a whole record when one field changes. And logical duplicates that are not duplicates at all — two genuine events that happen to share a business key because the key was never unique.

Teams tend to reach for the same tool for all three. Usually DISTINCT, sometimes GROUP BY, occasionally a ROW_NUMBER with no tiebreaker, and then they are surprised when the row counts move between runs.

I have written all six of the strategies below in production, and the choice is rarely about performance. It is about which row you want to keep and whether you can prove the choice is stable. A deduplication that returns a different survivor on Tuesday than it did on Monday is not a deduplication, it is a coin flip with extra steps.

Start with the question that decides everything: are the duplicate rows byte-identical, or do they differ?

Identical versus near-duplicate

flowchart TD
  dupes["Duplicate rows detected"] --> same{"Identical across every column?"}
  same -->|Yes| cheap["DISTINCT or GROUP BY"]
  same -->|No| key{"Which row should survive?"}
  key -->|Latest by timestamp| rn["ROW_NUMBER with QUALIFY"]
  key -->|Merge fields across rows| agg["GROUP BY with MAX or COALESCE"]
  key -->|Should not exist at all| grain["Grain bug: fix the key, not the rows"]
  cheap --> write["Then fix the write path"]
  rn --> write

Identical duplicates are a delivery artifact. Differing duplicates are a modeling decision you have to make explicitly.

The test is one query, and I run it before choosing anything:

-- How many duplicate keys, and do the rows differ?
select
  count(*)                                as total_rows,
  count(distinct order_id)                as distinct_keys,
  count(*) - count(distinct order_id)     as excess_rows,
  -- If this equals distinct_keys, every duplicate set is identical.
  count(distinct hash(order_id, customer_id, amount, status)) as distinct_full_rows
from raw.orders;

When distinct_full_rows equals distinct_keys, the duplicates are exact copies and the cheap strategies apply. When it is larger, the rows differ and you have to decide which one is true. That decision belongs to whoever owns the data, not to whoever writes the SQL.

Strategy 1: DISTINCT

The right tool for exactly one case — a full-row duplicate over a narrow projection.

-- Fine: enumerating unique values.
select distinct country_code, currency from dim_locations;

-- Useless: any differing column defeats it.
select distinct * from raw.orders;

The failure mode is silent. If two rows share order_id but differ by a loaded_at timestamp, DISTINCT * returns both and the query looks like it worked. This is the same trap as DISTINCT used to patch a join fan-out, which I wrote about in the anti-patterns post — the keyword is not the problem, using it as a substitute for knowing the grain is.

Cost: a full sort or hash of every projected column. On wide tables it is the most expensive of the cheap options, because the hash key is the entire row.

Strategy 2: GROUP BY with aggregates

When duplicates differ and you want to merge them rather than pick one, GROUP BY is the honest tool. This handles the case where a source resends partial records and each copy has some fields populated.

select
  order_id,
  max(customer_id)                          as customer_id,
  max(amount)                               as amount,
  -- Coalesce across rows: keep the first non-null we see.
  max(coalesce(shipping_address, ''))       as shipping_address,
  min(created_at)                           as first_seen_at,
  max(updated_at)                           as last_updated_at,
  count(*)                                  as source_row_count
from raw.orders
group by order_id;

source_row_count is worth keeping. It costs nothing, and it lets downstream consumers and monitoring see how much duplication the source is producing without rerunning the diagnostic.

The catch: MAX per column merges values from different rows. If row A has amount = 100, status = 'pending' and row B has amount = 90, status = 'shipped', you get amount = 100, status = 'shipped' — a record that never existed. For additive or monotonic fields that is fine. For a record that must stay internally consistent, use strategy 3.

Strategy 3: ROW_NUMBER and QUALIFY

The workhorse. Pick one whole row per key by an explicit ordering, which keeps the surviving record internally consistent.

-- Snowflake, BigQuery, DuckDB: QUALIFY filters on the window result.
select *
from raw.orders
qualify row_number() over (
  partition by order_id
  order by
    updated_at desc,          -- primary rule: newest wins
    _loaded_at  desc,         -- tiebreaker: latest batch
    _file_row_number desc     -- final tiebreaker: guarantees determinism
) = 1;

-- Postgres and anywhere without QUALIFY.
select * from (
  select *,
    row_number() over (
      partition by order_id
      order by updated_at desc, _loaded_at desc, _file_row_number desc
    ) as rn
  from raw.orders
) t
where rn = 1;

The stacked ORDER BY is the part people skip and the part that matters. If updated_at ties — and with second-granularity timestamps from a batch load it will — the survivor is whichever row the engine happened to sort first. That varies with parallelism, so the same input produces different output across runs, your incremental model detects spurious changes, and any downstream diff is noise.

Rule I enforce in review: the ORDER BY inside a dedup ROW_NUMBER must be provably unique. End it with a column that cannot tie — a file row number, an ingestion sequence, a surrogate key, or worst case the primary key itself so at least the choice is stable.

Use ROW_NUMBER, not RANK. RANK emits ties, so rank() = 1 can return two rows and reintroduce the duplication you came to remove. The frame and ordering mechanics behind this are in the window frames deep dive.

Cost: one sort per partition key. Comparable to DISTINCT on a wide table and usually cheaper, because the sort key is the partition plus ordering columns rather than every column.

Strategy 4: MERGE at write time

The strategies above deduplicate on read. MERGE deduplicates on write, which is where it belongs if you control the load.

merge into fct_orders t
using (
  -- Dedup the batch first. MERGE errors when the source
  -- has multiple rows matching one target row.
  select * from staging.orders_batch
  qualify row_number() over (
    partition by order_id order by updated_at desc, _file_row_number desc
  ) = 1
) s
on t.order_id = s.order_id
when matched and s.updated_at > t.updated_at then update set
  t.customer_id = s.customer_id,
  t.amount      = s.amount,
  t.status      = s.status,
  t.updated_at  = s.updated_at
when not matched then insert
  (order_id, customer_id, amount, status, updated_at)
values
  (s.order_id, s.customer_id, s.amount, s.status, s.updated_at);

Two things to notice. The source is deduplicated before the merge, because a MERGE whose source has two rows for one target key fails on Snowflake and Postgres and non-deterministically updates on some others. And the when matched and s.updated_at > t.updated_at guard makes the merge idempotent — replaying an old batch cannot overwrite newer data with older data.

That guard is what makes backfills safe. Without it, re-running last week’s file rolls the table back to last week’s values, and you find out from a dashboard. This is the same idempotency property that makes incremental models trustworthy.

Cost: a join against the target plus a write. More expensive per run than a read-time dedup, and paid once instead of on every query. That trade is almost always worth it.

Strategy 5: dedup at the stream boundary

For streaming sources, the cheapest deduplication is the one that happens before the warehouse. Kafka consumers with an idempotent producer, a Flink keyed state with a TTL, or a Snowflake stream processed by a task that merges on a natural key.

-- Snowflake: consume a stream, dedup within the change set, merge.
create or replace task t_dedup_orders
  warehouse = load_wh
  schedule = '5 minute'
as
merge into fct_orders t
using (
  select * from stream_raw_orders
  where metadata$action = 'INSERT'
  qualify row_number() over (
    partition by order_id order by updated_at desc
  ) = 1
) s
on t.order_id = s.order_id
when matched and s.updated_at > t.updated_at then update set
  t.status = s.status, t.updated_at = s.updated_at
when not matched then insert (order_id, status, updated_at)
values (s.order_id, s.status, s.updated_at);

The important property is a bounded dedup window. Exactly-once across all history requires remembering every key ever seen, which grows without limit. Everyone in practice deduplicates within a window — five minutes, an hour, seven days — and accepts that a duplicate arriving outside it gets through. Choose the window deliberately and document it, because “exactly once” without a stated window is marketing. The mechanics of streams and tasks are in the streams and tasks guide.

Strategy 6: fix the key

The strategy nobody wants and half of these cases need. Sometimes the “duplicates” are two real events sharing a business key, because the key was never unique to begin with.

An order_id that repeats across source systems. A user_id that recycles after account deletion. A composite grain — one row per order per warehouse per day — being deduplicated to one row per order and quietly discarding real data.

-- Are the duplicates actually distinct at a finer grain?
select order_id, count(*) as n,
       count(distinct source_system)   as systems,
       count(distinct fulfillment_id)  as fulfillments
from raw.orders
group by order_id
having count(*) > 1
order by n desc
limit 50;

If systems or fulfillments is greater than 1, these are not duplicates. Deduplicating them destroys data. The fix is a composite key or a surrogate key, and a conversation with the source owner about what the grain actually is — the kind of thing a data contract exists to settle once.

I run this diagnostic before writing any dedup logic. It has saved me from silently dropping rows at least three times.

Picking the survivor

Once you know duplicates are real, the survivor rule needs to be written down. My defaults:

• Latest by event time, not ingestion time, when the source provides a reliable updated_at. Ingestion time reflects when your pipeline ran, which reorders on backfills.
• Latest by ingestion time when event time is missing or untrustworthy, with the caveat above documented.
• First seen for immutable facts — a payment that was retransmitted should keep its original record, not the retry.
• Most complete — count non-null columns and prefer the fullest row — when the source sends progressive enrichment. This needs a deterministic secondary sort too.

-- "Most complete row wins," with a stable tiebreaker.
select *
from raw.customers
qualify row_number() over (
  partition by customer_id
  order by
    (case when email      is not null then 1 else 0 end)
  + (case when phone      is not null then 1 else 0 end)
  + (case when address    is not null then 1 else 0 end) desc,
    updated_at desc,
    _file_row_number desc
) = 1;

Write the rule as a comment above the QUALIFY. Six months later somebody will ask why a specific record shows an old address, and the answer should be in the model, not in your memory.

Pitfalls

DISTINCT on rows that carry a load timestamp. The timestamp differs per copy, so nothing is removed and the query looks successful. Always check whether duplicates are identical first.

ROW_NUMBER without a unique tiebreaker. Non-deterministic output, spurious diffs in incremental models, and irreproducible bugs. End the ORDER BY with something that cannot tie.

Deduplicating before a join instead of at the source. Every downstream model repeats the sort. Dedup once in staging and let everything read the clean table.

MERGE with a duplicated source. Errors on some engines, silently picks a row on others. Always dedup the source subquery inside the MERGE.

Ignoring late-arriving duplicates in incremental models. A 7-day lookback window that dedups only within the current batch misses a duplicate that arrives on day 8. Dedup against the target table, not just the increment.

Treating a composite grain as a duplicate. Dropping real rows because one column of the key was omitted. Run the finer-grain diagnostic before you delete anything.

GROUP BY with per-column MAX on a record that must stay consistent. You synthesize a row that never existed. Use ROW_NUMBER to keep one whole row.

FAQ

Which strategy is fastest?

For identical duplicates, GROUP BY on the key is typically cheapest since the hash key is narrow. For differing duplicates, ROW_NUMBER costs one sort and beats DISTINCT * on any wide table. But read-time cost is the wrong metric — a MERGE at write time is cheaper than either, amortized over every query that no longer needs to dedup.

Is DISTINCT the same as GROUP BY on all columns?

Yes, on modern engines they compile to the same plan. Neither is the problem. The problem is using either one as a substitute for knowing your grain.

How do I dedup in an incremental dbt model?

Use merge as the incremental strategy with a unique_key, and dedup the incoming batch inside the model with QUALIFY before dbt builds the merge. Add a lookback window wider than your worst observed late arrival so replays actually reach the affected partitions.

What about deduplicating across two source systems?

That is entity resolution, not deduplication. Fuzzy matching on name, email, and address with a scored threshold and a human review queue. Do not attempt it with ROW_NUMBER on a business key that means different things in each system.

Should I dedup in staging or in marts?

Staging, once, so every downstream model inherits a clean grain and the sort is paid a single time. Marts should be able to assume their sources are unique at their declared grain, backed by a uniqueness test.

How do I prove a dedup is deterministic?

Run it twice on a frozen snapshot and hash-compare the output. If the hashes differ, your ORDER BY has a tie. This is a good CI check for any model whose survivor rule feeds financial reporting.

What this means for your pipelines

Deduplication is where a lot of teams accumulate quiet technical debt, because every individual fix works. A DISTINCT here, a ROW_NUMBER there, and eventually every model in the warehouse re-sorts the same table because nobody trusts the layer below.

Push it down instead. Dedup once, as early as you can — ideally at write time with an idempotent MERGE, otherwise once in staging — and put a uniqueness test on the output so downstream models can stop defending themselves. That single move removes sorts from dozens of queries and makes every grain claim in the DAG checkable instead of aspirational.

And separate the two questions permanently. “Which rows are duplicates” is a technical question with a query for an answer. “Which duplicate is true” is a business question, and encoding a guess in an ORDER BY without asking is how a customer ends up shipped to an address they moved out of two years ago. Write the rule down in the model, make the tiebreaker unique, and test that the output does not change when nothing upstream did.