Study guide
13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins
Microsoft Certified: Azure Databricks Data Engineer Associate DP-750
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Azure Databricks Architecture & Compute Selection
1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute
2 Chapter 2: Compute Configuration, Performance & Cost Management
2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions
3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup
3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown
4 Chapter 4: Unity Catalog Privilege Model & Access Control
4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation
5 Chapter 5: Advanced Data Security, Masking & ABAC
5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks
6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing
6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions
7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming
7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory
8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance
8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg
9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture
9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment
10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs
11 Chapter 11: Workflow Orchestration with Lakeflow Jobs
11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling
12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles
12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines
13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning
13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics
13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins
Key Takeaways Explain This Topic AI
Adaptive Query Execution (AQE) is enabled by default in Databricks Runtime (spark.sql.adaptive.enabled = true) and re-optimizes physical query execution plans at runtime based on accurate stage shuffle statistics.
Dynamic Coalescing of Shuffle Partitions (spark.sql.adaptive.coalescePartitions.enabled) automatically merges small adjacent shuffle partitions into optimal target sizes (default 64 MB), eliminating the need to manually tune spark.sql.shuffle.partitions.
Dynamic Switching to Broadcast Hash Join converts expensive SortMergeJoins into fast BroadcastHashJoins at runtime when intermediate filtering reduces a join relation below spark.sql.adaptive.autoBroadcastJoinThreshold.
Dynamic Skew Join Optimization detects skewed partitions exceeding configured thresholds and automatically splits them into smaller sub-partitions, duplicating corresponding join keys on the other side.
Databricks Disk Cache (Delta Cache) stores remote Parquet/Delta data on fast worker NVMe SSDs transparently without JVM heap overhead, whereas Spark In-Memory Caching (cache()/persist()) stores deserialized objects in RAM and requires explicit unpersist() lifecycle management.
Last updated: August 2026
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:
- Size > spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (default: 256 MB )
- 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).