13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis

13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis

In enterprise lakehouse engineering, building functional data pipelines is only half the battle. When production pipelines miss service level agreements (SLAs), exhaust cluster memory, or experience unpredictable runtimes, data engineers must look inside the distributed execution engine. The Apache Spark UI embedded within Azure Databricks is the primary diagnostic console for observing query execution, profiling resource consumption, diagnosing bottlenecks, and validating optimization strategies.

Mastering the Spark UI requires understanding how high-level PySpark and SQL DataFrame operations translate into distributed physical execution plans, stages, and tasks.

---

1. Spark UI Architecture & Navigation Tabs

The Spark UI is accessible directly from running or completed Azure Databricks compute clusters, Lakeflow Job run details, and notebook cell execution results. It organizes execution telemetry into five core tabs:

+-------------------------------------------------------------------------+
|                        SPARK UI DIAGNOSTIC TABS                         |
+-------------------------------------------------------------------------+
|                                                                         |
|  [Jobs]       High-level timeline of Spark actions (.saveAsTable,      |
|               .count(), .collect()) and their constituent stages.       |
|                                                                         |
|  [Stages]     Granular stage-level execution metrics, task breakdown,   |
|               duration quantiles, shuffle read/write, and memory spill. |
|                                                                         |
|  [Storage]    Memory and disk footprint of cached RDDs/DataFrames       |
|               persisted via .cache() or .persist().                     |
|                                                                         |
|  [Executors]  Hardware resource consumption, active cores, JVM GC time, |
|               memory usage, and task distribution across worker nodes.  |
|                                                                         |
|  [SQL/DF]     Interactive Directed Acyclic Graph (DAG) showing physical |
|               operators, row counts, scan metrics, and execution times. |
+-------------------------------------------------------------------------+

Detailed Tab Functions

---

2. The Spark Execution Hierarchy: Application, Jobs, Stages, & Tasks

To interpret Spark UI metrics accurately, engineers must understand the four-tier execution hierarchy that governs all distributed Spark processing:

+-------------------------------------------------------------------------+
|                       SPARK EXECUTION HIERARCHY                         |
+-------------------------------------------------------------------------+
|                                                                         |
|  SPARK APPLICATION (Driver + Cluster Lifetime)                          |
|  │                                                                      |
|  ├── JOB 0 (Triggered by Action: df.write.saveAsTable(...))             |
|  │   ├── STAGE 0 (Narrow Transformations: Scan -> Filter -> Project)    |
|  │   │   ├── Task 0 (Partition 0)                                      |
|  │   │   ├── Task 1 (Partition 1)                                      |
|  │   │   └── Task N (Partition N)                                      |
|  │   │                                                                  |
|  │   │   ================ [SHUFFLE EXCHANGE] ================           |
|  │   │                                                                  |
|  │   └── STAGE 1 (Wide Transformations: ShuffleRead -> Aggregation)     |
|  │       ├── Task 0 (Shuffle Partition 0)                               |
|  │       └── Task M (Shuffle Partition M)                               |
|  │                                                                      |
|  └── JOB 1 (Triggered by Action: df_summary.count())                    |
+-------------------------------------------------------------------------+

1. Spark Application

A Spark Application corresponds to the overarching driver process and set of executors provisioned for a notebook session or Lakeflow Job run. It persists for the lifespan of the cluster or job.

2. Jobs

A Job is initiated whenever a Spark action is evaluated. Because Spark uses lazy evaluation, transformations (such as .select(), .filter(), .join(), and .groupBy()) do not trigger computation on their own. When an action is called—such as .write.saveAsTable(), .count(), .collect(), .take(), or .show()—Spark compiles the accumulated transformation lineage into a Job and submits it to the DAG Scheduler.

3. Stages & Shuffle Boundaries

A Job is divided into one or more Stages. The boundary between stages is determined strictly by data dependencies:

• Narrow Dependencies (Pipelined Execution): Operations where each partition of the parent DataFrame is used by at most one partition of the child DataFrame (e.g., map(), filter(), select(), withColumn()). Spark collapses consecutive narrow transformations into a single stage using pipelining, executing them in memory without writing intermediate data to disk.
• Wide Dependencies (Shuffle Boundaries): Operations where multiple child partitions depend on data distributed across multiple parent partitions (e.g., groupBy(), join(), distinct(), orderBy(), repartition()). Wide dependencies require an Exchange (Shuffle), where data is serialized, partitioned across the network, written to executor disks, and re-read by downstream tasks. Every shuffle boundary forces the creation of a new Stage.

4. Tasks

A Task is the smallest unit of execution in Spark. Each stage is divided into a collection of identical tasks, with exactly one task spawned per data partition. The Spark driver schedules tasks onto available executor cores. If a stage processes 200 partitions on a cluster with 16 total cores, the stage executes across approximately 13 successive waves of 16 concurrent tasks.

---

3. Visualizing Query Execution DAGs in the SQL/DataFrame Tab

The SQL/DataFrame Tab provides a graphical representation of the physical execution plan compiled by the Catalyst Optimizer. Each node in the DAG represents a physical operator, annotated with runtime execution statistics:

+-------------------------------------------------------------------------+
|                 TYPICAL PHYSICAL PLAN OPERATOR GRAPH                    |
+-------------------------------------------------------------------------+
|                                                                         |
|      [ PhotonScan / FileScan parquet ]                                  |
|      - Number of output rows: 50,000,000                                |
|      - Data size: 4.2 GiB                                               |
|      - Files read: 128                                                  |
|                     │                                                   |
|                     ▼                                                   |
|      [ Filter (isNotNull(customer_id) AND amount > 0) ]                 |
|      - Number of output rows: 48,200,000                                |
|                     │                                                   |
|                     ▼                                                   |
|      [ HashAggregate (Partial) ]                                        |
|      - Keys: [customer_id]                                              |
|      - Functions: [partial_sum(amount), partial_count(1)]               |
|                     │                                                   |
|                     ▼                                                   |
|      [ Exchange (hashpartitioning(customer_id, 200)) ] <== SHUFFLE      |
|      - Shuffle records written: 1,200,000                               |
|      - Shuffle write size: 85.4 MiB                                     |
|                     │                                                   |
|                     ▼                                                   |
|      [ HashAggregate (Final) ]                                          |
|      - Keys: [customer_id]                                              |
|      - Functions: [sum(amount), count(1)]                               |
|                     │                                                   |
|                     ▼                                                   |
|      [ PhotonResult / Execute InsertIntoHadoopFsRelationCommand ]        |
|                                                                         |
+-------------------------------------------------------------------------+

Key Physical Operators

1. FileScan / PhotonScan: Reads underlying Delta/Parquet data files from ADLS Gen2. Metrics show partition filters applied, data size read, and total files scanned. A high file count with low byte size indicates a "small file problem".
2. Exchange: Represents a shuffle operation across worker nodes. The operator specifies the partitioning strategy:
   • hashpartitioning(keys, numPartitions): Used for groupBy and join operations to route identical keys to the same partition.
   • RoundRobinPartitioning(numPartitions): Used for generic repartitioning to balance partition sizes evenly.
   • SinglePartition: Indicates all data is being pulled to a single node (frequently caused by non-partitioned window functions like row_number().over(Window.orderBy("date"))), creating an extreme bottleneck.
3. SortMergeJoin (SMJ): Standard join operator for large-scale datasets. Requires both sides to be shuffled on join keys and sorted before merging.
4. BroadcastHashJoin (BHJ): Fast join operator where the smaller table is broadcast to all worker nodes, eliminating the shuffle of the large table.
5. HashAggregate: Two-phase aggregation engine. The first phase (Partial Aggregation) computes local aggregates on each partition before the shuffle; the second phase (Final Aggregation) computes global results after the shuffle Exchange.

---

4. Diagnosing Stragglers & Data Skew via Task Duration Quantiles

A straggler task occurs when one or a few tasks in a stage run significantly longer than all other tasks, causing the entire pipeline to stall while waiting for the stage to complete.

Analyzing the Task Metrics Summary Table

Inside the Stage Details page, the Spark UI presents a statistical quantile breakdown of all tasks executed within that stage:

Diagnosis Rules for Data Skew:

1. Duration Discrepancy: If the 75th percentile duration is under 3 seconds but the Max duration is tens of minutes, a severe straggler is present.
2. Shuffle Read Correlation: Examine the Shuffle Read Size / Records across the quantiles. If the Median is 1.2 MB but the Max task processed 14.2 GB, the straggler is caused by data skew (an uneven distribution of join/grouping keys sending massive data volumes to a single partition).
3. Hardware / GC Stragglers: If the Max task duration is extreme but its Shuffle Read Size is identical to the Median task, the issue is not data skew. Check the Task Deserialization Time, JVM GC Time, or executor node health on the Executors tab.

+-------------------------------------------------------------------------+
|                     TASK TIMELINE DIAGNOSTIC PROFILES                   |
+-------------------------------------------------------------------------+
|                                                                         |
|  1. HEALTHY UNIFORM STAGE EXECUTION                                     |
|  Core 1: [Task 1][Task 5][Task 9 ][Task 13]                             |
|  Core 2: [Task 2][Task 6][Task 10][Task 14]                             |
|  Core 3: [Task 3][Task 7][Task 11][Task 15]                             |
|  Core 4: [Task 4][Task 8][Task 12][Task 16]                             |
|  Stage Finish Time: 12 seconds                                          |
|                                                                         |
|  2. SEVERE STRAGGLER / SKEWED STAGE EXECUTION                           |
|  Core 1: [Task 1][Task 5][Task 9 ][================ Task 13 ===========]|
|  Core 2: [Task 2][Task 6][Task 10] (IDLE) ............................  |
|  Core 3: [Task 3][Task 7][Task 11] (IDLE) ............................  |
|  Core 4: [Task 4][Task 8][Task 12] (IDLE) ............................  |
|  Stage Finish Time: 18 minutes (Cores 2-4 starved waiting for Task 13!) |
+-------------------------------------------------------------------------+

---

5. Interpreting Physical Execution Plans in Code (df.explain())

In addition to the Spark UI, data engineers can print physical execution plans directly in notebook cells using df.explain(mode="formatted"):

# Generate formatted physical execution plan
df_joined = sales_df.join(customers_df, on="customer_id", how="inner")
df_joined.explain(mode="formatted")

Formatted Plan Output Analysis:

== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=true
+- == Final Plan ==
   *SortMergeJoin [customer_id#10], [customer_id#25], Inner
   :- *Sort [customer_id#10 ASC NULLS FIRST], false, 0
   :  +- AQEShuffleRead coalesced
   :     +- Exchange hashpartitioning(customer_id#10, 200), ENSURE_REQUIREMENTS, [plan_id=45]
   :        +- *Filter isnotnull(customer_id#10)
   :           +- *Scan ExistingRDD[customer_id#10, amount#11]
   +- *Sort [customer_id#25 ASC NULLS FIRST], false, 0
      +- AQEShuffleRead coalesced
         +- Exchange hashpartitioning(customer_id#25, 200), ENSURE_REQUIREMENTS, [plan_id=46]
            +- *Filter isnotnull(customer_id#25)
               +- *Scan ExistingRDD[customer_id#25, name#26]

Reading the Plan:

• The asterisk (*) prefixing operators (e.g., *SortMergeJoin, *Filter) signifies that Whole-Stage Code Generation (WSCG) has compiled these operators into a single optimized Java bytecode loop.
• AdaptiveSparkPlan isFinalPlan=true indicates that Adaptive Query Execution (AQE) updated the physical plan at runtime based on intermediate stage statistics.
• AQEShuffleRead coalesced proves that AQE dynamically merged small shuffle partitions into optimal sizes.