Question 500
An analyst reports that a SQL query now takes several minutes after data volume grows. The change record shows Inspect the physical plan and build-side size estimates. The acceptance test concerns: A join unexpectedly uses an expensive sort-merge stage instead of an eligible broadcast join. Which outcome confirms it?
Explanation
Correct Answer
The acceptance criterion for “A join unexpectedly uses an expensive sort-merge stage instead of an eligible broadcast join” is The plan reveals join strategy and whether broadcast thresholds were met; this is the expected evidence after Inspect the physical plan and build-side size estimates.
Incorrect Answers
This signal belongs to the adjacent case “A query is delayed by a small set of long-running skewed tasks,” whose remedy is Use Spark UI or Query Profile partition/task timing to diagnose skew. The observed result for the present requirement is The plan reveals join strategy and whether broadcast thresholds were met.
That observation is a valid indicator for “One join stage has an unexpectedly large shuffle and dominates the critical path” when using Inspect Query Profile operators and shuffle bytes. The scenario instead tests “A join unexpectedly uses an expensive sort-merge stage instead of an eligible broadcast join”; the relevant result is The plan reveals join strategy and whether broadcast thresholds were met.
This signal belongs to the adjacent case “A scan reads far more rows than the WHERE clause should need,” whose remedy is Inspect filter pushdown and file/data skipping metrics in Query Profile. The observed result for the present requirement is The plan reveals join strategy and whether broadcast thresholds were met.