Join strategies in Spark
Join output 40× the fact table
Symptoms
- Dimension has duplicate business keys
- No unique constraint
Join strategies in Spark interview questions
Interactive Spark interview questions on Join strategies in Spark. Same topic as /learn/spark/joins. Sort-merge shuffles both sides. Broadcast copies the small side. The wrong choice shuffles terabytes.
Question 1 of 3
beginner
Join strategies in SparkBroadcast join vs shuffle join — when do you pick each?
Answer it out loud, then reveal. Play steps through like the simulators.
Production scenario
Case 1 of 1 · symptoms
All questions on this page
Indexed as FAQ. Open any item if you prefer a list to Play.
beginnerBroadcast join vs shuffle join — when do you pick each?
Join strategies in Spark · tap to open the answer
Short: Broadcast if one side fits in memory on every executor. Shuffle (sort-merge) if both sides are large.
Detailed: Broadcast replicates the small table. SMJ partitions both by key and sorts. A wrong broadcast of a 'small' 10 GB table OOMs executors.
Common mistake: Broadcasting the fact table because 'joins should be broadcast'.
Follow-up: How do you force a broadcast in Spark SQL?
intermediateThe plan says SortMergeJoin but you expected broadcast. Why?
Join strategies in Spark · tap to open the answer
Short: Statistics said both sides were large, or AQE/broadcast threshold was below the build side.
Detailed: Check table stats, spark.sql.autoBroadcastJoinThreshold, and filters that Catalyst didn't push (so size is wrong). AQE can switch later if enabled.
Common mistake: Hints without checking stats.
Follow-up: What happens if stats are stale after a 10× load?
seniorHow do you debug a join that 'exploded' row counts?
Join strategies in Spark · tap to open the answer
Short: It's a many-to-many — duplicates on the key, not Spark magic.
Detailed: Count distinct keys vs rows on both sides before the join. Look for duplicate dimension keys. Spark UI won't show 'duplicates'; data profiling will.
Senior: SELECT key, COUNT(*) FROM dim GROUP BY 1 HAVING COUNT(*) > 1 — then the same on the fact.
Common mistake: Raising shuffle partitions to fix a row explosion.
Follow-up: What's the SQL test you'd run in the interview whiteboard?