Troubleshooting and Tuning · Topic 1 of 2

Performance Tuning, Partitioning, Skew and AQE

Spark tuning for the Databricks exam: repartition versus coalesce, shuffle partitions, spotting data skew, broadcast joins and how AQE adapts plans.

5 min
Includes a diagram

In a nutshell

One partition is processed by one task, so partition count sets the parallelism.
repartition always shuffles; coalesce only decreases partitions and avoids a full shuffle.
spark.sql.shuffle.partitions defaults to 200; spark.sql.files.maxPartitionBytes defaults to 128 MB.

What it is

Tuning means getting the same result with less time and less memory. In Spark that mostly comes down to how many partitions the data has, how evenly the rows spread across them, and how often data must move between executors in a shuffle.

Adaptive Query Execution (AQE) automates part of this. It re-plans a query while it runs, using the real sizes of the intermediate data.

How it works

Partitions and cluster utilization

A partition is the unit of parallel work: one partition is processed by one task. Too few partitions leave cores idle. Too many make tiny tasks whose scheduling overhead dominates.

File sources are split by spark.sql.files.maxPartitionBytes (128 MB by default). After a shuffle, the partition count comes from spark.sql.shuffle.partitions (200 by default). Check the current count with df.rdd.getNumPartitions().

repartition versus coalesce

	repartition	coalesce
Direction	Increase or decrease	Decrease only
Shuffle	Always a full shuffle	No full shuffle, merges neighbors
Result	Roughly even for repartition(n)	Possibly uneven partitions
Column form	`repartition(n, "col")` groups equal keys	Not available

df = spark.read.parquet("/data/events")
wide = df.repartition(200, "customer_id")
narrow = wide.coalesce(20)

Use coalesce before writing to cut the number of small output files. Use repartition to raise parallelism or, with repartition(n) and no column, to spread data evenly.

Identifying data skew

Skew means a few keys hold far more rows than the rest, so one partition and one task are much bigger than the others. In the Spark UI stage page, the signs are a maximum task duration or shuffle read size far above the median, and one task still running while the others have finished.

Common fixes are to broadcast the small side of a join, to add a random salt column to the hot key and aggregate in two steps, or to let AQE split the skewed partitions.

Reducing shuffling

A shuffle writes data to disk and moves it across the network, so it is the most expensive step.

Filter and select early so less data is shuffled.
Broadcast the small table in a join so the large table is not shuffled.
Avoid needless repartition calls and repeated wide operations on the same key.
Prefer built in functions over UDFs, which Spark cannot optimize.

from pyspark.sql import functions as F
result = big.join(F.broadcast(small), "key")

Spark broadcasts a table automatically when it is under spark.sql.autoBroadcastJoinThreshold (10 MB by default). Setting it to -1 turns automatic broadcasting off.

Adaptive Query Execution

Adaptive Query Execution: when a shuffle stage finishes, its partition size statistics let AQE re-plan the rest of the query: coalesce small shuffle partitions, switch a sort merge join to a broadcast join when one side is small, and split skewed partitions. AQE is on by default (spark.sql.adaptive.enabled). It uses statistics from finished shuffle stages to change the rest of the plan.

AQE feature	What it does
Coalesce shuffle partitions	Merges many small post shuffle partitions toward a target size
Switch join strategy	Turns a sort merge join into a broadcast join when one side turns out small
Skew join handling	Splits oversized partitions in a sort merge join

A partition counts as skewed when it is larger than spark.sql.adaptive.skewJoin.skewedPartitionFactor (5) times the median and larger than spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (256 MB). Both conditions must hold.

Because AQE coalesces partitions, the default of 200 shuffle partitions is less of a problem than it used to be.

How the exam asks about this

"A job writes thousands of tiny files after a filter." Use coalesce to reduce the partition count without a full shuffle.
"One task runs for an hour while the others finish in seconds." This is data skew, fixed by salting, a broadcast join or AQE skew handling.
"Which feature picks a broadcast join after seeing real data sizes?" Adaptive Query Execution.

What to remember for the exam

One partition is processed by one task, so partition count sets the parallelism.
repartition always shuffles; coalesce only decreases partitions and avoids a full shuffle.
spark.sql.shuffle.partitions defaults to 200; spark.sql.files.maxPartitionBytes defaults to 128 MB.
Skew shows as one task far larger or slower than the median in the Spark UI.
Broadcast joins avoid shuffling the large side; the automatic limit is 10 MB.
AQE coalesces shuffle partitions, switches join strategies and splits skewed partitions at run time.

Watch out

Do not use coalesce to increase partitions. It cannot, and asking for more partitions than exist leaves the count unchanged.

Common mistakes

Calling repartition to reduce file count. repartition works but pays for a full shuffle. When only reducing the count, coalesce is the cheaper answer.

Assuming AQE fixes everything. AQE only helps shuffle based plans that it can see. A single huge hot key in an aggregation, or a UDF that is slow per row, still needs your own fix.

Treating skew as a memory problem. Adding executor memory may hide skew but does not remove it. The cause is the uneven key distribution, so fix the distribution.

Check yourself

Which call reduces partitions without a full shuffle?
coalesce, because it merges existing partitions on the same executors instead of redistributing all rows.
How do you recognize data skew in the Spark UI?
One task has a much larger shuffle read size or duration than the median task, while most tasks finish quickly.
What does AQE do with a sort merge join when one side is small after filtering?
It can convert it to a broadcast join at run time, using the real size from the finished shuffle stage.

Topic 1 of 2 in Troubleshooting and Tuning

Test yourself on Troubleshooting and Tuning

The full Databricks Associate Developer for Apache Spark question bank has 600 questions, with timed practice exams and an explanation for every answer.

Get Full Access Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Databricks. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.