Home / DE Professional practice test / Cost & Performance Optimisation / Question

Databricks Data Engineer Professional · free practice question

A PySpark job joins a 500 GB `transactions` fact table with a 2 GB `product_catalog` lookup table. The job is running slowly. A data engineer opens the Spark…

Exam-style question on Cost & Performance Optimisation from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Cost & Performance Optimisation Medium

A PySpark job joins a 500 GB transactions fact table with a 2 GB product_catalog lookup table. The job is running slowly. A data engineer opens the Spark UI query profile and observes: (1) A SortMergeJoin node is shown — not a BroadcastHashJoin. (2) Shuffle read/write metrics show ~500 GB of data being shuffled across executors. (3) Join task durations are highly skewed — most tasks complete in 2 seconds but 3 tasks take over 8 minutes. Which diagnosis and remediation is MOST accurate and complete?

  1. ASortMergeJoin is the optimal strategy for two tables of this size, and the long tasks come from Spark's default shuffle partition count. Set spark.sql.shuffle.partitions=2000 to spread the 500 GB of shuffled rows more evenly across the join tasks.
  2. BThe long tasks come from null values in the join key. Add WHERE product_id IS NOT NULL to both sides before the join, removing the null-key tasks, resolving the skew, and leading the optimizer to select BroadcastHashJoin for product_catalog.
  3. CSortMergeJoin is expected here, as BroadcastHashJoin applies to tables under a fixed 10 MB limit. The 8-minute tasks show executors running out of memory, so raise spark.executor.memory to 16g and keep the sort-merge strategy for the lookup table.
  4. DThe 500 GB shuffle shows the transactions table is being collected to the driver. Set spark.driver.maxResultSize=0 to lift the driver result limit, after which the optimizer switches the join to BroadcastHashJoin and the skewed tasks disappear.
  5. ESortMergeJoin shows the optimizer lacks size statistics for product_catalog or it exceeds the broadcast threshold; the long tasks show hot-key skew. Run ANALYZE TABLE or add a broadcast() hint, and enable AQE skew join with spark.sql.adaptive.skewJoin.enabled=true.
Answer & explanation

Correct answer: E

WHY E: This is the complete multi-factor diagnosis. (1) SortMergeJoin vs BroadcastHashJoin: for a 2 GB lookup table the optimizer should choose BroadcastHashJoin, which avoids shuffling the large table. Seeing SortMergeJoin means the optimizer either lacks up-to-date size statistics for product_catalog (stale or missing ANALYZE output, or a load path that bypasses statistics) or the size is above the broadcast threshold; without statistics the optimizer defaults to SortMergeJoin. The fix is ANALYZE TABLE product_catalog COMPUTE STATISTICS or an explicit broadcast(product_catalog) hint, which eliminates the 500 GB shuffle, the dominant cost. (2) Task skew: 3 tasks at 8 minutes against 2-second tasks is the classic data skew symptom, where certain join key values hold heavily concentrated rows (hot keys). AQE skew join optimization (spark.sql.adaptive.enabled=true and spark.sql.adaptive.skewJoin.enabled=true) detects skewed partitions at runtime and splits them into sub-partitions to balance task durations.

WHY NOT A: SortMergeJoin is NOT optimal when one join side is small enough to broadcast. More shuffle partitions reduce per-partition size and help somewhat with skew, but they do not eliminate the fundamental 500 GB shuffle.

WHY NOT C: The default spark.sql.autoBroadcastJoinThreshold is 10 MB but it is configurable, not fixed; with accurate statistics showing product_catalog is 2 GB, teams often raise the threshold or use hints. Also, OOM causes task failures and retries with error messages, not simply extra-long task durations.

WHY NOT D: spark.driver.maxResultSize limits the size of data returned to the driver from collect() / take() operations. It is unrelated to executor-to-executor shuffle volume and does not influence join strategy selection.

WHY NOT B: Null join keys CAN cause skew, but the scenario does not mention null values. More critically, filtering nulls would not change the SortMergeJoin selection or the 500 GB shuffle, which are driven by statistics and threshold settings, not null handling.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →