AWS Training
Modules Listen Certification
Deduplication and top-N per group SQL0 — SQL Foundations for Data Engineering
0:00 0:00

← SQL Foundations for Data Engineering

Starts this lesson and continues through 1 more to the end of the course.

Deduplication and top-N per group

The two queries you will write forever

Every data engineer writes these two constantly:

  1. "Give me the latest row per key." — CDC snapshots, SCD current-state, last event per user.
  2. "Give me the top N per group." — top 3 products per category, 5 most recent orders per customer.

They're the same query. Both are solved with ROW_NUMBER(), and knowing that saves you from the self-join mess most people write first.

The pattern

-- Latest row per customer
SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC, event_id DESC   -- tie-breaker, see below
    ) AS rn
  FROM customer_events
) t
WHERE rn = 1;

Read the window clause as a sentence: restart the numbering for each customer_id, order the rows within that customer by updated_at descending, and number them. Row 1 is the latest. Filter to rn = 1.

For top-N, change one character: WHERE rn <= 3.

⚠️ You cannot filter on a window function in WHERE directly. Window functions are evaluated after WHERE — the query must be wrapped in a subquery or CTE. WHERE ROW_NUMBER() OVER (...) = 1 is an error. This is a standard interview question and the reason is the evaluation order from lesson 3.

The tie-breaker is not optional

This is the part that gets skipped and then causes a non-deterministic pipeline.

-- BAD: if two rows share the same updated_at, which one is rn = 1?
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC)

The answer is undefined. The engine may return either, and it may return a different one on each run — different plan, different parallelism, different result. You get a pipeline that produces slightly different output from identical input, which is one of the worst classes of bug to diagnose because it doesn't reproduce.

Always order by enough columns to be deterministic. Add a monotonic tie-breaker — a sequence, an event id, a file offset, an ingestion timestamp:

ORDER BY updated_at DESC, event_id DESC

If no such column exists, that is a real gap in your source data and worth raising, not papering over.

ROW_NUMBER vs RANK vs DENSE_RANK

Given scores 100, 90, 90, 80:

Function Result Use it when
ROW_NUMBER() 1, 2, 3, 4 You want exactly one row per group — dedup
RANK() 1, 2, 2, 4 Ties share a rank and consume the next positions
DENSE_RANK() 1, 2, 2, 3 Ties share a rank, no gaps

Dedup always uses ROW_NUMBER(). If you use RANK() and there's a tie, you get two rows back for rn = 1 and your "deduplicated" table still has duplicates — a delightful bug that only appears when real data contains a tie.

"Top 3 including ties" is RANK() <= 3. "Exactly 3 rows" is ROW_NUMBER() <= 3. Interviewers ask which you'd use and why; the answer is a question back — do you want exactly three, or everyone tied for third?

DISTINCT is not deduplication

SELECT DISTINCT customer_id, name, email FROM customers;

This removes rows that are identical across every selected column. If the same customer appears twice with a different updated_at, DISTINCT keeps both — and you still have duplicates by customer_id.

⚠️ SELECT DISTINCT is the standard wrong fix for fan-out (lesson 2) and for dedup. It answers "are these rows byte-identical?" which is almost never the question. The question is nearly always "which row is the current one per key?" — and that requires ROW_NUMBER(), because you have to choose.

The tell: if you can't say which row DISTINCT kept, you weren't deduplicating, you were hoping.

Alternatives worth knowing

QUALIFY — some engines support a clause that filters window results directly, removing the subquery:

SELECT * FROM customer_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

⚠️ QUALIFY is not in the PostgreSQL documentation and is not universally available. I have not verified which of Redshift, Athena/Trino, and Spark SQL support it — check your engine. The wrapped subquery form works everywhere, so prefer it for portable code and use QUALIFY only where you've confirmed support.

DISTINCT ON — a PostgreSQL extension that keeps the first row per group. Convenient, but it's a PostgreSQL extension and Redshift's documentation explicitly warns against assuming shared semantics (lesson 5). Don't reach for it in warehouse code.

Aggregate tricks — MAX(struct/row) or array_agg ... [1] patterns exist in some engines. They can be faster than a sort-based window, but they're engine-specific and harder to read. Optimise to them only with a measurement in hand.

Correctness checks for a dedup

Deduplication is exactly the kind of transformation that should be asserted, not trusted:

-- 1. The output must be unique by key. This must return zero rows.
SELECT customer_id, COUNT(*) FROM deduped GROUP BY customer_id HAVING COUNT(*) > 1;

-- 2. No keys were lost. These must be equal.
SELECT COUNT(DISTINCT customer_id) FROM customer_events;
SELECT COUNT(*)                    FROM deduped;

-- 3. Spot-check a key with known duplicates and confirm the right row won.
SELECT * FROM customer_events WHERE customer_id = '...' ORDER BY updated_at DESC;

Check 2 is the one people skip, and it catches the most damaging failure: a WHERE clause in the inner query that silently drops keys entirely. Dedup should reduce rows, never keys.

Redshift caveat: no NULLS clause in window functions

From Unsupported PostgreSQL features (verified 2026-08-12), the list of unsupported features includes:

"NULLS clause in Window functions"

So ORDER BY updated_at DESC NULLS LAST inside an OVER (...) is not available in Redshift. That matters directly here: if updated_at can be NULL, you need to control where those rows sort explicitly, for example by ordering on a coalesced expression or by adding a leading ORDER BY (updated_at IS NULL) term — verify the exact form against your engine before shipping it.

⚠️ Don't let a NULL sort to the top of your dedup ordering and silently win. A NULL updated_at becoming "the latest row" is a genuinely common production bug, and it produces a record with all the newest fields missing.

Check yourself

  1. Why can't you write WHERE ROW_NUMBER() OVER (...) = 1?
  2. You dedup with RANK() and still have duplicates. Why?
  3. What breaks if you omit a tie-breaker in the ORDER BY?
  4. Why is SELECT DISTINCT the wrong tool for "one row per customer"?
  5. Which check catches a dedup that silently dropped keys?
Answers
  1. Window functions are evaluated after WHERE in the logical order of operations, so the alias doesn't exist yet. Wrap it in a subquery or CTE and filter outside.
  2. RANK() gives tied rows the same rank, so a tie at the top produces two rows with rank 1. Dedup needs ROW_NUMBER(), which is always unique within a partition.
  3. The winning row becomes non-deterministic — the engine may pick a different row on each run depending on plan and parallelism. The pipeline then produces different output from identical input, and the bug doesn't reproduce on demand.
  4. DISTINCT removes rows identical across all selected columns. Two rows for the same customer with different timestamps are not identical, so both survive. It also can't express which row should win.
  5. Comparing COUNT(DISTINCT key) in the source against COUNT(*) in the output. Dedup should reduce rows but never lose keys.

Teaching this section

← PreviousCounting things correctlyNext →Redshift is not PostgreSQL