Deduplication Done Right: DISTINCT Isn't the Only Answer

Duplicate data is almost daily life in data engineering: a pipeline rerun imports twice, CDC pulls in several versions of the same record, JOIN fan-out copies rows. Many people reach for DISTINCT the moment they hear “dedupe” — but deduplication is far more than DISTINCT, and the kind a DE meets most often, DISTINCT genuinely can’t solve. This post first splits “duplicate” into two kinds so you can treat each correctly; and the fix for the second kind is the first real-world use of the previous post‘s window function.

First be clear: which kind of “duplicate” are you removing

There are actually two kinds of “duplicate”, with completely different fixes — confuse them and you’ll use the wrong tool:

Confusing these two is the most common deduplication mistake: applying DISTINCT to “multi-version” data, removing nothing (because every row really is different), and believing it’s deduplicated.

Identical rows: DISTINCT (and its true face)

The first kind is the simplest; DISTINCT or GROUP BY both work. But there’s a truth about DISTINCT you must know: it deduplicates “the whole row”, not “some column”.

SELECT DISTINCT user_id, status FROM events;
-- ⚠ this dedupes the "combination" (user_id, status)
-- not "dedupe user_id and bring status along" — a user with two statuses keeps two rows

Many people think DISTINCT user_id, status gives “one row per user”; in fact it keeps every distinct (user_id, status) combination. Also, DISTINCT is really just a special case of GROUP BY on every column — so when you want to “dedupe and count at the same time”, just use GROUP BY rather than DISTINCT wrapped in another layer.

One row per key: ROW_NUMBER deduplication

This is the deduplication a DE does every day: the same entity has several versions, and I want one (usually the latest). The standard fix is ROW_NUMBER() — use the previous post‘s window function to number each group, then keep number 1:

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM events
) t
WHERE rn = 1;   -- each user's latest row (per post 1: a window can't go in WHERE, so wrap it)

Three knobs answer three questions: PARTITION BY = “what counts as the same one” (user? order? email?), ORDER BY = “which row is the representative” (latest? largest amount?), rn = 1 = “keep the representative”. Swap these three and you can express almost any “one row per group” requirement.

Two additions:

• ROW_NUMBER vs RANK: ROW_NUMBER guarantees exactly one row per group (even on a tie in updated_at, it forces a pick); RANK keeps several rows on a tie. Deduplication needs uniqueness, so it’s almost always ROW_NUMBER — but beware that on a tie “which one it picks” is undefined, so make ORDER BY decisive (add an id DESC, say, to break ties).
• PostgreSQL has a shorter form, DISTINCT ON:

SELECT DISTINCT ON (user_id) *
FROM events
ORDER BY user_id, updated_at DESC;   -- per user_id, keep the first row by ORDER BY

DISTINCT ON is PostgreSQL-only and very concise, but it forces ORDER BY to start with those columns and isn’t portable across databases. For portability and flexibility (top N per group, say), ROW_NUMBER is the safe choice.

Reflections

The first step of deduplication is asking “what counts as the same row”

I’ve seen too many deduplication bugs whose root wasn’t syntax but never defining clearly what “duplicate” means. Is it identical whole rows? The same email counting as one person? Several updates to the same order? Without that definition, you pick the wrong tool — DISTINCT on multi-version data, or the wrong PARTITION BY key — and the result is entirely wrong, without an error. So before I dedupe now, I always answer two questions: “which columns being equal makes it the same row (→ PARTITION BY)”, “within the same row, which version to keep (→ ORDER BY)”. Get those two straight and the SQL practically writes itself.

DISTINCT isn’t the “dedupe by one column” you think it is

SELECT DISTINCT a, b dedupes the (a, b) combination, not “dedupe a and bring b along” — I’ve seen this misunderstanding countless times, and it’s the same kind of insidious bug: “runs, no error, just a few extra rows”. If you truly want “one row per a”, what you want was never DISTINCT; it’s ROW_NUMBER or DISTINCT ON. A tool whose name sounds right doesn’t mean it does what you think — which is what this series keeps taking apart: don’t be fooled by how it reads; look at what it actually does.

Which pipeline layer deduplicates matters more than how

Once you can write ROW_NUMBER deduplication, the more worthwhile question is “which layer should this deduplication happen in”. For the same duplicating data, do you dedupe on the fly at every query (paying the cost every time, and easy to miss), or dedupe cleanly at the landing/modelling layer so downstream receives data that’s already unique? I lean to the latter: treat deduplication as part of data cleaning and do it once, as far upstream as possible, rather than scattering it across every downstream query. It’s consistent with my attitude to NULL semantics and fan-out — fixing the mess once at the source beats patching it in every downstream. It also makes pipeline reruns idempotent: the dedup logic is explicit, and running it any number of times gives the same result.