A data engineer joins a large fact table to a small 8 MB dimension table but notices Spark ran a shuffle sort-merge join instead of a broadcast hash join. The setting spark.sql.autoBroadcastJoinThreshold is at its default. They want to understand the default behavior and how to force a broadcast. Which statement is correct?
Answer & explanation
Correct answer: C
WHY C: The default autoBroadcastJoinThreshold is 10 MB, so an 8 MB table should auto-broadcast; when statistics are missing and it does not, a /*+ BROADCAST(dim) */ hint forces the broadcast hash join. WHY NOT A: The default is 10 MB (10485760 bytes), not -1, so auto-broadcast is enabled by default rather than disabled. WHY NOT B: The value -1 disables auto-broadcast entirely; it does not force broadcasting of any table. WHY NOT D: shuffle.partitions controls the number of shuffle partitions, not whether a table is broadcast, so lowering it does not trigger a broadcast join. WHY NOT E: Broadcast hash joins work on both serverless and all-purpose compute; compute type does not restrict Spark to sort-merge joins.