Remove Duplicates in a Data Pipeline

Duplicates are usually a property of how data is moved, not a bug in 1 job. A producer that times out and retries sends a message the consumer may already have received. An orchestrator reruns a failed task over a window that half loaded. A backfill overlaps the days a scheduled run already covered. A change data capture feed emits 1 row per change, so a table with 3 updates to 1 record yields 3 rows for it.

A join can create duplicates too, without any source repeating itself: joining on a key that isn't unique on the other side multiplies rows, and a later SUM silently counts them twice. That duplicate is fixed at the join, not by deduplicating the output, so find out which kind you have before you reach for a filter.

The 2 kinds you meet most are exact copies, where every column matches, and newer versions of the same key, where the key repeats but the values moved on. Both are deduplicated with the same query. What differs is whether you meant to keep the history.

The ORDER BY inside the window is the whole decision about which row wins, so write it as a rule you could defend: the newest updated_at, the highest version number, the row from the preferred source. Then ask whether 2 rows can tie on it. Order 103 can: its 2 paid rows both carry the update time 10:00, because the same event was loaded twice.

ROW_NUMBER gives tied rows different numbers anyway, and nothing tells you in what order. Unlike RANK, which gives peers the same number, it picks 1 of them, and a different pick on a rerun is a different table. Add a unique last column to the sort. Here that is loaded_at DESC, which makes the 11:05 load the winner for order 103 on every run. When no such column exists, a surrogate such as a file offset or an ingestion sequence number does the same job.

Test the sort for nulls as well. In most engines a descending sort puts nulls first or last by a default that differs between systems, and a row with a null updated_at can win by accident. State NULLS LAST when the column can be null.

If the question in an interview is to keep the latest record per key, this is the answer they want, and the follow-up is the tiebreaker. SQL window function interview questions cover the variants, and the SQL practice problems and SQL interview questions let you practice it on real data.

Look at the data before choosing. Order 101 has a placed row at 09:00 and a shipped row at 11:30. Those aren't copies: the second is the order's later state. Keeping the newest row is right for a table that answers what is the state now, and wrong for one that must answer what was the state at 10:00. A table that needs the second answer keeps both rows and models the history, with a validity range on each row, instead of deduplicating.

When the only duplicates are exact copies, SELECT DISTINCT is enough and cheaper, since it needs no sort inside each key. Reach for ROW_NUMBER when the key repeats and the other columns differ, or when you need a rule for the winner. A GROUP BY that takes MAX of each column can build a row that never existed, mixing the status of 1 version with the timestamp of another, so it's the wrong tool for choosing a record.

RANK and DENSE_RANK are not safe for deduplication. They give tied rows the same number, so filtering to 1 keeps every tied row. On the update time alone, RANK would keep 7 rows for 5 orders.

Deduplicate in more than 1 place, because each place catches duplicates the others cannot. A stream step catches repeats while they are cheap to spot, within a bounded window. The raw layer should keep what arrived, duplicates included, so you can replay and audit. The transform that builds the clean table removes duplicates with a rule, and a test after it proves the rule worked.

The ordering matters. Keep the raw layer append-only and deduplicate downstream of it, and a wrong rule is fixed by rerunning the transform. Deduplicate while writing the raw layer, and the discarded rows are gone for good. The same logic makes the transform itself repeatable: building the clean table from the raw table with a deterministic rule gives the same result on every run, which is the idea behind idempotent pipelines.

Pipeline design interviews ask exactly this: where duplicates enter, which layer owns the fix and how you would know. Data pipeline interview questions and data pipeline practice problems work through it with designs you draw.

An append-only incremental load turns every replay into duplicate rows. The dbt documentation says its append strategy doesn't check for duplicates, so a record that appears twice in the source is inserted twice. A merge strategy with a unique_key inserts new keys and updates existing ones, which makes a rerun overwrite instead of repeat. SQL upsert with INSERT ON CONFLICT and MERGE shows the statements.

A merge needs a clean batch. PostgreSQL states that INSERT ... ON CONFLICT DO UPDATE may not affect any single row more than once, and raises a cardinality violation when 2 proposed rows hit the same key. The Delta Lake documentation says the same of its merge: the new data must be deduplicated within itself. So the order is ROW_NUMBER on the incoming batch first, then merge the survivors in. Read the PostgreSQL INSERT documentation for the exact behavior.

Delta also shows how to bound the cost: an insert-only merge that matches the target only over the last 7 days looks for duplicates in that window instead of the whole table. That is correct only if a duplicate can never arrive later than the window, so choose it from how late your sources really replay.

A stream has no end to sort, so a streaming dedup remembers keys. The Spark Structured Streaming guide says the query stores the data it needs from previous records to filter duplicates, and that without a watermark it keeps all past records as state, with no bound. With a watermark on an event time column, and that column included in the deduplication columns, Spark drops the state of old records that aren't expected to get duplicates anymore.

When the event time differs between copies, as with a writer that stamps the time at write, the guide offers dropDuplicatesWithinWatermark. It deduplicates on the key alone within the watermark delay, and a delay of 1 hour deduplicates duplicates that occurred within 1 hour. It requires a watermark, and the guide advises setting the delay longer than the largest time gap between copies. Details are in the Structured Streaming deduplication guide.

Batch is different. dropDuplicates on a column subset removes repeats, but when the surviving row matters, rank with row_number as in the PySpark version.

Test the key on the clean table after every load. The cheapest check compares 2 counts: the table should hold 5 rows and 5 distinct order ids, and the load should fail when the counts differ. A unique test in dbt does the same, and a SQL assertion that selects keys with HAVING COUNT(*) > 1 returns the offending ids you need to debug.

Also compare input to output. 9 rows in and 5 rows out is a 4 row drop, and a drop far from the usual share is a signal: a sudden jump may mean an upstream replay, and a drop of 0 may mean the key changed. Track the removed count per load next to the row count, and a duplicate problem shows up as a trend before it shows up as a wrong number on a dashboard.

Last, check the key itself. If a deduplication on order_id is wrong because the true key is order_id plus line_number, it will quietly delete valid rows and every unique test will pass. Confirm the grain with the table's owner before the first run.

It's asked 2 ways. In the SQL round the prompt is a table with repeated rows, and the task is to keep the latest record per key, then to explain why RANK or DISTINCT would not do. In the system design round the prompt is a pipeline that must stay correct under retries, and the interviewer wants to hear where duplicates enter and which layer owns the fix.

A strong answer names the key and the ordering rule before writing any SQL, adds the tiebreaker without being asked, and says how the load stays correct on a rerun. Practice the query in the SQL round guide and the full set of SQL practice problems, and the design half in the data pipeline practice problems.