Question

A data engineer is joining a large fact table (billions of rows) with a small dimension table (about 20 MB) in a Spark SQL query. The job is running slowly because of heavy shuffling during the join. Which technique should the engineer use to eliminate the shuffle and improve performance?

Accepted Answer

Broadcast the small dimension table so it is sent to all executors and joined locally without shuffling the fact table

Answer

Repartition both tables by the join key so the data is co-located before joining

Answer

Apply a window function partitioned by the join key to avoid the join altogether

Answer

Cache the fact table in memory before executing the join