Interview/Spark

Spark interview questions: Adaptive Query Execution

Interactive Spark interview questions on Adaptive Query Execution. Practice with the matching lesson. AQE uses runtime stats to coalesce shuffle partitions, change join strategy, and split skewed partitions.

Lesson · Simulation

What is Adaptive Query Execution?

Answer it out loud, then reveal. Play steps through like the simulators.

Production scenario

Adaptive Query Execution

AQE broadcast conversion OOMs a growing dim

Symptoms

  • Worked last quarter
  • Dim crossed the threshold at runtime

All questions on this page

Indexed as FAQ. Open any item if you prefer a list to Play.

Showing 8 of 8 questions.

beginner

What is Adaptive Query Execution?

Adaptive Query Execution · tap to open the answer

Say this first: Spark re-plans later stages at runtime using real sizes.

Why: Three headlines: coalesce shuffle partitions, switch join strategy, handle skewed joins. Enabled by default in modern Spark / Databricks.

Common mistake: AQE as a replacement for designing a good join key.

Follow-up they will ask: Does AQE run before the first stage?

Lesson · Simulation

intermediate

When will AQE not save you?

Adaptive Query Execution · tap to open the answer

Say this first: A single skewed key, a Python UDF, or a broadcast that's already too big.

Why: Coalesce won't split a hot key. Join conversion needs a truly small side. Skew join has limits. You still need a sane plan and files.

Common mistake: Turning every knobs to true and calling it architecture.

Follow-up they will ask: Which AQE feature shows up as extra stages in the UI?

Lesson · Simulation

senior

AQE coalesced 2000 shuffle partitions down to 12, and now one task is huge. What happened?

Adaptive Query Execution · tap to open the answer

Say this first: Coalesce packed leftover data into too few partitions, or skew was hidden.

Why: Coalesce is for empty/small partitions. If data is uneven, you can get fat tasks. Inspect post-AQE partition sizes. Disable coalesce for that query or salt.

Senior depth: spark.sql.adaptive.enabled and compare SQL UI plans — same query, different later stages.

Common mistake: Blaming AQE without looking at the adaptive plan.

Follow-up they will ask: How do you show AQE on/off in an interview demo?

Lesson · Simulation

beginner

Why does the plan in the SQL / DataFrame tab look different from what df.explain() printed in your notebook?

Adaptive Query Execution · tap to open the answer

Say this first: explain() shows the pre-execution plan; the UI shows the final plan after AQE re-planned it with real statistics.

Remember these

  • explain() before execution reports isFinalPlan=false
  • AQE re-plans at shuffle boundaries using measured statistics
  • The SQL / DataFrame tab holds the plan that actually ran

Why: With AQE on, the physical plan is wrapped in an AdaptiveSparkPlan node, and before execution it reports isFinalPlan=false — what you printed is Catalyst's estimate-based guess. As each shuffle map stage completes, AQE reads its real output statistics and may coalesce shuffle partitions, convert a join to a broadcast, or split a skewed partition, then re-plans the remainder of the query. The SQL / DataFrame tab shows that final plan with isFinalPlan=true and marks the nodes AQE changed. So a plan pasted from a notebook is a hypothesis about what will run, and the UI is the record of what did run. Debug against the UI plan — chasing a SortMergeJoin that AQE already converted wastes an afternoon.

df.explain("formatted")   # AdaptiveSparkPlan isFinalPlan=false
df.collect()              # then read the SQL / DataFrame tab

Common mistake: Debugging the notebook plan and never opening the SQL tab to see what AQE did.

Follow-up they will ask: Which part of a query can AQE never change?

Lesson · Simulation

intermediate

Where does AQE get its runtime statistics, and what follows from that?

Adaptive Query Execution · tap to open the answer

Say this first: From the map output statistics of completed shuffle stages, which is why it can only act at Exchange boundaries.

Remember these

  • Statistics come from completed shuffle map stages, per output partition
  • No shuffle in the query means no AQE decision point
  • The first shuffle still runs with the static partition count

Why: When a shuffle map stage finishes it reports the size of every output partition back to the driver. AQE uses those numbers — actual bytes per reducer partition, not estimates — to decide whether to coalesce partitions, convert a join, or split a skew. Two consequences follow. A query with no shuffle gets essentially nothing from AQE, because there is no measurement point. And the very first shuffle is still planned from estimates and still launches spark.sql.shuffle.partitions tasks, so AQE cannot fix the partitioning of the stage it is measuring, only of the stages after it. That is why people see 200 tasks in the first stage and a handful in the second and conclude AQE is inconsistent. The measurement point is the Exchange.

spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.get("spark.sql.shuffle.partitions")  # still the initial count

Common mistake: Expecting AQE to fix the partitioning of the first shuffle, which it must run before it can measure.

Follow-up they will ask: What does spark.sql.adaptive.coalescePartitions.initialPartitionNum change?

Lesson · Simulation

advanced

How does AQE decide the final number of shuffle partitions, and which configs bound it?

Adaptive Query Execution · tap to open the answer

Say this first: It packs adjacent shuffle partitions together until each merged one is near the advisory size, within the min and initial partition bounds.

Remember these

  • Coalescing groups contiguous partitions up to the advisory size
  • initialPartitionNum sets the granularity AQE merges from
  • minPartitionNum stops a small query collapsing to one task
  • AQEShuffleRead in the final plan reports the coalescing applied

Why: After the map stage reports per-partition sizes, the coalescing rule walks the partitions in order and groups contiguous ones into a single reducer task whose total is close to spark.sql.adaptive.advisoryPartitionSizeInBytes. spark.sql.adaptive.coalescePartitions.minPartitionNum floors the result so a small query does not collapse to one task, and spark.sql.adaptive.coalescePartitions.initialPartitionNum sets the pre-coalesce count independently of spark.sql.shuffle.partitions — set the initial number high so there is granularity to merge from, and let AQE pick the final number. Because the grouping is contiguous, an uneven size distribution can still produce one fat merged partition, which is the usual explanation for 'AQE coalesced to twelve and now one task is huge'. Verify by reading the post-shuffle partition sizes and the AQEShuffleRead node in the final plan, which reports the coalescing it applied.

spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "128m")
spark.conf.set("spark.sql.adaptive.coalescePartitions.initialPartitionNum", "2000")

Senior depth: The same node reports a skew split instead, showing that a partition was broken into several reader slices fed to separate tasks. Reading that node is how you prove a skew split happened rather than hoping it did: no AQEShuffleRead annotation on the skewed side means the thresholds were never met and the partition went to one task exactly as before.

Common mistake: Tuning spark.sql.shuffle.partitions downward while AQE is on, instead of raising the initial count and setting an advisory size.

Follow-up they will ask: What does AQEShuffleRead look like for a skew split rather than a coalesce?

Lesson · Simulation

advanced

AQE converted a sort-merge join to a broadcast, but the Exchange is still in the final plan. Explain.

Adaptive Query Execution · tap to open the answer

Say this first: The shuffle had already executed — that is how AQE measured the side it decided to broadcast — so the plan reads the existing shuffle output locally.

Remember these

  • AQE measures after the map stage, so the shuffle write already happened
  • The local shuffle reader avoids the network fetch, not the write
  • A runtime conversion saves less than a correctly planned broadcast

Why: AQE's decisions happen after a shuffle map stage completes, so by the time it knows the build side is small those shuffle files already exist. It cannot un-write them, so the converted plan keeps the Exchange and reads its output with a local shuffle reader: each executor reads the blocks it wrote itself, avoiding a network fetch, and the broadcast hash join runs on top. You see this as an Exchange still present with an AQEShuffleRead marked local, controlled by spark.sql.adaptive.localShuffleReader.enabled. The saving is real — the expensive part of a sort-merge join is the sort and the all-to-all fetch, not the map-side write — but smaller than a broadcast planned from the start. That is also why fixing statistics still beats relying on AQE when the size is knowable up front.

spark.conf.get("spark.sql.adaptive.localShuffleReader.enabled")
# final plan: Exchange + AQEShuffleRead local + BroadcastHashJoin

Senior depth: Whenever the size is knowable before runtime and the query runs often. A planned broadcast skips the map-side shuffle write entirely, which on a large fact table is the bulk of the cost. AQE is the safety net for cases estimates cannot reach — UDF results, JDBC sources, heavily filtered intermediates — not a reason to leave ANALYZE TABLE undone on your core tables.

Common mistake: Reading the leftover Exchange as proof the conversion never happened.

Follow-up they will ask: So when is fixing statistics still worth the effort?

Lesson · Simulation

senior

You want reproducible plans for a benchmark. Is disabling AQE the right call?

Adaptive Query Execution · tap to open the answer

Say this first: For one isolated experiment, defensible; as a standing configuration, no — you would be benchmarking a plan production never runs.

Remember these

  • AQE makes plans data-dependent by design
  • Benchmarking without it measures a plan production never runs
  • Capture the final plan per run and treat plan flips as a result

Why: AQE makes plans data-dependent, so two runs over slightly different data can produce different joins and partition counts, which is genuinely awkward for a controlled comparison. For a single experiment, pinning spark.sql.adaptive.enabled to false on both arms is fine as long as you report it. But production runs with AQE on, so a benchmark without it measures a plan that will not be used, and the differences are not small — coalescing changes task counts, conversion changes join strategy, skew splitting changes stage time. The better approach is to keep AQE on and capture the final plan per run from the SQL tab or the event log, then compare like with like and treat a plan change as a result rather than as noise. Disabling AQE to make a dashboard look stable is how teams end up tuning a configuration that does nothing in production.

spark.conf.set("spark.sql.adaptive.enabled", "false")  # experiment only, and report it

Senior depth: Scope it to the feature and the query. If a runtime broadcast conversion caused an executor OOM, cap spark.sql.adaptive.autoBroadcastJoinThreshold rather than killing adaptive execution; if coalescing produced a fat task, set an advisory partition size or disable spark.sql.adaptive.coalescePartitions.enabled for that query only. Global off is a blunt instrument that also surrenders the skew handling you probably depend on.

Common mistake: Turning AQE off permanently after one query regressed, instead of capping the specific feature involved.

Follow-up they will ask: Which single AQE feature would you disable instead of the whole thing?

Lesson · Simulation