Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data engineer is optimizing a Spark SQL query that involves joining two very large tables, `orders` (100 GB) and `customers` (500 MB), both stored in a Microsoft Fabric Lakehouse. The `customers` table is relatively small compared to `orders`. The engineer wants to ensure that Spark broadcasts the smaller `customers` table to all executors to speed up the join operation. Which join hint should the engineer use in the Spark SQL query?
- A/*+ REPARTITION(customers, 100) */
- B/*+ SORT_MERGE(customers) */
- C/*+ SHUFFLE_MERGE(customers) */
- D/*+ BROADCAST(customers) */
Show answer & explanationAnswer & explanation
Correct answer: D. /*+ BROADCAST(customers) */
The `BROADCAST` join hint explicitly tells Spark to broadcast the specified table to all executor nodes. This is highly effective when one of the tables in a join is significantly smaller than the other, as it avoids a costly shuffle of the larger table.
Why the other options are wrong
- A. `REPARTITION` changes the number of partitions for a DataFrame, unrelated to the join strategy hint itself.
- B. `SORT_MERGE` is another name for `SHUFFLE_MERGE`, indicating a shuffle merge join strategy.
- C. `SHUFFLE_MERGE` hints Spark to use a shuffle merge join, which is generally used for large tables and requires shuffling both tables.
Spark SQL BROADCAST Join Hint
A Spark SQL hint that forces the optimizer to use a broadcast hash join by broadcasting the specified table to all executor nodes.
- Ideal for joining a small table with a large table.
- Avoids data shuffling for the larger table.
- Can significantly improve join performance if the broadcasted table fits in executor memory.
Memory trick: Broadcast the small message to all for a fast meeting.