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 large tables, `orders` and `customers`, both stored as Delta tables in Microsoft Fabric. The `customers` table is relatively small (under 100MB) compared to the `orders` table (several TBs). To improve join performance, the engineer wants to ensure the smaller `customers` table is broadcast to all worker nodes. Which Spark SQL hint should be used?
- A/*+ MERGE */
- B/*+ BROADCAST */
- C/*+ SHUFFLE_HASH */
- D/*+ REPARTITION */
Show answer & explanationAnswer & explanation
Correct answer: B. /*+ BROADCAST */
The `BROADCAST` hint (also known as `BROADCASTJOIN` or `MAPJOIN`) explicitly instructs Spark to broadcast the specified table to all worker nodes. This is highly effective for joining a small table with a large table, as it avoids shuffling the larger table and significantly speeds up the join operation.
Why the other options are wrong
- A. The `MERGE` hint (or `MERGEJOIN`) suggests a sort-merge join, which is chosen when neither table is small enough for broadcasting, and both are large. It requires sorting both tables.
- C. The `SHUFFLE_HASH` hint suggests a shuffle hash join, which is used when one table is moderately sized and can fit in memory on each executor, but not small enough to be broadcast to all executors without risk of OOM errors. It requires shuffling both tables.
- D. The `REPARTITION` hint (or `REPARTITION_BY_COL`) suggests repartitioning the table by a specific column for better distribution, but it does not directly optimize a small-table-to-large-table join by broadcasting.
Spark SQL BROADCAST Hint
The `BROADCAST` hint in Spark SQL forces the specified table (usually the smaller one) to be broadcast to all worker nodes during a join operation, thereby optimizing performance by avoiding a shuffle of the larger table.
- Used for joining a small table with a large table.
- Table size threshold for broadcasting is configurable (default is 10MB).
- Significantly reduces network I/O and improves join speed.
- Can lead to OutOfMemory (OOM) errors if the broadcasted table is too large.
Memory trick: Broadcast small tables, shuffle large ones, merge if you have to!