Adaptive Query Execution AQE in Databricks Spark

Listen, we’ve all been there. It’s 2 AM, you’re staring at a Spark job that’s been stuck at 99% for forty minutes, and you’re wondering why you didn’t just become a carpenter.

The culprit? Nine times out of ten, it’s a static execution plan.

In the old days (we’re talking pre-Spark 3.0), Spark was like a GPS that calculated your route once and then stubbornly refused to recalculate — even if there was a massive pile-up or a mudslide on the I-95 right in front of you. It had a plan, and it was sticking to it, efficiency be damned.

Adaptive Query Execution (AQE) is the “Waze” of Spark. It doesn’t just make a plan and close its eyes. It looks at the actual data as it moves through the pipeline, learns from it, and dynamically changes the plan on the fly.

Here is the breakdown of how this single feature saves your weekend, prevents production on-call nightmares, and makes your company’s cloud budget look a whole lot healthier.

Enable AQE

set spark.sql.adaptive.enabled = true;

1. The “Too Many Empty Boxes” Problem (Partition Coalescing)

Imagine you’re running a shipping business and you need to move 200 boxes across the country. As you’re packing, you realize that 180 of them are actually empty (maybe your upstream filter was super effective).

A “static” system is dumb; it would still pay for and dispatch 200 trucks, one for each box, costing you a fortune in network overhead and scheduling.

The Tech Reality: This happens constantly when you set a high default for spark.sql.shuffle.partitions (like 200 or 2000), but your final, post-filtered dataset is tiny.

• Without AQE: Spark launches 200 separate tasks. The overhead (the time it takes the master to schedule and start each task) is longer than the actual work the task is doing. Your job “hangs” not because it’s doing hard work, but because it’s doing pointless administrative work.
• With AQE: Spark pauses at the “shuffle” boundary, looks at the actual sizes of the files generated, and says, “This is silly. Let’s just put all this data into 2 trucks instead of 200.”
• The Result: Your job stops suffocating on tiny tasks and finishes in a fraction of the time.

2. The “Piano vs. Pillow” Struggle (Skew Join Handling)

Now imagine a team building exercise where you give 10 people one bag each to carry up a mountain. It seems fair, until you realize that one person’s bag contains a grand piano and the other nine have pillows.

Obviously, nine people finish in seconds and proceed to wait at the summit for three hours, completely idle and frustrated, while the “piano person” struggles.The Tech Reality: This is Data Skew, and it is the #1 reason why production Spark jobs crawl or fail with Out-Of-Memory (OOM) errors. It happens when you join on a column where one value is massively overrepresented (e.g., millions of “NULL” values or all sales from “USA”).

• Without AQE: One CPU core in your cluster is pinned at 100% for an hour while the rest of your expensive cluster sits there doing absolutely nothing, just waiting for that one “piano” task to finish. You are wasting tons of money.
• With AQE: Spark detects the “piano” (the skewed partition) while the job is running. It dynamically says, “Oye, this is too much for one core.” It automatically breaks that giant partition into 10 smaller pieces and distributes them to the idle workers.
• The Result: A perfectly balanced cluster, and a much faster (and satisfying) “Success” notification.

Why didn’t AQE detect my data skew?

There are two size conditions that must be satisfied for AQE to detect a partition as a skewed partition:

• The partition size is larger than the spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (default 256MB)
• The partition size is larger than the median size of all partitions times the skewed partition factor spark.sql.adaptive.skewJoin.skewedPartitionFactor (default 5)

In addition, skew handling support is limited for certain join types, for example, in LEFT OUTER JOIN, only skew on the left side can be optimized.

3. The “Truck vs. Uber” Choice (Join Re-optimization)

If you’re moving a single envelope, you don’t go to U-Haul and rent a 15-foot box truck. You throw it in your backpack and take an Uber, or you just walk.

Spark, however, used to be “U-Haul or nothing.” It would look at the initial estimated size of a table and plan an expensive, cluster-wide move (a Sort-Merge Join — the U-Haul) even if that table gets filtered down to almost nothing before the join happens.

The Tech Reality: This is the most computationally expensive mistake Spark can make.

• Without AQE: Spark executes the full shuffle-and-sort plan (Sort-Merge Join). You are moving gigabytes of data across the network that you don’t need to.
• With AQE: After the filters run, Spark pauses and realizes, “Wait, this filtered table is now only 5MB! It fits in memory!” It instantly cancels the “U-Haul” plan and switches to a lightning-fast Broadcast Hash Join (the Uber).
• The Result: You avoid the “Shuffle” — which is the single slowest part of any distributed system — turning a 10-minute operation into a 10-second one.

Why didn’t AQE broadcast a small join table?

If the size of the relation expected to be broadcast does fall under this threshold but is still not broadcast:

• Check the join type. Broadcast is not supported for certain join types, for example, the left relation of a LEFT OUTER JOIN cannot be broadcast.
• It can also be that the relation contains a lot of empty partitions, in which case the majority of the tasks can finish quickly with sort merge join or it can potentially be optimized with skew join handling. AQE avoids changing such sort merge joins to broadcast hash joins if the percentage of non-empty partitions is lower than spark.sql.adaptive.nonEmptyPartitionRatioForBroadcastJoin.

The Bottom Line: Stop Trying to be a “Spark Whisperer”

The real beauty of AQE isn’t just that it’s fast; it’s that it makes your pipelines resilient.

You can stop spending your life trying to guess the perfect number of shuffle partitions or manually fixing skew for every edge case. Data volumes change, data distribution shifts, but AQE adapts to it all.

If you are running Databricks Runtime 7.3 or later, this is already on by default. If you are on an older, non-Databricks Spark, you are living in the dark ages. Turning on AQE is the simplest, most human thing you can do to write better code, build faster pipelines, and get your weekends back.

References

https://docs.databricks.com/aws/en/optimizations/aqe