Establish the grain first, then quantify the duplication: group by the intended key and count rows above one. Determine whether the duplicates are identical (a load problem) or differ in some column (a join fan out or a source level duplicate). Trace back layer by layer to find the first place duplication appears, fix at that layer, then rebuild affected partitions and add a uniqueness test.
Why interviewers ask this
Duplicate revenue is the classic warehouse bug, and interviewers want a methodical investigation rather than a guess. The key discriminator is whether you check the layer by layer origin instead of slapping a DISTINCT on the final query, and whether you finish by adding a test so the same defect cannot silently return.
How to structure your answer
- Define the intended grain and quantify how many keys are duplicated.
- Compare duplicate rows to see if they are identical or divergent.
- Walk back through layers to find where duplication first appears.
- Fix at the origin rather than deduplicating at the end.
- Add a uniqueness test and rebuild affected history.
Example answer
First I write down what the grain is supposed to be, one row per order line for example, then count keys with more than one row so I know whether this is twelve rows or twelve percent. Then I compare a few duplicate sets side by side, because that tells you the cause almost immediately. If the rows are byte identical, it is a load issue, usually a retried job appending instead of replacing. If they differ in one column, it is a join fan out, which is what it was the last time I saw this: a dimension had gone type 2 and the join did not filter to the current version, so every fact row matched three historical dimension rows and revenue tripled for affected customers. I fix it at the layer where it first appears, not with a DISTINCT in the final model, because that hides the problem and slows every query. Then a uniqueness test on the grain, and a rebuild of affected partitions so finance gets corrected numbers.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How would a join to a type 2 dimension cause this exactly?
- Why is deduplicating in the final model a bad fix?
- How would you tell finance that historical numbers will change?
Related data engineer questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions