What you will be able to do
- Open a query profile from query history and explain when no profile is available
- Interpret the query details panel metrics, including wall-clock duration, aggregated task time and pruning percentages
- Read the operator DAG and the Top operators panel to locate the slowest part of a query
- Use performance insights to turn a profile into a concrete fix
Key concept
Query profile — A visual record of how one query actually ran. It is a graph (DAG) of operators such as Scan, Join and Shuffle, and each operator carries metrics like time spent, rows and peak memory. Because the metrics are per operator, you can see which step caused the slowdown instead of guessing from the total runtime.
1.Getting to a query profile, and when there isn't one
The query profile is the main diagnostic tool for SQL warehouses and serverless compute. On classic compute clusters, the Spark UI does the same job. The profile is built for troubleshooting: it shows every query operator with its metrics, and it helps you find common mistakes such as exploding joins or full table scans. You can see a profile if you own the query or if you have at least CAN MONITOR permission on the SQL warehouse that ran it.
The usual route goes through Query History. Open it from the sidebar and click a query's name. A query details panel opens with the status, the compute, the query text, the key metrics and a small preview of the DAG. From that panel, click See query profile to open the full graph. You can also get there from the SQL editor (the elapsed-time link under the results), from a notebook attached to a SQL warehouse or serverless compute (See performance), from the Query History tab in the Lakeflow pipelines UI, and from the jobs UI.
Checkpoint 1 of 5· Put it in order
Put the steps for opening a detailed query profile from query history in order.
- 1.Click See query profile to open the detailed DAG
- 2.Click the name of the query to open the query details panel
- 3.Click Query History in the sidebar
Clicking a query in Query History opens the details panel. The full DAG opens only from that panel's See query profile button.
“Click See query profile to open the detailed DAG.”Source: docs.databricks.com
To share a profile with a colleague who has CAN MANAGE on the query, send them its URL. Anyone else, including people outside the workspace, can get it as a JSON download, which they load with Import query profile (JSON) from the Query History kebab menu. An imported profile lives only in the current browser session and is not saved to the workspace.
2.Reading the query details panel
Before you open the graph, the summary already narrows things down. Query wall-clock duration is the total time from the start of scheduling to the end of execution. Below it is a breakdown into scheduling, query optimization and file pruning, and execution time. If most of the time went to scheduling, the warehouse was busy, and rewriting the query won't help. The query metrics under the query text carry filter icons on some metrics, and these show the percentage of data pruned during the scan. That number is your first data-skipping signal: a low pruning percentage on a selective query means files are being read that the filters should have skipped. Input/Output (IO) shows how much data was read and written.
Nothing is broken. Aggregated task time adds up the work done on every core of every node, so heavy parallelism makes it far larger than wall-clock time. If it is *shorter* than wall-clock time, tasks were waiting for nodes to become available.
Checkpoint 2 of 5· Check yourself
In the query details panel, which element shows directly how well data skipping worked for a scan?
The filter icons show the percentage of data pruned during scanning, which tells you how many files the filters let the scan skip.
“The filter icons that appear with some metrics indicate the percent of data pruned during scanning.”Source: docs.databricks.com
Sources1
3.The operator DAG and the Top operators panel
The detailed profile has summary tabs on the left (Details, Top operators, Query text) and the DAG on the right. The graph can display Time spent, Memory peak or Rows on each node. Click a metric to switch between them. Clicking an operator opens its detailed metrics and description. The Top operators tab lists the most expensive operators, which is usually the quickest way to find what to fix. Two things can trip you up. First, some non-Photon operations run as a group, so every member shows the parent operator's value for a shared metric. Second, metrics for operations that are unlikely to be bottlenecks are hidden by default. Turn on verbose mode to see all of them. For Databricks SQL queries you can also choose Open in Spark UI.
| Operator | What happened |
|---|---|
| Scan | Data was read from a data source and output as rows |
| Filter | Input was reduced by a criterion such as a WHERE clause |
| Join | Rows from multiple relations were combined into one set |
| Union | Rows from relations with the same schema were concatenated |
| Shuffle | Data was redistributed or repartitioned across executors |
| Hash / Sort | Rows were grouped by a key and aggregated (SUM, COUNT, MAX) |
Checkpoint 3 of 5· Match them up
Match each profile feature to what it gives you.
Tap a term, then the definition that fits it.
Top operators ranks cost. Verbose mode shows the operations hidden by default. Memory peak is one of the graph's switchable metrics. The kebab menu opens Databricks SQL queries in the Spark UI.
“Top operators: Opens the Top operators panel which shows the most expensive operators used in your query.”Source: docs.databricks.com
Checkpoint 4 of 5· Exam question
A data engineer runs a Spark SQL query that filters a 500 GB Delta table on `event_timestamp` and `customer_region`. The query profile shows the scan node pruned only 8% of files during scanning, even though the table is partitioned by `ingestion_date` and was Z-ordered on `customer_id` last month. Which change is most likely to raise the pruning percentage the query profile reports for this query?
Correct answer: A — Recluster the table with Liquid Clustering keys on `event_timestamp` and `customer_region`, aligning the file-level min and max statistics with this query's filter columns so the scanner can skip non-matching files.
- A. Clustering on the columns the query actually filters on lets Delta Lake's file-level statistics match the predicate, so the scanner can eliminate files whose min/max range falls outside the filter and the pruning percentage in the query profile rises.
- B. The shuffle partition count controls how output data is redistributed after the scan completes; it has no effect on which input files the scan node decides to read, so the pruning metric stays unchanged.
- C. Local SSD caching reduces the latency of re-reading the same files but does not change the file-level statistics used for pruning, so a differently filtered query still scans the same file set.
- D. Compacting files without reclustering on the filter columns reduces the total file count but leaves each file's min/max ranges spanning the same wide value ranges, so the filter still cannot eliminate many files.
Sources1
4.Performance insights: the profile's own diagnosis
You don't always have to read the DAG yourself. When a query runs, Databricks may attach performance insights. The query details panel shows a summary of them, ranked by projected effect on total task duration, and the Performance insights tab in the detailed profile has the full detail for each one. Each insight comes with a recommendation, such as compacting small files, collecting statistics or resizing the warehouse. If an insight is actionable, Optimize opens Genie Code. For query changes, Genie Code rewrites the query and asks for your approval. For table or compute changes, it describes the recommended actions in plain language.
Some insights report work that has already been done. These carry an Accelerated label and need no action. One example: Databricks chose a broadcast join based on how similar queries ran before.
Checkpoint 5 of 5· Check yourself
An insight in the Performance insights tab has an Accelerated label. What should you do?
Accelerated insights describe optimizations that were already applied during execution, such as an automatic broadcast join. They are informational.
“They appear in the Performance insights tab with an Accelerated label and do not require action.”Source: docs.databricks.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.If a re-run query shows no profile, you lack permission and need CAN MANAGE.Why is that wrong?
Queries answered from the query cache have no profile at all. Make a trivial change to the query, such as changing or removing the LIMIT, to force a real execution. To view a profile you need ownership or CAN MONITOR on the warehouse.
Covered in Getting to a query profile, and when there isn't one
2.Aggregated task time far above wall-clock duration means the query wasted time.Why is that wrong?
Aggregated task time adds up work across all cores of all nodes, so parallel execution is expected to push it well above wall-clock time.
Covered in Reading the query details panel
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“You can discover and fix common mistakes in SQL statements, such as exploding joins or full table scans.”
↩︎ Getting to a query profile, and when there isn't one“you must have at least CAN MONITOR permission on the SQL warehouse that executed the query”
↩︎ Getting to a query profile, and when there isn't one“you can download the query profile as a JSON object”
↩︎ Getting to a query profile, and when there isn't one“A detailed breakdown of scheduling, query optimization and file pruning, and execution time appears below the summary.”
↩︎ Reading the query details panel“It can be shorter than the wall-clock duration if tasks waited for available nodes.”
↩︎ Reading the query details panel“Shuffle operations are expensive with regard to resources because they move data between executors on the cluster.”
↩︎ The operator DAG and the Top operators panel“Some non-Photon operations are executed as a group and share common metrics.”
↩︎ The operator DAG and the Top operators panel“By default, metrics for some operations are hidden. These operations are unlikely to be the cause of performance bottlenecks.”
↩︎ The operator DAG and the Top operators panel“The query details panel shows a summary of the most impactful insights, ranked by their projected effect on total task duration.”
↩︎ Performance insights: the profile's own diagnosis“For an insight that requires a query change, Genie Code rewrites the query and presents the changes for your approval.”
↩︎ Performance insights: the profile's own diagnosis“You can identify the slowest part of a query execution at a glance and assess the impacts of modifications to the query.”
↩︎ Key concept“To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT.”
↩︎ Exam trap 1“It can be significantly longer than the wall-clock duration if multiple tasks are excuted in parallel.”
↩︎ Exam trap 2“Click See query profile to open the detailed DAG.”
↩︎ Checkpoint“A query profile is not available for queries that run from the query cache.”
↩︎ Prediction“The filter icons that appear with some metrics indicate the percent of data pruned during scanning.”
↩︎ Checkpoint“Top operators: Opens the Top operators panel which shows the most expensive operators used in your query.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/lakehouse-architecture/deployment-guide/observabilityOfficial docs
“For serverless compute and SQL warehouses, use query profile to analyze and optimize query performance.”
↩︎ Getting to a query profile, and when there isn't one
Also cited
“They appear in the Performance insights tab with an Accelerated label and do not require action.”
↩︎ Checkpoint