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.

• ✓

  Use a broadcast hint: SELECT /*+ BROADCAST(customers) */ * FROM orders JOIN customers ON orders.customer_id = customers.customer_id

  Why this is correct

  The broadcast hint tells Spark to broadcast the smaller 'customers' table to all executors, avoiding a shuffle of the large 'orders' table. This is the correct technique when one side of the join is small enough to fit in memory. Spark SQL supports the BROADCAST hint, and it is the standard way to eliminate shuffle for such joins. The hint must be placed immediately after SELECT.

• ✗

  Set spark.sql.autoBroadcastJoinThreshold to -1 to force broadcast joins.

  Why it's wrong here

  Setting autoBroadcastJoinThreshold to -1 disables broadcast joins entirely, which is the opposite of what is needed. The default threshold is 10MB; increasing it allows more tables to be broadcast. A value of -1 means no table will be broadcast, forcing a sort-merge join and shuffle. This would worsen performance for the small dimension table scenario.

• ✗

  Repartition both DataFrames on customer_id before joining.

  Why it's wrong here

  Repartitioning both DataFrames on the join key would co-locate the data but still requires a full shuffle of both datasets to redistribute rows. This does not eliminate the shuffle; it adds an explicit shuffle step. While it can improve subsequent joins, it is not the technique to avoid shuffle for a small-large join. Broadcasting the small table is more efficient.

• ✗

  Use a merge hint: SELECT /*+ MERGE(customers) */ * FROM orders JOIN customers ON orders.customer_id = customers.customer_id

  Why it's wrong here

  There is no MERGE hint in Spark SQL for joins. The MERGE hint is not a valid join strategy hint; Spark supports BROADCAST, MERGE, SHUFFLE_HASH, and SHUFFLE_REPLICATE_NL hints, but MERGE is used for merge joins (sort-merge) which still require a shuffle. Using an invalid hint may be ignored or cause an error. This does not eliminate the shuffle.