Home / DE Associate practice test / Troubleshooting, Monitoring, and Optimization / Question

Databricks Data Engineer Associate · free practice question

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…

Exam-style question on Troubleshooting, Monitoring, and Optimization from the free DE Associate practice test, with the answer and a full explanation below.

Updated November 10, 2026

Troubleshooting, Monitoring, and Optimization

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?

  1. AThe default value of spark.sql.autoBroadcastJoinThreshold is -1, which disables auto-broadcast entirely, so the 8 MB table gets shuffled; raise it to a positive byte value to let Spark broadcast the dimension table.
  2. BSetting spark.sql.autoBroadcastJoinThreshold to -1 forces Spark to broadcast any table regardless of size, so applying that value will convert the sort-merge join into the desired broadcast hash join in this case.
  3. CThe default threshold is 10 MB, so the 8 MB dimension should broadcast automatically; when it does not, a /*+ BROADCAST(dim) */ hint forces the broadcast hash join explicitly for this specific query at execution time.
  4. DBroadcast joins are governed by spark.sql.shuffle.partitions, whose default of 200 is too high here; lowering it below the dimension's row count makes Spark broadcast the small table instead of shuffling it across tasks.
  5. EBroadcast hash joins require an all-purpose cluster; this job runs on serverless compute, which only supports sort-merge joins, so moving the workload onto an all-purpose cluster is what enables the broadcast here.
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.

Take the full DE Associate practice test free →

Would you pass today?

This question is one of 45 in the free, timed Databricks Data Engineer Associate practice test. Take it and get a section-by-section score.

Start the free test →