Back to Advanced Data Engineering
Q7Question 7 of 24

Common AQE configuration parameters and pitfalls

Key Concepts
  • The master switch spark.sql.adaptive.enabled gates all AQE features; if it is off, nothing else matters.
  • Sub-switches control coalescing (coalescePartitions.enabled) and skew handling (skewJoin.enabled) independently.
  • Tuning knobs include advisory partition size, skew factor, skew byte threshold, and the broadcast join threshold.
  • Pitfall 1: assuming AQE is always on when older Spark versions or custom configs may disable it.
  • Pitfall 2: changing thresholds blindly without understanding their interactions can make jobs slower.
  • Pitfall 3: expecting AQE to solve extreme single-key skew that needs manual salting or pre-aggregation.

Definition

Know the master, skew, and coalesce switches plus the key thresholds, and avoid the three traps: assuming AQE is on, tuning blindly, and expecting it to cure extreme skew.
  • Master switch – spark.sql.adaptive.enabled turns all AQE features on or off for the session.
  • Sub-switches – spark.sql.adaptive.coalescePartitions.enabled controls partition coalescing; spark.sql.adaptive.skewJoin.enabled controls skew handling.
  • Tuning knobs – advisory partition size (target for coalesced partitions), skew factor and byte threshold (when a partition counts as skewed), and broadcast join threshold (when a side is small enough to broadcast).
  • Common pitfalls – assuming AQE is always enabled, changing thresholds blindly, and expecting AQE to solve all skew.

Think of AQE as a set of smart assistants at the National Bank of India, each with an on-off switch and a few dials. There is a master switch that powers them all, a switch for the skew assistant, and a switch for the merge-small-trays assistant, plus dials for how big a tray should be and when a pile counts as too big. The pitfalls are simple mistakes: believing the assistants are working when the master switch is off, spinning dials randomly hoping for speed without understanding what they do, and trusting the skew assistant to handle even a monstrous single account it was never designed to fully absorb.

Understanding the Concepts

What are the AQE configuration parameters?

AQE parameters divide into switches and tuning knobs. The master switch gates everything; the sub-switches control individual features; and the knobs adjust thresholds.

PYTHON
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("AQEConfig").getOrCreate()

# Master switch – must be true for any AQE feature to work
spark.conf.set("spark.sql.adaptive.enabled", "true")

# Sub-switches
spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

# Tuning knobs
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "128m")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionFactor", "5")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "256m")
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "10m")

In modern Spark (3.2+) all switches default to true, but the exact defaults depend on the version and the platform.

How do the switches interact?

The master switch is the gate. If it is off, the sub-switches are irrelevant.

PYTHON
# This configuration disables everything despite the sub-switches
spark.conf.set("spark.sql.adaptive.enabled", "false")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")   # ignored
spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", "true")  # ignored

To selectively disable one feature while keeping the others, leave the master on and turn off just the sub-switch.

PYTHON
# AQE on, but skew handling disabled (perhaps for debugging)
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "false")

What is pitfall 1: assuming AQE is always enabled?

On older Spark versions (2.x) AQE did not exist or was off by default. In many real environments a platform team or inherited configuration explicitly disables it.

PYTHON
# Wrong: assume AQE is on and skip verification
result = txn.join(accounts, "account_id")
result.write.parquet("/lake/gold/enriched")

# Right: verify the effective configuration
print(spark.conf.get("spark.sql.adaptive.enabled"))  # check this!

# Also verify in the physical plan
result.explain()
# Look for AdaptiveSparkPlan – if absent, AQE is off
TEXT
# AQE ON:
AdaptiveSparkPlan isFinalPlan=true
+- SortMergeJoin ...

# AQE OFF:
SortMergeJoin ...  (no AdaptiveSparkPlan wrapper)

What is pitfall 2: changing thresholds blindly?

The knobs interact, and a careless change can make things worse.

PYTHON
# Mistake 1: advisory partition size too small recreates tiny-task problem
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "1m")
# Result: AQE creates many 1MB partitions instead of a few 128MB ones

# Mistake 2: broadcast threshold too high blows executor memory
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "2g")
# Result: AQE broadcasts a 2GB table, causing OOM on executors

# Mistake 3: skew factor too low falsely splits normal partitions
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionFactor", "1.5")
# Result: almost every partition looks "skewed" relative to the median

The right approach is to change one knob at a time, measure against representative data, and base values on real partition sizes and executor memory.

PYTHON
# Safe approach: check partition sizes first, then tune
from pyspark.sql import functions as F

stats = txn.groupBy(F.spark_partition_id().alias("pid")) \
    .agg(F.count("*").alias("row_count"))
stats.describe("row_count").show()

What is pitfall 3: expecting AQE to solve all skew?

AQE's skew handling targets joins and splits a skewed partition into sub-partitions. But if a single key is so dominant that even one sub-partition is unbalanced, splitting cannot help because those rows share one key.

PYTHON
from pyspark.sql import functions as F

# ACC-9999 has 100 million rows – even after splitting,
# each sub-partition still has millions of ACC-9999 rows
# because they all share the same key

# Manual salting is the fix for extreme single-key skew
salted = txn.withColumn(
    "salted_account",
    F.concat(F.col("account_id"), F.lit("_"), (F.rand() * 20).cast("int"))
)

# Join on the salted key, then aggregate back
salted_accounts = accounts.crossJoin(
    spark.range(20).withColumnRenamed("id", "salt")
).withColumn(
    "salted_account",
    F.concat(F.col("account_id"), F.lit("_"), F.col("salt"))
)

result = salted.join(salted_accounts, "salted_account")

Skewed aggregations and window functions are also outside AQE's join-focused skew handling and may need salting or pre-aggregation.

How do you verify AQE improvements?

Use the Spark UI's SQL tab and the physical plan to confirm AQE is working.

PYTHON
# Check the plan for AQE nodes
result.explain(True)

# In the Spark UI SQL tab, look for:
# - "coalesced" in the shuffle read (coalescing active)
# - "skewedShuffle" annotation (skew handling active)
# - BroadcastHashJoin replacing SortMergeJoin (dynamic switch)

What should a senior engineer know?

  • AQE removes most everyday tuning but is a complement to good data design, not a replacement.
  • Always verify AQE is active in production by checking the plan or the Spark UI.
  • Pair AQE with sensible defaults: a 128 MB advisory partition size, a broadcast threshold that fits executor memory, and a skew factor of 5.
  • Reserve manual salting for the few mega-keys AQE cannot absorb.
  • Test threshold changes on representative data; never tune in production without measuring.
PYTHON
# Production checklist
assert spark.conf.get("spark.sql.adaptive.enabled") == "true"
assert spark.conf.get("spark.sql.adaptive.skewJoin.enabled") == "true"
assert spark.conf.get("spark.sql.adaptive.coalescePartitions.enabled") == "true"

What are common interview follow-up questions?

  • What happens if the master switch is off but the sub-switches are on? Nothing; the master switch gates everything.
  • Can AQE make a job slower? In rare cases, aggressive coalescing to very large partitions can cause memory pressure, but this is uncommon with default settings.
  • Does AQE change the logical result? No, only the physical execution strategy. The output is identical.

Visual Overview

DIAGRAM
  AQE Configuration Hierarchy
  ───────────────────────────

  ┌─────────────────────────────────────────┐
  │  spark.sql.adaptive.enabled  (master)   │
  │  ┌───────────────────────────────┐      │
  │  │  coalescePartitions.enabled   │      │
  │  │  ┌─────────────────────────┐  │      │
  │  │  │ advisoryPartitionSize   │  │      │
  │  │  └─────────────────────────┘  │      │
  │  └───────────────────────────────┘      │
  │  ┌───────────────────────────────┐      │
  │  │  skewJoin.enabled             │      │
  │  │  ┌─────────────────────────┐  │      │
  │  │  │ skewedPartitionFactor   │  │      │
  │  │  │ skewedPartitionThreshold│  │      │
  │  │  └─────────────────────────┘  │      │
  │  └───────────────────────────────┘      │
  │  ┌───────────────────────────────┐      │
  │  │  autoBroadcastJoinThreshold   │      │
  │  │  (drives dynamic join switch) │      │
  │  └───────────────────────────────┘      │
  └─────────────────────────────────────────┘

  Master OFF → everything OFF
  Master ON  → each sub-switch independently

Key Takeaway

AQE is controlled by a small set of parameters that divide into switches and tuning knobs. The master switch spark.sql.adaptive.enabled gates everything; the coalesce switch and skew-join switch control their respective features under it; and the tuning knobs – advisory partition size, skew factor, skew byte threshold, and broadcast join threshold – adjust the behavior of each feature.

There are three pitfalls to always call out. Assuming AQE is enabled when older Spark versions or inherited configurations may have disabled it. Changing thresholds blindly, because lowering the advisory partition size recreates the tiny-task problem, raising the broadcast threshold too high causes executor OOM, and loosening the skew factor either misses real skew or falsely splits normal partitions. And expecting AQE to solve all skew, when extreme single-key dominance needs manual salting because those rows share one key and cannot be balanced by splitting.

At the National Bank of India we kept AQE on with sensible thresholds – 128 MB advisory partition size, a broadcast threshold that fit well within executor memory, and the default skew factor of 5. We always verified in production that the master switch was active because a disabled switch silently reverts to the old static behavior. And we reserved manual salting for the few mega-accounts whose single-key volume exceeded what splitting could absorb.

The mindset is that AQE is a powerful default that removes most everyday tuning, but it complements good data design and an understanding of your data distribution rather than replacing them.

Remember one sentence: verify the master switch is on, tune one knob at a time with real data, and reserve manual salting for the extreme skew that AQE's join-focused splitting cannot absorb.

---

Share
Use ← → arrow keys to navigate