What Counts as a Duplicate
Concepts covered: Deduplication
The question rarely contains the word key. It arrives as 'this feed double-writes sometimes, clean it up', as 'the daily order count runs 3% above what finance reports', or as 'every join off this table fans out'. All 3 describe the same table, and none says which columns make 2 rows the same. A row in orders has 5 columns, and 2 rows can agree on all 5, on 2 of them, or on 2 of them only after a string is trimmed, so the word duplicate means nothing until a column list goes with it.
| order_id | customer_id | order_date | amount | created_at |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-01 | 100 | 2024-01-01 09:00:00 |
| 1002 | 1 | 2024-01-01 | 140 | 2024-01-01 10:00:00 |
| 1003 | 1 | 2024-01-01 | 110 | 2024-01-01 11:00:00 |
| 1004 | 1 | 2024-01-02 | 60 | 2024-01-02 08:15:00 |
| 1005 | 2 | 2024-01-01 | 75 | 2024-01-01 12:45:00 |
Match on every column and no 2 of these rows are the same: 5 distinct rows, nothing to remove. If customer_id and order_date decide the match, rows 1001 to 1003 are copies of one key, which leaves 3 rows. On customer_id alone, customer 1's 4 rows fold into 1, which leaves 2. The same table gives a different answer under each definition, and the interviewer has handed you none of them. The first thing you produce is the definition, because every query after it is scored against that choice.
Exact duplicates and duplicates by key
An exact duplicate agrees with another row in every column. It comes from a file loaded twice, a UNION ALL that should have been UNION, or a lookup table someone appended to instead of replacing. Nothing in the row tells the copies apart, so any copy can survive, and one keyword removes the rest. The question is answered in a line and the interviewer learns nothing from it.
A duplicate by key agrees in the columns that identify the entity and differs somewhere else. Rows 1001 to 1003 share a customer and a date, but order_id differs on each one, as do amount and created_at, because the feed wrote the order 3 times and stamped each write. Retries, change-data-capture replays after a failover, and corrections written as fresh rows all produce this shape. Removing these means deciding on a key and then on which copy survives, and almost every interview version of the question is this case, because it tests judgement rather than vocabulary.
The grain is the definition
The sentence worth saying out loud is the grain: what one row of the finished table stands for. 'I'll treat customer_id plus order_date as the key, so the result holds a single row for each customer on each date' takes 2 seconds for the interviewer to confirm or correct, and either response hands you the column list. If she answers that order_id is the business key, this table has no duplicates at all and the 3% overcount comes from a join downstream. That is a different problem, and you caught it before writing a line. Candidates who skip the sentence commit to a definition inside their SQL and discover it was the wrong one when the row count comes back.
Where the copies come from
- A client that retries on a timeout sends the same event twice, and the second copy arrives with a later receipt time
- A change-data-capture stream replays committed rows after a database failover
- A nightly file loaded twice because the first load's success message was lost
- A join on a column that was never unique, fanning each left row into several
- UNION ALL written where UNION was intended, keeping copies from both branches
- A correction written as a new row instead of an update, so old and new versions coexist
- Opens with the keyword
- Types SELECT DISTINCT * before asking a single question and gets every row back
- Switches to GROUP BY and loses the columns the interviewer asked to keep
- Spends the next 5 minutes recovering from a definition nobody agreed on
- Hears 'why did the count not change?' before saying anything scorable
- Opens with the definition
- Names the key and the grain in one sentence and waits for a nod
- Asks which copy survives when 3 rows share a key
- Counts what repeats before removing anything
- Writes the keep-one query once, with the right columns and no rewrite
About this lesson
This section is part of the Deduplication: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.