Deduplication

Keep exactly one row per business key

Raw feeds arrive dirty. A daily customer dump routinely carries the same email two or three times: one stale record plus a couple of updates. If you load all of them, every downstream join fans out, COUNT(DISTINCT email) stops matching COUNT(*), and a metric like revenue per customer silently double counts. Deduplication is the gate that guarantees exactly one row per business key before the data lands, and it has to keep the right row, usually the most recently updated one.

The pattern: rank inside, filter outside

The portable move is to number the rows within each key, then keep number one:

SELECT email, name, city, updated_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY email             -- one counter per business key
           ORDER BY updated_at DESC       -- newest row gets rn = 1
         ) AS rn
  FROM stg_customer
) ranked
WHERE rn = 1;

Read it as a grain change. PARTITION BY email restarts the counter for every distinct email; ORDER BY updated_at DESC decides who counts as "first"; WHERE rn = 1 keeps that single winner. Because ROW_NUMBER hands out unique integers inside each partition, you get exactly one row per key: never zero, never two.

Concretely, three raw rows (two for a@x.com, one for b@x.com):

email      name    updated_at
a@x.com    Ann     2024-01-01
a@x.com    Ann R.  2024-03-02
b@x.com    Bo      2024-02-10

collapse to one row per email, keeping each key's newest updated_at:

email      name    updated_at
a@x.com    Ann R.  2024-03-02
b@x.com    Bo      2024-02-10

The two a@x.com rows reduce to the March 2 update; b@x.com was already unique, so it passes through untouched.

Pitfalls

• DISTINCT is not dedup-by-key. SELECT DISTINCT only collapses rows that match on every selected column. Two rows with the same email but a different updated_at are not identical, so DISTINCT keeps both. Reach for ROW_NUMBER whenever "duplicate" means "same key, different attributes."
• A window function is illegal in WHERE. You cannot write WHERE ROW_NUMBER() OVER (...) = 1, because windows are computed after WHERE runs. Wrap the window in a subquery or CTE and filter the rn alias in the outer query.
• Unbroken ties are nondeterministic. If two rows share the same updated_at, the engine may pick either one as rn = 1, so a rerun can swap winners. Add a tiebreaker: ORDER BY updated_at DESC, source_row_id DESC. Now the same row wins every run, which is what keeps the load idempotent.
• NULL timestamps. In SQLite NULL sorts below every real value, so ORDER BY updated_at DESC naturally pushes missing timestamps to the bottom and any real updated_at beats a NULL one. (Postgres defaults the other way under DESC and needs an explicit NULLS LAST; SQLite gives you the behavior you want for free.)

In the warehouse: Snowflake and BigQuery let you skip the wrapper with QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1. SQLite and Postgres have no QUALIFY, so keep the subquery.

Interview nuance: know why it must be ROW_NUMBER and not RANK or DENSE_RANK. On a tie, RANK and DENSE_RANK assign the same number to both rows, so WHERE rank = 1 returns both and your duplicates come right back. Only ROW_NUMBER guarantees a strict 1, 2, 3 with no repeats, which is exactly the "one row per key" contract dedup depends on.

Execution mode: you write a multi-statement script against a pre-created target that may already hold rows from a previous run. Lead with DELETE FROM <target>;, then insert the deduped rows. The grader runs your script twice and checks the row count is unchanged, so a rerun must not double the data. Hidden assertions then verify the row count, which row won each key, and that zero duplicate keys remain.