Data engineering round
Pandas Join Fan-out & Deduplication Coding Problem
Written and reviewed by Sahil Srivastav
What this interview round tests
The most expensive data bugs are the ones that do not throw. A join between two tables at mismatched grain quietly multiplies rows, and every sum, count, and average downstream inflates. Nothing errors; the dashboard is just wrong, and it stays wrong until someone notices revenue looks 30% too high.
Interviewers use this because grain awareness separates people who have run pipelines from people who have only written queries. Knowing the grain of each table — one row per what? — and choosing keys that preserve it is the core skill, and a dedup applied to the wrong columns is a classic way to paper over the symptom while leaving the data corrupt.
The scenario
You are given a transform that enriches an orders table by joining reference and lookup tables in pandas. The output has more rows than there are orders, and a deduplication step meant to fix it is keying on the wrong subset, so it either removes legitimate rows or leaves duplicates behind.
The tests assert the output has exactly one row per order and that the enriched values are correct. Your job is to fix the join grain so the cardinality is right by construction, rather than trying to clean up an inflated result after the fact.
What you’ll practice
- Reasoning about table grain: one row per what, on each side of a join
- Diagnosing many-to-many fan-out and where the extra rows come from
- Choosing join keys that preserve cardinality
- Deduplication on the correct subset — and why post-hoc dedup is a smell
- Row-count and value assertions as a data-quality gate
How to approach it
Start from grain: state how many rows the output should have and what each table is keyed on, then find where expectation and reality diverge. Fan-out is a cardinality question — which side is not unique on the keys being joined — and a dedup bolted on after the fact is worth being suspicious of as a remedy.
Once the join is at the correct grain, the enriched values fall out for free. Treat the row-count assertion as the invariant and work backwards to the join that violates it.
The starter repository ships with a failing test suite and a bundled verify.sh. Reviewed reference solutions are part of Gronex Pro — this page stays spoiler-free on purpose.
FAQ
Is this just a pandas API quiz?
No. The bug is conceptual — join grain and cardinality — not a matter of knowing a specific method. The same reasoning applies to SQL joins and any dataframe library; pandas is just the medium.
Why is deduplicating the result the wrong fix?
Because a post-hoc dedup masks the symptom without addressing why rows multiplied, and it is fragile: dedup on the wrong subset drops real rows or keeps duplicates. Fixing the grain makes the cardinality correct by construction.