A fan out happens when you join to a table with multiple matching rows per key, so each left row is duplicated and any SUM over it is inflated. It is the most common cause of overstated revenue in a report. Spot it by counting rows before and after the join, or by checking whether the join key is actually unique on the right side before you aggregate.
Why interviewers ask this
This produces wrong numbers that look believable, which is the worst class of analytical bug, and it is usually only caught by someone in finance who knows the real total. Interviewers want a proactive detection habit rather than a definition, and ideally the pattern of pre aggregating the many side before joining.
How to structure your answer
- Explain the mechanism: one to many join duplicating left rows.
- Show why aggregates inflate rather than error.
- Give the detection habit: row counts before and after.
- Verify key uniqueness on the join side explicitly.
- Offer the fix: aggregate the many side first, then join.
Example answer
If I join orders to order items on order_id, and an order has four items, that order row now appears four times. Nothing errors, but if I then sum the order total, I have quadrupled revenue for that order. It is insidious because the number is plausible, just wrong, and the person who finds it is usually finance three weeks later. My habit is to count rows on the base table, run the join, count again, and if the number changed I need to be able to explain why. I also check uniqueness on the join key first with a quick group by and having count greater than one, which takes ten seconds. The fix is usually to aggregate the many side to the grain I want before joining, so I sum item revenue per order in a CTE and join that one row per order back to the header. That keeps the grain intact and makes the query easier to reason about.
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 you rewrite a fan out query to preserve the grain?
- Why is SELECT DISTINCT a bad fix for this?
- How would you detect this in a dashboard someone else built?
Related data analyst 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