A Time AQE Made It Worse

Concepts covered: Adaptive Query Execution, Configuration Tuning

Interviewers ask for a backfire story because a story is hard to fake: the details either connect or they do not. A credible one has 4 parts in order: what changed, what the Spark UI showed, the mechanism that explains it, and the fix together with what the fix cost. Here is one built on a mechanism you can reproduce, with numbers that hold together.

The job

A nightly job joins a day of order events to customers, groups by order_id, and runs a Python UDF that parses each order's JSON payload into 40 fields. The team had tuned spark.sql.shuffle.partitions to 400 for this job on a cluster with 200 executor cores, so the UDF stage ran 400 tasks in 2 waves of about 2.5 minutes each, roughly 5 minutes for the stage. Then the platform team moved the cluster to a Spark version with adaptive execution on by default, and set spark.sql.adaptive.coalescePartitions.parallelismFirst to false to cut the count of tiny tasks on the shared cluster. The next night the job missed its deadline.

What the Spark UI showed

19
53 min
181

On the SQL tab, the exchange before the UDF had an AQEShuffleRead node above it reporting the coalesced partition count and roughly 1.2GB of shuffle data. The page showed the plan as AdaptiveSparkPlan isFinalPlan=true, and the initial plan beside it still read hashpartitioning(order_id, 400). The event timeline showed most executors empty for the length of the stage, and the task summary showed durations clustered tightly: no long tail, every task uniformly slow.

The mechanism

With parallelismFirst off, coalescing packs adjacent shuffle partitions together until each reaches spark.sql.adaptive.advisoryPartitionSizeInBytes, 64MB by default. The job wrote roughly 1.2GB of shuffle output, and dividing that by 64MB gives roughly 19 partitions. The tuned 400 had become only the starting count. The flaw is in what the 64MB measures: compressed shuffle bytes, the input to the stage. The expensive work in this stage was the UDF, whose cost follows row count and payload length, and repetitive JSON compresses very well. Each remaining task now carried about 21 times the rows of an old one, so a 2.5 minute task became a 53 minute task, while 181 of the 200 cores waited.

Fixes, and what each one costs

Fix	Effect on the UDF stage	Cost
Set `spark.sql.adaptive.coalescePartitions.enabled` to false for this job	Back to 400 tasks, about 5 minutes	Every other shuffle in the job loses coalescing, and its cheap stages return to 400 tiny tasks
Lower the advisory size to 8MB for this job	About 154 tasks in a single wave, roughly 6.5 minutes	Every shuffle in the job is sized finer, including the cheap ones
`repartition(400)` just before the UDF	400 tasks, since AQE leaves an explicit partition count alone	An extra shuffle of the aggregated rows and a further stage

parsed = (
    orders.join(customers, "customer_id")
    .groupBy("order_id")
    .agg(F.first("payload").alias("payload"))
    .repartition(400)
    .withColumn("fields", parse_payload(F.col("payload")))
)

The team chose the repartition(400). It confined the fix to the single expensive step and kept coalescing for the cheap shuffles elsewhere in the job. The extra stage wrote less than the 1.2GB the aggregation had shuffled, a small price next to a 48 minute regression. They rejected turning coalescing off, because that fixes this stage and silently regresses every small shuffle in the job.

Other backfires with the same shape

Backfire	Mechanism	What you would see
Runtime broadcast exhausts memory	The conversion reads serialized shuffle size; the hash table built from those rows in memory can be several times larger	Driver or executor memory errors around a broadcast exchange that the initial plan did not have
Skew split multiplies reads	A skewed partition cut into N pieces re-reads the other side's matching partition N times	The join stage's task count jumps and its shuffle read exceeds the shuffle written into it

Telling it

Open with the change: an upgrade, a config, a shift in the data. Backfires follow a change.
Give before and after numbers for the slow stage, in tasks and minutes.
Name the UI evidence: the AQEShuffleRead partition count and idle executors on the timeline.
State the mechanism in a sentence: coalescing sizes tasks by compressed input bytes.
Name the fix you chose, the one you rejected, and why.
Close with what you now check when reviewing a job after an upgrade.

About this lesson

This section is part of the The Architecture Recital lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.