Two rows are identical in every column; two others are identical only on the columns that identify one entity — why are those different defects?
answer
- two defects wear one word
- identical everywhere versus identical on identity
- one collapses freely, one loses a value
- the disagreeing columns are the finding
- count distinct rows against distinct identities
basics
~20 sRows identical in every column carry nothing you would lose by collapsing them. Rows identical only on the identifying columns disagree somewhere else, so collapsing one discards a fact and forces a decision about which row is right.
solid answer
~40 sThe same word covers two unrelated problems. **Identical in every column** means one record was emitted twice: neither copy holds anything the other lacks, so removing one is information-preserving and there is nothing to decide. **Identical only on the identifying columns** means two rows make competing statements about the same subject — same account, different balance. Removing one throws away a value, and which one survives changes the numbers downstream. So the first is an operation and the second is a decision: you must first say whether two rows per subject are legal at all, then state the rule that picks the winner, then decide what happens to the loser. Answering the wrong one of these is the commonest way to get this question wrong.
go deeper
Recall that duplicate covers two things: rows that match everywhere, and rows that match only on what identifies the subject. Say which one you mean before proposing anything, and say that the second involves a choice.
Explain why only the exact copy is safe to collapse without a stated rule, and show the three counts — rows, distinct whole rows, distinct identifying combinations — that tell you which defect is in front of you and how much of it there is.
Demonstrate that you would establish whether two rows per subject are legal before removing anything, since a legitimate second row means your identifying column set is too narrow rather than the data being broken.
The angle worth arguing is whether removing repeats inside a transform is a service to consumers or a way of hiding a producer's defect, and who pays when a silently cleaned table makes the upstream problem invisible for a year.
## One word, two defects A table can hold "the same row twice" in two senses that share almost nothing but the word people use for them. Getting them confused is the usual reason a de-duplication step either removes nothing or removes something it should not have. **Identical in every column.** Every value in the first row equals the corresponding value in the second, right across the record. Whatever produced the table emitted one record twice. Neither occurrence carries information the other lacks. **Identical only on the columns that identify one entity.** Two rows both describe account `4471`, and one reports a balance of 1,200 while the other reports 1,340. These are not copies of each other. They are two competing statements about one subject. Both get called duplicates. Only the first is a copy. ## Why the remedies are not the same shape Collapsing an exact copy is information-preserving. Whichever occurrence survives reconstructs the removed one exactly, so the question "which do I keep" has no content — there is nothing to choose between. Collapsing a disagreement is not. A value is going to be discarded, and the choice of which changes the answer. That makes it a decision rather than an operation, and it has to be made before anything runs: 1. **Decide whether two rows per subject are legal at all.** If the table is meant to hold one row per account, they are not, and the finding is a defect to report. If it is meant to hold one row per account per day, two rows for one account are perfectly legal and the real mistake is that you named too few identifying columns. 2. **Decide which of the disagreeing rows is right** — the most recent, the one from the more trusted source, the one whose status column says it is current — and state that rule out loud, because the operation is going to keep one of them whether or not you stated it. 3. **Decide what happens to the loser.** Discarded, kept with a flag on it, or sent back to whoever produced it. Only the exact copy skips all three steps. ## Telling them apart before you touch anything | | Identical in every column | Identical only on the identifying columns | |---|---|---| | What it says about the data | One record was delivered or written twice | Two records claim to describe the same thing | | Where it usually comes from | A retried load, a file processed twice, a producer that re-emits on restart | Late corrections, two source systems, an entity that legitimately has a history | | Cost of collapsing | Nothing is lost; any occurrence reconstructs the other | A value is discarded, and which one survives moves the numbers | | The work required | Choose the columns, run it | Decide legality, state the precedence rule, decide what happens to the loser | | What it inflates if you leave it | Any additive measure, by exactly the number of extra occurrences | Nothing predictable — it depends which row a later step happened to pick up | The cheap diagnostic is to count both: how many rows are there, how many distinct combinations of the identifying columns are there, and how many distinct whole rows are there. If the last two numbers are equal and both below the row count, everything you have is exact copies. If the number of distinct whole rows is higher than the number of distinct identifying combinations, you have disagreements too, and the gap between them is how many. ## The columns that identify an entity are a choice, not a given There is no column set that is inherently "the identity" of a record. A transactions table might be identified by an account plus a timestamp plus an amount, or by a transaction reference alone, and a table of daily balances is identified by an account plus a date, not by an account. Which you pick decides which of the two defects you are even able to see: - **Compare on every column** and you only ever find exact copies. Anything that disagrees anywhere — including on a load timestamp nobody cares about — survives untouched. - **Compare on the identifying columns only** and you find the disagreements too, but now every removal is a decision. Neither is the right default. The right move is to say what a row is supposed to mean in this table, name the columns that follow from that, and be explicit that the rest of the columns are values rather than identity. ## What an interviewer is listening for They are not testing whether you can name an operation. They are testing whether, shown a table with repeats in it, you would ask which kind before doing anything. A strong answer names both defects, says that only one of them is safe to collapse without a rule, and points out that which one you have is determined by the columns you chose to compare on — so the answer to "do we have duplicates" is always "on which columns?".
- You find rows identical on the identifying columns but disagreeing on one measure. What do you establish before writing any code?Whether two rows per subject are supposed to exist at all. If they are not, this is an upstream defect and the fix belongs with the producer. If they are — a corrected figure, a versioned record, one row per day — then your identifying column set is wrong, not the data, and widening it makes the disagreement disappear legitimately rather than by discarding a value.
- Which of the two defects can change a reported total, and in which direction?Both, differently. Exact copies inflate any additive measure by exactly the sum of the extra occurrences, so the error is always upward and is computable. Disagreements move the total in either direction depending on which occurrence a later step kept, and the size of the error is not recoverable from the result — you have to go back to the input to find out what it was.
- How do you count how many of each kind you have?Three numbers off the same table: the row count, the number of distinct whole rows, and the number of distinct combinations of the identifying columns. Row count minus distinct whole rows is the exact-copy surplus. Distinct whole rows minus distinct identifying combinations is how many extra rows exist that disagree with another row about the same subject.
Two photocopies of one filled-in form against two forms the same person filled in on different days. Shredding a photocopy costs nothing; shredding one of the two forms destroys an answer, and you had better know which day you meant to keep.
saying these in an interview costs you the question
- Says any two repeated rows can be collapsed, whichever occurrence you keep.
- Assumes every repeat is a redelivery, and never asks whether two rows per subject are legal.
- Defines a repeat on all columns without asking which columns identify the record.
- Treats disagreeing rows as a removal problem rather than a decision about which is right.
- Expects the operation to raise something when the two rows disagree.