Join output 40× the fact table
Symptoms
- Dimension has duplicate business keys
- No unique constraint
Interactive Spark interview questions on Join strategies in Spark. Practice with the matching lesson. Sort-merge shuffles both sides. Broadcast copies the small side. The wrong choice shuffles terabytes.
Question 1 of 8
Broadcast join vs shuffle join — when do you pick each?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 3 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 8 of 8 questions.
Broadcast join vs shuffle join — when do you pick each?
Join strategies in Spark · tap to open the answer
Say this first: Broadcast if one side fits in memory on every executor. Shuffle (sort-merge) if both sides are large.
Why: 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 they will ask: How do you force a broadcast in Spark SQL?
The plan says SortMergeJoin but you expected broadcast. Why?
Join strategies in Spark · tap to open the answer
Say this first: Statistics said both sides were large, or AQE/broadcast threshold was below the build side.
Why: 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 they will ask: What happens if stats are stale after a 10× load?
How do you debug a join that 'exploded' row counts?
Join strategies in Spark · tap to open the answer
Say this first: It's a many-to-many — duplicates on the key, not Spark magic.
Why: 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 depth: 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 they will ask: What's the SQL test you'd run in the interview whiteboard?
List the join strategies Spark can pick and say what rules each one out.
Join strategies in Spark · tap to open the answer
Say this first: Broadcast hash, shuffle hash, sort-merge, and the nested-loop fallbacks — size, hints, and whether there is an equi-key decide which survives.
Remember these
Why: For an equi-join, the planner prefers BroadcastHashJoin when one side is hinted or its estimated size is under spark.sql.autoBroadcastJoinThreshold, because replicating the small side removes the shuffle on the big one. If neither side is broadcastable, it considers a shuffle hash join when one side is substantially smaller and spark.sql.join.preferSortMergeJoin allows it, then falls back to SortMergeJoin, which shuffles and sorts both sides and is the safe general case. Without an equality condition there is nothing to hash or sort on, so you get BroadcastNestedLoopJoin or a Cartesian product, and seeing either of those in a plan for a join you believed was keyed means your condition is not an equi-condition. AQE can revise the choice at runtime once real shuffle sizes are known, which is why the plan you read before execution may not be the plan that ran.
Common mistake: Naming only broadcast and sort-merge, then being stuck when the plan shows BroadcastNestedLoopJoin.
Follow-up they will ask: What in your SQL would produce a BroadcastNestedLoopJoin by accident?
You put a broadcast hint on the left side of a LEFT OUTER JOIN and the plan ignores it. Why?
Join strategies in Spark · tap to open the answer
Say this first: For a left outer join the left side must stream, so only the right side is a legal build side.
Remember these
Why: A hash join streams one side and probes a hash table built from the other. Outer semantics constrain which is which: a LEFT OUTER JOIN must emit every left row exactly once, including unmatched ones, and that is only possible if the left side is the streaming side and the right side is the build side. So Spark can broadcast the right side of a left outer join and the left side of a right outer join, and for a FULL OUTER JOIN neither side qualifies for a broadcast hash join at all. A hint that violates this is not an error — the planner simply cannot honour it and falls back to SortMergeJoin, which is why your hint vanished silently. If the small table really is on the left, flip the join to a RIGHT OUTER JOIN with the sides swapped, or restructure to an inner join plus a separate anti-join if the semantics allow.
big.join(F.broadcast(small), "customer_id", "left") # legal: small is the right side
small.join(F.broadcast(big), "customer_id", "left") # hint ignored, falls back to SMJCommon mistake: Concluding the hint syntax is wrong when the join type is what made it impossible.
Follow-up they will ask: Your plan shows BroadcastHashJoin but the build side is the table you thought was huge. What should you check?
Both sides of your join have about 30% NULL join keys and one task runs forever. Explain the mechanism and the fix.
Join strategies in Spark · tap to open the answer
Say this first: NULLs all hash to the same shuffle partition, so a third of both tables lands on one reducer doing work that can never match.
Remember these
Why: Partition assignment is the hash of the key, and every NULL hashes identically, so all NULL-keyed rows from both sides converge on one reducer. Under SQL equality semantics NULL never equals NULL, so none of those rows can produce an inner-join match — the reducer sorts and merges a third of your data to emit nothing. The Spark UI shows one task with enormous Shuffle Read and heavy Spill (Disk) while the rest of the stage finishes quickly. For an inner join the fix is a filter on both sides before the join, which also lets the optimizer push a null-safety predicate into the scan. For an outer join you cannot drop them, so handle the NULL population separately: join the non-null rows, then union back the null-keyed rows with the outer columns filled as nulls. Sentinel values like 'UNKNOWN' or -1 behave exactly the same way and are easy to miss because they do not look like nulls.
matched = left.where("customer_id IS NOT NULL").join(right.where("customer_id IS NOT NULL"), "customer_id", "inner")
unmatched = left.where("customer_id IS NULL")
result = matched.unionByName(unmatched, allowMissingColumns=True)Senior depth: Group the join key and look at the top of the frequency distribution before you join. A single key holding double-digit percent of the rows is the tell, whether it is NULL, an empty string, a zero, or a placeholder the source system has used since 2014. Build that distribution check into the pipeline as an assertion, because the sentinel always shows up in a load long after the join was written.
Common mistake: Calling it generic skew and salting the key, which spreads NULLs that still cannot match anything.
Follow-up they will ask: How do you find a sentinel value acting like a null without knowing it is there?
AQE skew join handling is enabled and a skewed task is still running an hour behind. Why might it not have fired?
Join strategies in Spark · tap to open the answer
Say this first: Skew splitting fires only when a partition clears both the median-multiple and the absolute-size thresholds, on a join shape it is allowed to split.
Remember these
Why: AQE classifies a post-shuffle partition as skewed only if it is larger than spark.sql.adaptive.skewJoin.skewedPartitionFactor times the median partition size and larger than spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes. Both conditions must hold, so a uniformly fat shuffle where every partition is huge has no skew relative to the median and is left alone. Beyond the thresholds, it needs spark.sql.adaptive.enabled and skewJoin.enabled, it only applies to join shapes it can split — splitting one side requires replicating the matching partition of the other, which outer join semantics can forbid — and it cannot help at all if the skew is in a groupBy rather than a join. The confirmation to look for is the word 'skewed' on the AQE shuffle read node in the SQL tab, or a post-shuffle task count higher than the configured partition count.
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionFactor", "5")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "256MB")Senior depth: Salt the hot key manually: append a random bucket suffix to the key on the large side, explode the small side across the same bucket range, join on the composite key, and drop the salt afterwards. It is more code and it changes the join key, so it needs a test that proves row counts and sums are unchanged. Reach for it only after confirming the key really is a single hot value rather than a wide fat tail.
Common mistake: Treating 'AQE is on' as proof that skew is handled, without checking the plan for a skewed shuffle read.
Follow-up they will ask: What do you do when AQE legitimately cannot split it?
Two 4 TB fact tables are joined on customer_id every hour. Design the storage so the join stops shuffling.
Join strategies in Spark · tap to open the answer
Say this first: Bucket both tables on customer_id with the same bucket count and sort within buckets, so the join reads pre-partitioned data and skips the Exchange.
Remember these
Why: Bucketing writes each table into a fixed number of hash buckets on the join key, recorded in the table metadata. When both sides are bucketed on the same column with the same bucket count, the planner knows the required distribution is already satisfied and the SortMergeJoin appears with no Exchange underneath — you paid the shuffle once at write time instead of once an hour. Sorting within buckets at write time also lets the merge skip its sort. The costs are real: bucketing requires metastore-managed tables written through saveAsTable rather than raw paths, the bucket count is effectively permanent because changing it means rewriting both tables, writes get more expensive, and a hot customer_id is still a hot bucket so skew survives untouched. Spark can also join tables whose bucket counts differ by a multiple when spark.sql.bucketing.coalesceBucketsInJoin.enabled is set, which buys back some flexibility.
(fact.write
.bucketBy(512, "customer_id")
.sortBy("customer_id")
.mode("overwrite")
.saveAsTable("curated.fact_bucketed"))Senior depth: Accept the shuffle and shrink what gets shuffled: pre-aggregate or narrow one side so the Exchange moves gigabytes rather than terabytes, cluster on the join key so scans prune hard, and consider whether an hourly incremental merge into a maintained wide table replaces the join entirely. Re-joining two full 4 TB facts every hour is usually a pipeline-shape problem, not a join-strategy problem.
Common mistake: Pitching bucketing as a free win without mentioning that the bucket count is a long-term commitment.
Follow-up they will ask: What would you choose instead on a Delta lakehouse where bucketing is awkward?