The full answer
A join multiplies rows when a key appears more than once on both sides: a key with 3 rows on the left and 4 on the right produces 12 rows. So the cause is almost always a key that is not unique where you assumed it was, or a join condition that is missing part of the key. Count rows per key on each side, find the keys that repeat, and fix either the data or the join.
1. Measure the fan-out
left_dups = left.groupBy("customer_id").count().filter("count > 1")
right_dups = right.groupBy("customer_id").count().filter("count > 1")
left_dups.orderBy(F.desc("count")).show(10)
right_dups.orderBy(F.desc("count")).show(10)
In the Spark UI's SQL tab, the join operator's "number of output rows" against its inputs shows the multiplication at a glance, even in a long job.
2. The usual causes
| Cause | Example |
|---|---|
| A dimension that is not one row per key | A customer table that keeps history, with a row per address change |
| A missing column in the join | Joining orders to prices on product_id but not on country or the date range |
| Duplicates loaded upstream | A rerun appended the same file twice |
| A placeholder key | Thousands of rows with customer 0 or UNKNOWN on both sides |
| Joining two fact tables | Orders joined to shipments on customer_id instead of order_id |
3. Fixes
- History tables: join on the key and the validity period, or filter to the current row first:
current = customers.filter("is_current = true") - Missing key columns: add every column that makes the match unique.
- Duplicate loads: fix the load so it is safe to rerun, and remove the duplicates at the source, not after the join.
- Placeholder keys: treat them as missing. Join only real keys, and handle the rest separately.
- Facts to facts: aggregate one side to the grain of the other before joining.
Only use dropDuplicates() after the join if you know why the duplicates exist. Removing them blindly hides a data problem and can drop rows that are genuinely different.
4. The opposite surprise: missing rows
NULL keys never match with ==, because comparing NULL with anything gives NULL, not true. If rows vanish in an inner join, check for NULL keys. If NULL should match NULL, use eqNullSafe:
left.join(right, left.region.eqNullSafe(right.region))
5. Stop it reaching production
Add checks to the pipeline: assert that a dimension has one row per key before it is used in a join, and compare the joined row count with the count of the driving table. Fail the job when the ratio jumps.
What interviewers want: the multiplication explained (3 times 4 is 12), counting rows per key on both sides, the common causes with fixes, and an automated check so the next one is caught before anyone sees the report.
Last updated 16 October 2026
Sources