Databricks-DA-Assoc Executing Queries with Databricks SQL Practice Question
A data analyst runs a query in Databricks SQL that joins a large fact table to a small dimension table. The query spills to disk and is slow. The analyst adds a /*+ BROADCAST(dim) */ hint to the query. What is the primary effect of this hint on the query execution?
⚠ Common exam trap
The trap here is assuming that the BROADCAST hint partitions or sorts the large table, when it actually replicates the small table to avoid shuffling the large one.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
It forces the optimizer to broadcast the dimension table to all executor nodes, avoiding a shuffle of the large fact table.
The BROADCAST hint tells the optimizer to replicate the small dimension table to all executors, so the join with the large fact table can be done locally without shuffling the fact table. This reduces network traffic and avoids disk spills, making the query faster. The hint is effective when the broadcasted table is small enough to fit in memory.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
It forces the optimizer to broadcast the dimension table to all executor nodes, avoiding a shuffle of the large fact table.
Why this is correct
The BROADCAST hint instructs Spark to replicate the small dimension table to every executor, so the join can be performed locally without shuffling the large fact table. This reduces network I/O and often eliminates disk spills, improving performance. The hint is appropriate when one side of the join is small enough to fit in memory.
- ✗
It caches the dimension table in Delta cache to speed up subsequent reads.
Why it's wrong here
Delta cache caches data on local disks of executor nodes to accelerate reads, but the BROADCAST hint does not control caching. Caching is managed separately, for example by using the CACHE TABLE command. The hint affects the join strategy, not the caching layer.
- ✗
It partitions the fact table by the join key before the join to co-locate matching rows.
Why it's wrong here
Partitioning the fact table by the join key is a technique to avoid shuffling, but the BROADCAST hint does not do this. Instead, it replicates the small table. Partitioning the large table would require a shuffle and is not the purpose of the hint. The hint specifically targets the small table for broadcast.
- ✗
It sorts both tables by the join key before merging them, ensuring a sort-merge join.
Why it's wrong here
A sort-merge join requires sorting both sides, which is expensive for large tables. The BROADCAST hint does not enforce sorting; it enables a broadcast hash join, which avoids sorting altogether. Sort-merge join is a different strategy that the optimizer might choose when both sides are large.
About these practice questions
This Databricks-DA-Assoc question is part of Courseiva's 291-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-DA-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-DA-Assoc exam.