13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins

13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins

Optimizing distributed queries historically required extensive manual tuning: calculating custom partition counts, injecting broadcast hints, and restructuring queries to mitigate data skew. In modern Azure Databricks runtimes, performance tuning combines autonomous engine optimizations—primarily Adaptive Query Execution (AQE)—with targeted architectural techniques such as Broadcast Hash Joins and Databricks Disk Caching.

Understanding how these optimization subsystems operate under the hood allows data engineers to write high-throughput pipelines that maximize hardware efficiency while minimizing Databricks Unit (DBU) consumption.

---

1. Adaptive Query Execution (AQE) Architecture

In standard Apache Spark, the Catalyst Optimizer compiles a static physical plan before query execution begins. However, static optimization suffers from a major limitation: the optimizer cannot accurately predict the size, cardinality, or skew of data after complex filters, joins, and aggregations have executed.

Adaptive Query Execution (AQE) solves this by introducing a runtime feedback loop. AQE divides the physical execution plan into query stages. As each stage completes its shuffle write, AQE pauses, inspects the exact runtime statistics of the materialized shuffle files, and dynamically re-optimizes the remaining downstream physical plan.

+-------------------------------------------------------------------------+
|                  ADAPTIVE QUERY EXECUTION (AQE) LIFECYCLE               |
+-------------------------------------------------------------------------+
|                                                                         |
|  1. INITIAL STAGE EXECUTION                                             |
|     ├── Scan Table A & Table B with initial estimated plan              |
|     └── Execute Stage 0 Shuffle Write to local disks                    |
|                                                                         |
|  2. RUNTIME STATISTIC COLLECTION                                        |
|     └── AQE inspects exact shuffle metrics: partition sizes, row counts |
|                                                                         |
|  3. DYNAMIC RE-PLANNING (Three Core Optimizations)                      |
|     ├── 1. Dynamically Coalesce Shuffle Partitions (Merge tiny parts)   |
|     ├── 2. Dynamically Switch Join Strategy (SortMergeJoin -> BHJ)      |
|     └── 3. Dynamically Optimize Skew Joins (Split giant partitions)     |
|                                                                         |
|  4. RESUME EXECUTION WITH OPTIMIZED STAGE 1 PLAN                        |
+-------------------------------------------------------------------------+

Exam Tip: AQE is enabled by default in Databricks Runtime (spark.sql.adaptive.enabled = true). In DP-750 scenarios, you should rely on AQE before attempting manual low-level Spark configuration overrides.

---

2. The Three Pillars of AQE Optimization

Pillar 1: Dynamic Coalescing of Shuffle Partitions

• The Problem: In legacy Spark, spark.sql.shuffle.partitions defaulted to a static value of 200. For small datasets (10 MB), 200 partitions resulted in 200 tiny tasks with massive scheduling overhead and small file proliferation. For massive datasets (10 TB), 200 partitions caused massive tasks that spilled to disk.
• The AQE Solution: Setting spark.sql.adaptive.coalescePartitions.enabled = true allows Spark to start with a high partition count and automatically coalesce adjacent small partitions into target partition sizes based on spark.sql.adaptive.advisoryPartitionSizeInBytes (default: 64 MB or 128 MB).

+-------------------------------------------------------------------------+
|               DYNAMIC SHUFFLE PARTITION COALESCING                      |
+-------------------------------------------------------------------------+
|                                                                         |
|  BEFORE AQE: 5 Tiny Partitions (5 tasks spawned, high overhead)         |
|  [Part 0: 12MB] [Part 1: 8MB] [Part 2: 15MB] [Part 3: 9MB] [Part 4: 10MB]|
|                                                                         |
|  AFTER AQE COALESCING: 1 Consolidated Partition (Target: 64MB)          |
|  [=================== Coalesced Task 0: 54MB ===================]       |
+-------------------------------------------------------------------------+

Pillar 2: Dynamic Switching to Broadcast Hash Join

• The Problem: A table might be 100 GB on storage, preventing a broadcast join during initial compilation. However, if a query applies a selective filter (e.g., WHERE date = '2026-08-26'), the actual filtered dataset size might be only 5 MB.
• The AQE Solution: When the shuffle stage completes, AQE checks the actual materialized size of the relation. If the size is below spark.sql.adaptive.autoBroadcastJoinThreshold (default: 10 MB, configurable), AQE swaps the planned SortMergeJoin with a high-speed BroadcastHashJoin at runtime, completely skipping the shuffle for the other relation.

Pillar 3: Dynamic Skew Join Optimization

• The Problem: One partition in a join contains 10 GB while all others contain 20 MB, creating severe task stragglers.
• The AQE Solution: AQE detects that a partition is skewed if its size satisfies two conditions:
  1. Size > spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (default: 256 MB)
  2. Size > spark.sql.adaptive.skewJoin.skewedPartitionFactor * Median Partition Size (default factor: 5.0)
• When detected, AQE automatically splits the skewed partition into smaller sub-partitions and duplicates the corresponding partition of the join partner.

+-------------------------------------------------------------------------+
|                     AQE CONFIGURATION PARAMETERS                        |
+-------------------------------------------------------------------------+
|  CONFIGURATION PROPERTY                              | DEFAULT VALUE    |
|------------------------------------------------------|------------------|
|  spark.sql.adaptive.enabled                          | true             |
|  spark.sql.adaptive.coalescePartitions.enabled       | true             |
|  spark.sql.adaptive.advisoryPartitionSizeInBytes     | 67108864 (64 MB) |
|  spark.sql.adaptive.autoBroadcastJoinThreshold       | 10485760 (10 MB) |
|  spark.sql.adaptive.skewJoin.enabled                 | true             |
|  spark.sql.adaptive.skewJoin.skewedPartitionFactor   | 5.0              |
|  spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes | 268435456 (256 MB)|
+-------------------------------------------------------------------------+

---

3. Broadcast Hash Join (BHJ) Mechanics & Explicit Hints

A Broadcast Hash Join (BHJ) is the fastest join strategy in distributed data processing because it completely avoids the expensive network shuffle of the large dataset.

+-------------------------------------------------------------------------+
|                    BROADCAST HASH JOIN ARCHITECTURE                     |
+-------------------------------------------------------------------------+
|                                                                         |
|  1. Driver collects Small Table (e.g., 20 MB Dimension Table).          |
|  2. Driver broadcasts serialized hash table to all Worker Nodes.        |
|  3. Each Worker streams local partitions of Large Table (10 TB) and     |
|     performs in-memory hash lookups against the broadcast table.        |
|  4. ZERO network shuffle of the 10 TB Large Table!                      |
|                                                                         |
+-------------------------------------------------------------------------+

Explicit Broadcast Hints in SQL and PySpark

When engineers know a dataset is small and want to enforce a broadcast join regardless of catalog table statistics, they supply explicit broadcast hints:

# PySpark: Explicit Broadcast Hint via broadcast() function
from pyspark.sql.functions import broadcast

orders_df = spark.table("gold.orders")              # Large Fact Table (500M rows)
stores_df = spark.table("gold.store_locations")     # Small Dim Table (50 rows)

# Enforce Broadcast Hash Join
joined_df = orders_df.join(broadcast(stores_df), on="store_id", how="inner")

-- SQL: Explicit Broadcast Hint
SELECT /*+ BROADCAST(s) */
  o.order_id,
  o.amount,
  s.store_name,
  s.city
FROM gold.orders o
JOIN gold.store_locations s
  ON o.store_id = s.store_id;

Caution: Never broadcast large datasets (> 1 GB). Attempting to broadcast a multi-gigabyte table will crash the Driver with an OutOfMemoryError or cause severe executor memory pressure.

---

4. Databricks Disk Caching vs. Spark In-Memory Caching

Azure Databricks provides two distinct caching layers that data engineers must not confuse:

+-------------------------------------------------------------------------+
|             DATABRICKS DISK CACHE VS. SPARK IN-MEMORY CACHE             |
+-------------------------------------------------------------------------+
|  FEATURE                 | DATABRICKS DISK CACHE   | SPARK IN-MEMORY CACHE |
|--------------------------|-------------------------|-----------------------|
|  Storage Location        | Worker Local NVMe SSD   | Worker JVM Heap RAM   |
|  Activation Mechanism    | Automatic (Transparent) | Manual (.cache())     |
|  Format Stored           | Raw uncompressed files  | Deserialized JVM objs |
|  Cache Invalidation      | Automatic (Delta Log)   | Manual (.unpersist()) |
|  Impact on GC / Heap     | ZERO (Stored on disk)   | HIGH (Consumes Heap)  |
|  Best For Workload       | Repeated Delta queries  | Iterative ML loops    |
+-------------------------------------------------------------------------+

1. Databricks Disk Cache (formerly Delta Cache)

• How it Works: Worker nodes automatically copy remote Parquet/Delta data files from ADLS Gen2 onto local, high-speed NVMe SSD drives as they are read.
• Automatic Consistency: The cache tracks Delta Lake transaction log commits (_delta_log). If underlying data is updated, inserted, or deleted, Databricks automatically invalidates obsolete cache blocks.
• Zero Configuration: Enabled by default on all cluster node types equipped with local SSDs (e.g., Azure Standard_E8ds_v5, Standard_L series).

2. Spark In-Memory Cache (df.cache() / df.persist())

• How it Works: Explicitly instructs Spark to store the computed DataFrame partitions in worker JVM execution memory in deserialized or serialized form.
• Lifecycle Management: Cached DataFrames persist in JVM heap until explicitly evicted via df.unpersist() or pushed out by Least Recently Used (LRU) policy when memory pressure spikes.
• Best Practices: Use df.cache() only when the exact same intermediate DataFrame transformation is evaluated multiple times within an iterative workflow (e.g., training a machine learning model across 50 iterations).