When joining a large fact table with a small dimension table in Spark, which technique reduces network I/O and avoids expensive shuffles?

When joining a large fact table with a small dimension table in Spark, which technique reduces network I/O and avoids expensive shuffles?

• A. Using a shuffle-hash join
• B. Using a sort-merge join
• C. Using a broadcast join
• D. Using a nested loop join

Answer: (C)

Broadcasting the small dimension table to every executor allows a map-side join, so each worker can join its portion of the large fact table locally without shuffling the big data across the cluster. This cuts network I/O dramatically and avoids the expensive shuffles that occur when redistributing the large table for a different join strategy. It works best when the small table fits in memory on each executor, and Spark can auto-broadcast it (spark.sql.autoBroadcastJoinThreshold) or you can explicitly broadcast it with a hint. In contrast, shuffle-based approaches like shuffle-hash and sort-merge involve moving and repartitioning large data across the cluster, and a nested loop join would be inefficient for big datasets.

Broadcasting the small dimension table to every executor allows a map-side join, so each worker can join its portion of the large fact table locally without shuffling the big data across the cluster. This cuts network I/O dramatically and avoids the expensive shuffles that occur when redistributing the large table for a different join strategy. It works best when the small table fits in memory on each executor, and Spark can auto-broadcast it (spark.sql.autoBroadcastJoinThreshold) or you can explicitly broadcast it with a hint. In contrast, shuffle-based approaches like shuffle-hash and sort-merge involve moving and repartitioning large data across the cluster, and a nested loop join would be inefficient for big datasets.