Skip to content

Associate Developer for Apache Spark · Domain 4: Troubleshooting and Tuning Apache Spark DataFrame API Applications · 10% of the exam

Task 4.1: Implement performance tuning strategies & optimize cluster utilization, including partitioning, repartitioning, coalescing, identifying data skew, and reducing shuffling; Describe Adaptive Query Execution (AQE) and its benefits

Spotting skew and spill in the Spark UI, choosing repartition, coalesce or a broadcast, and what adaptive query execution coalesces, converts and splits at run time.

Study it

  • Skew, spill and shuffles: repartition, coalesce and broadcasting

    Partly covered by: Database Sharding

  • Adaptive query execution

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

On Databricks with adaptive query execution at its defaults, a job runs orders.groupBy("customer_id").agg(F.sum("amount")), with pyspark.sql.functions imported as F. One customer_id, a marketplace account, holds 40% of the rows, and in the aggregation stage one task runs far longer than the 75th percentile while the other cores sit idle. Which change spreads that customer's rows over many tasks?

  1. ANone: adaptive query execution splits the skewed partition of the aggregation by itself
  2. BAdd a REBALANCE hint before the groupBy so that Spark splits the skewed partition
  3. CAggregate by customer_id and a random salt bucket first, then sum the partial results by customer_id
  4. DAdd workers, so that more cores share the work of the slow task
Show the answer and why
  • ANone: adaptive query execution splits the skewed partition of the aggregation by itself

    Incorrect

    AQE's skew handling splits skewed tasks in sort merge joins and shuffle hash joins; this stage is an aggregation.

  • BAdd a REBALANCE hint before the groupBy so that Spark splits the skewed partition

    Incorrect

    REBALANCE splits skewed output partitions when the stage writes a table; it does not split a key's rows inside an aggregation.

  • CAggregate by customer_id and a random salt bucket first, then sum the partial results by customer_id

    Correct

    Appending a random bucket to a skewed key and aggregating in two stages spreads the key's rows over many tasks.

  • DAdd workers, so that more cores share the work of the slow task

    Incorrect

    With skew, one task does most of the work while the other cores sit idle, so more cores do not shorten that task.

Skew shows as one or a few tasks running much longer than the rest, with poor cluster utilization. AQE splits skewed partitions automatically only in sort merge joins and shuffle hash joins, so a skewed aggregation needs a code change: salt the skewed key with a random bucket suffix, aggregate by key and bucket, and aggregate the partial results by key. The REBALANCE hint addresses skewed output partitions of a write.

Question 2 · choose 1

On Databricks with adaptive query execution at its defaults, users.join(clicks, "user_id", "left") runs as a sort merge join. One partition of clicks, holding a bot account's rows, is large enough for AQE to treat it as skewed, and its task runs far longer than the others. What does AQE do about that partition?

  1. ANothing: in a LEFT OUTER join AQE optimizes skew only on the left side, and the skew is in clicks
  2. BIt splits the skewed clicks partition into roughly evenly sized tasks
  3. CIt converts the join to a broadcast hash join of clicks, which removes the skew
  4. DIt splits the partition, but only after the join is rewritten as a FULL OUTER join
Show the answer and why
  • ANothing: in a LEFT OUTER join AQE optimizes skew only on the left side, and the skew is in clicks

    Correct

    For a LEFT OUTER join AQE optimizes skew on the left side only, and clicks is the right side of this join.

  • BIt splits the skewed clicks partition into roughly evenly sized tasks

    Incorrect

    The join type decides which side AQE can optimize, and in a LEFT OUTER join it is not the right side.

  • CIt converts the join to a broadcast hash join of clicks, which removes the skew

    Incorrect

    clicks is the large, skewed side; AQE's switch to a broadcast join applies to a small side, and broadcast joins are never skew-optimized.

  • DIt splits the partition, but only after the join is rewritten as a FULL OUTER join

    Incorrect

    For a FULL OUTER join AQE optimizes skew on neither side.

AQE handles skew only in shuffle-based joins, sort merge and shuffle hash joins, by splitting skewed tasks into roughly evenly sized ones. The join type decides which side can be optimized: both sides for INNER and CROSS, the left side for LEFT OUTER, LEFT SEMI and LEFT ANTI, the right side for RIGHT OUTER and neither side for FULL OUTER. In users LEFT OUTER JOIN clicks, the skewed clicks partition is on the right, so AQE leaves it as it is.

Question 3 · choose 1

On Databricks, a Structured Streaming query joins two streams, clicks and impressions, on ad_id with an inner join and watermarks on both sides. One ad_id is far more frequent than the others, and in every micro-batch the join stage has one task that runs much longer than the rest. Adaptive query execution and its skew join handling are turned on, as they are by default. Why is the long task not split?

  1. AThe join has no exchange, so adaptive query execution has nothing to re-optimize
  2. BAdaptive query execution applies only to queries that are not streaming queries
  3. CAdaptive query execution re-optimizes a query only after it finishes, and a streaming query never finishes
  4. DSkew join handling covers broadcast hash joins only, and this join shuffles both sides
Show the answer and why
  • AThe join has no exchange, so adaptive query execution has nothing to re-optimize

    Incorrect

    A join usually produces an exchange, which meets that condition; the query is excluded for another reason.

  • BAdaptive query execution applies only to queries that are not streaming queries

    Correct

    AQE applies only to queries that aren't streaming queries and that contain at least one exchange or subquery.

  • CAdaptive query execution re-optimizes a query only after it finishes, and a streaming query never finishes

    Incorrect

    AQE re-optimizes while a supported query runs, at the end of each shuffle or broadcast exchange.

  • DSkew join handling covers broadcast hash joins only, and this join shuffles both sides

    Incorrect

    Skew is handled in sort merge and shuffle hash joins, which are shuffle-based; broadcast joins are never skew-optimized.

Adaptive query execution applies to queries that meet two conditions: the query isn't a streaming query, and it contains at least one exchange (usually from a join, aggregate or window) or a subquery. In supported queries AQE re-optimizes the plan at the end of each shuffle or broadcast exchange, for example by splitting skewed partitions of a sort merge or shuffle hash join. A streaming query is excluded, so the skew join setting does not change its micro-batches.

Question 4 · choose 1

On classic compute with Databricks Runtime 15.4 LTS, the jobs timeline of a pipeline shows long gaps between Spark jobs, and the team suspects that the driver itself is overloaded. Where does the Spark UI guide say to check this?

  1. AIn the Storage tab of the Spark UI, by comparing cached sizes
  2. BIn the compute's Metrics, in the Server load distribution visualization
  3. CIn the SQL tab, by counting the queries that ran
  4. DIn the executor logs, by searching for out-of-memory errors
Show the answer and why
  • AIn the Storage tab of the Spark UI, by comparing cached sizes

    Incorrect

    The Storage tab lists persisted RDDs and DataFrames; it does not show how loaded the driver machine is.

  • BIn the compute's Metrics, in the Server load distribution visualization

    Correct

    On Databricks Runtime 13.0 and above, the compute's Metrics show a Server load distribution block per machine, red for heavy load.

  • CIn the SQL tab, by counting the queries that ran

    Incorrect

    The SQL tab shows queries and their plans, not the load of the driver machine.

  • DIn the executor logs, by searching for out-of-memory errors

    Incorrect

    Executor logs are written by the executors; the question is whether the driver is overloaded.

Gaps in the jobs timeline mean no Spark job is running, usually with idle workers. Among the causes is an overloaded driver. To check it, open the compute's Metrics (Databricks Runtime 13.0 and above) and look at the Server load distribution visualization, which shows a block of color per machine: red means heavy load, blue means no load.

Question 5 · choose 1

A complex batch ETL job on classic compute joins several large tables, and most of its time goes to moving data between machines. According to the Databricks Spark UI guide, which worker configuration reduces the amount of data shuffled?

  1. AMany more, smaller workers with the same total memory
  2. BThe same workers with autoscaling turned on
  3. CFewer, larger workers
  4. DA larger driver with the same workers
Show the answer and why
  • AMany more, smaller workers with the same total memory

    Incorrect

    The guide suggests the opposite shape for joins across multiple tables.

  • BThe same workers with autoscaling turned on

    Incorrect

    Autoscaling changes the number of workers over time; it is not the guide's way to reduce shuffled data.

  • CFewer, larger workers

    Correct

    For complex batch ETL with joins across multiple tables, the guide recommends fewer, larger workers to reduce the amount of data shuffled.

  • DA larger driver with the same workers

    Incorrect

    The shuffle moves data between executors on the workers; the driver size does not change it.

The Skew and spill page lists ways to keep more data in memory, including instances with more memory, and for complex batch ETL with joins across multiple tables it recommends fewer, larger workers to reduce the amount of data shuffled between machines.

Databricks documentation

Question 6 · choose 1

Which adaptive query execution feature lets Databricks skip work for parts of a query that turn out at runtime to return no rows?

  1. ADynamic detection and propagation of empty relations
  2. BDynamic reordering of joins by their runtime sizes
  3. CDynamic switching from sort merge join to broadcast hash join
  4. DDynamic handling of skew in sort merge and shuffle hash joins
Show the answer and why
  • ADynamic detection and propagation of empty relations

    Correct

    AQE dynamically detects and propagates empty relations, which are parts of a query that return no rows.

  • BDynamic reordering of joins by their runtime sizes

    Incorrect

    AQE doesn't reorder joins at runtime; a better join order comes from table statistics for the cost-based optimizer.

  • CDynamic switching from sort merge join to broadcast hash join

    Incorrect

    This changes the join strategy based on runtime sizes; it is a different feature.

  • DDynamic handling of skew in sort merge and shuffle hash joins

    Incorrect

    Skew handling splits large partitions; it does not deal with empty relations.

Among the features of AQE, it changes sort merge joins into broadcast hash joins, handles skew in sort merge and shuffle hash joins, and dynamically detects and propagates empty relations, parts of a query that return no rows; it does not reorder joins at runtime. In a plan, empty relation propagation shows as a LocalTableScan node with an empty relation field replacing part or all of the plan.

Practice domain 4 →Practice all domains →