Home › Learn › How reads and writes work › 8 · Plan and UI

Delta Lake and Spark · from zero to advanced

Query plan, Spark UI and query profile: what each shows and when to look

Read a real query plan bottom-up, then learn what the Spark UI tabs, the Databricks query profile, DESCRIBE DETAIL, DESCRIBE HISTORY and the delta log each tell you, with a symptom-to-tool table.

Reading a plan

The plan is a recipe: read it from the bottom

HashAggregate(sum(amount))4 final sum of the partial sumsExchange SinglePartition3 shuffle: send partials to one placeHashAggregate(partial_sum(amount))2 partial sum inside each partitionColumnarToRow1b convert column batches to rowsFileScan parquet [amount] Batched: true1a read only the amount columnRead plans from the bottom up: data flows from the scan at the bottom to the result at the top.

A real plan from the run, annotated.

*(2) HashAggregate(keys=[], functions=[sum(amount#52)], output=[sum(amount)#302])
+- Exchange SinglePartition, ENSURE_REQUIREMENTS, [id=322]
   +- *(1) HashAggregate(keys=[], functions=[partial_sum(amount#52)], output=[sum#1298])
      +- *(1) ColumnarToRow
         +- FileScan parquet [amount#52] Batched: true, DataFilters: [], Format: Parquet, Location: PreparedDeltaFileIndex(1 paths)[file:/private/tmp/claude-501/-Users-is
  • The bottom node is the scan. FileScan parquet [amount] lists only the columns it will read, and the plan lists the filters it handed to the reader as PushedFilters. For the one-day query the plan showed PushedFilters: [IsNotNull(event_date), EqualTo(event_date,2026-01-10)].
  • An Exchange is a shuffle. Count them: each one is a stage boundary and a network cost.
  • The join node tells you the strategy: BroadcastHashJoin, SortMergeJoin and so on.
  • Stars like *(1) mark steps fused into one generated-code stage.

When to look at what

Six places to look

EXPLAINbefore runningscan filters, join kind, exchangesSpark UI: SQL / Stagesafter runningtime, bytes, shuffle, spill, skewQuery profile (Databricks)after runningpruning %, operator time, memoryDESCRIBE DETAILtable nownumFiles, sizeInBytesDESCRIBE HISTORYtable pastwhat each write changed_delta_lograw truthactions, stats, DVs

Each tool answers a different question.

SymptomLook atWhat you will see
A query is slow and you want to know why before running itEXPLAIN FORMATTEDScan filters, columns read, join kind, number of exchanges
A query is slow after runningSpark UI, Stages and SQL tabs; Databricks query profileWhich stage or operator took the time; skewed tasks; spills
A filter should have skipped files but did notScan node metrics: files read, bytes read; the plan's PushedFiltersHow many files the scan actually opened
Reads got slower over weeksDESCRIBE DETAILnumFiles up and file size down means small files
A table got big or an update was expensiveDESCRIBE HISTORYPer commit: files added and removed, rows copied (see the write page)
You need to know exactly what a write did_delta_log/*.jsonThe add, remove and deletion vector actions
Memory errors on the driverExecutors tab, plan for collect or a broadcastResult size, broadcast size

The Spark UI

What each tab is for

TabUse it to
JobsSee the actions that ran and how long each took
StagesSee one stage's tasks: duration spread shows skew, shuffle read and write, spill
SQL / DataFrameSee the plan as a graph with row counts and time per node
StorageSee what is cached and how big it is
ExecutorsSee memory, task counts and garbage collection per executor
EnvironmentSee the settings that were in force
Measured. The same numbers are available programmatically. In the local runs the scan node reported number of files read (20 for select *, 1 for the one-day filter), size of files read, number of output rows, scan time and metadata time. The metadata time was 113 ms for select * and 68 ms for the filtered query, which is the driver-side cost of listing and pruning files: small here, but it grows with file count.

On Databricks

The query profile

The Databricks query profile (from the docs) shows a query summary with filter icons that indicate the percent of data pruned during scanning, aggregated task time, I/O, and a breakdown of scheduling, optimisation, file pruning and execution time. The graph shows operator nodes with time spent, memory peak and rows, a top-operators panel, performance insights, a link to the Spark UI, and JSON export.

Not confirmed: the docs page I read does not list the exact scan metric names (files read, files pruned, bytes read), the join type labels in the graph, or the spill metrics. Open a real profile in your workspace to see them.

Delta's own views

DESCRIBE DETAIL, HISTORY and the log

These answer questions about the table rather than one query. The names below are the real counters recorded by the write experiments:

WhereCounters seenTells you
DESCRIBE DETAILnumFiles, sizeInBytesIs the table made of many small files?
DESCRIBE HISTORY (operationMetrics)numAddedFiles, numRemovedFiles, numCopiedRows, numUpdatedRows, numDeletedRows, numDeletionVectorsAddedHow much a write rewrote
commitInfo in the logoperation, isolationLevel, isBlindAppend, readVersion, engineInfoWhat kind of write and under what isolation

Next page: how writes work under the hood, where these counters are used.

FAQ

Questions people ask

How do I read a Spark query plan?

From the bottom up. The scan at the bottom shows the columns read and the filters pushed down; each Exchange above it is a shuffle and a stage boundary; join nodes show the strategy. Data flows upward to the final result.

Where do I see how many files a query read?

In the scan node's metrics in the Spark UI SQL tab (number of files read, size of files read), and on Databricks in the query profile, which shows the percent of data pruned during scanning.

How do I know if my Delta table has small files?

Run DESCRIBE DETAIL and compare numFiles with sizeInBytes. Many files with a small average size is the small file problem.

← 7 · Photon9 · Write path →