Databricks-Spark-Assoc Using Spark SQL Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  SELECT /*+ MERGE(t1, t2) */ * FROM t1 JOIN t2

  Why it's wrong here

  The MERGE hint is specifically intended for merge joins, which are typically used when joining large datasets that are already sorted or partitioned. It does not instruct the optimizer to replicate small tables across the cluster, failing to address the specific performance bottleneck associated with joining small lookup tables against large ones.

• ✗

  SELECT /*+ SHUFFLE(t1, t2) */ * FROM t1 JOIN t2

  Why it's wrong here

  The SHUFFLE hint forces a shuffle-based join strategy, which is the exact opposite of what is required here. Forcing a shuffle when one table is small enough to fit in memory increases network traffic unnecessarily, resulting in significantly higher latency compared to an efficient broadcast join operation.

• ✓

  SELECT /*+ BROADCAST(t2) */ * FROM t1 JOIN t2

  Why this is correct

  The BROADCAST hint explicitly instructs the Spark catalyst optimizer to perform a broadcast hash join. By duplicating the smaller table to all executors, Spark eliminates the need for expensive wide transformations and data reshuffling, allowing the join to occur locally within each task, which maximizes performance for this specific scenario.

• ✗

  SELECT /*+ SKEW(t1) */ * FROM t1 JOIN t2

  Why it's wrong here

  The SKEW hint is used to handle data skew by splitting skewed partitions into smaller tasks. It does not improve performance for a simple small-to-large table join unless the large table itself contains highly unevenly distributed keys, making it irrelevant for this optimization task involving a small lookup table.