Let AQE Handle It

A medium Spark interview practice problem on DataDriven. Write the Spark code and run it against the tables, with instant feedback.

Domain

Spark

Difficulty

medium

Seniority

L5

Asked in

Databricks

Problem

The nightly match of transactions against users on user_id has crept to 90 minutes against a 30-minute SLA, and the on-call engineer traced it to one merge task reading 8x the median rows on a Spark 3.4 cluster whose job config switched adaptive execution off. Salting the key would ripple into three downstream jobs, so she wants a configuration-only fix that splits the hot slice at runtime while the match keeps its current merge strategy and still returns each matched transaction's user_id, transaction_date and total_amount.

Five tasks take 35 minutes. The other 195 take 30 seconds.

Expected output

Worked solution and explanation

This is a config archaeology problem dressed up as a performance ticket. Everyone flips spark.sql.adaptive.enabled back to true. What separates candidates is reading the plan note to the last line: the same override also pins `spark.sql.adaptive.skewJoin.enabled` to false, and the parent switch does not undo a child override. Ship the one-liner and AQE happily coalesces partitions, you report 'AQE is on', and partition 47 still runs as one 35-minute task.

Read the evidence first

The plan is a static SortMergeJoin over 200 hash partitions of user_id. In 3.4 both AQE and its skew rule default to on, so two explicit false values mean someone overrode them in the job or cluster config. Partition 47 holds 144M rows against an 18M median and shows up as one task on exec-1 with 312 seconds of GC while the other executors sit near 16.

Step 1: Re-enable the parent switch

Set spark.sql.adaptive.enabled to 'true'. This brings back runtime re-planning at stage boundaries, including partition coalescing, which is on by default under AQE and needs no extra flag.

Step 2: Re-enable the skew rule separately

Set spark.sql.adaptive.skewJoin.enabled to 'true'. Skew splitting is its own rule inside AQE. An explicit false on it survives the parent coming back on, so without this line the hot partition is never even considered for splitting.

Step 3: Confirm the thresholds already clear

About 460 GB into 200 partitions puts the median near 2 GB, so partition 47 is around 16 GB. That beats both the default 5x skewedPartitionFactor and the 256 MB skewedPartitionThresholdInBytes. Leave them alone: setting a knob to its own default is noise in a config review.

Step 4: Keep it a sort-merge join

The split rewrites the shuffle read feeding the SortMergeJoin: the hot partition is cut into ranges and the matching users partition is replicated to each. A broadcast join has no shuffle read to rewrite. At 60 GB, users is far above autoBroadcastJoinThreshold, so do not raise that threshold chasing a broadcast.

# The job config pins BOTH switches to false. The skew rule is its own flag:
# re-enabling the AQE parent does not undo the override on the child.
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

transactions = spark.table("transactions")
users = spark.table("users")

matched = transactions.join(users, "user_id", "inner")
result = matched.select("user_id", "transaction_date", "total_amount")

Parent flag only

spark.sql.adaptive.enabled true, skewJoin.enabled still false. The plan gains an AQEShuffleRead marked coalesced, 200 partitions shrink to 18, and partition 47 still lands on one task. Runtime barely moves.

Parent and skew rule

Both true. The big side's AQEShuffleRead reads coalesced plus skew-split, partition 47 becomes 8 sub-tasks, and the join stage stops waiting on a single straggler.

Common follow-up questions

• If partition 47 were 8x a 20 MB median, what would AQE do, and what would you change? (Whether the candidate knows the absolute `skewedPartitionThresholdInBytes` floor gates the split.)
• The join stage is fixed but the job still takes over an hour. Where do you look next? (Separating the scan cost of `transactions` from the shuffle skew.)
• What changes if someone raises `autoBroadcastJoinThreshold` to 100 GB? (Broadcasting 60 GB is an executor memory risk, and skew splitting stops applying.)