What you will be able to do
- Open a query profile from Query History, the SQL editor, a notebook, the pipelines UI or the jobs UI
- Read a profile's summary, DAG and Top operators to find the operator that is causing the problem
- Explain why a profile can be missing, and who is allowed to view one
- Query the compute and job system tables to link a failed task to the compute it ran on and that compute's resource usage
1.Opening a query profile and reading its summary
For SQL warehouses and serverless compute, the main diagnostic tool is the query profile. It is a visual record of how one query ran. You can use it to see each operator's time, rows and memory, to find the slowest part of the query at a glance, and to "discover and fix common mistakes in SQL statements, such as exploding joins or full table scans."
The usual way in is Query History in the sidebar. Click a query's name and a details panel opens. It shows:
- the query's status - user and compute details - the query's UUID - the full statement and key metrics - a preview of the DAG and a See longest operators button - the wall-clock duration, broken down into scheduling, optimization and pruning, and execution - the aggregated task time: the combined time it took to execute the query across all cores of all nodes
For errors, check the status first. "The query is tagged with its current status: Queued, Running, Finished, Failed, or Cancelled."
The fix is easy: "To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT." Permissions are the other thing to watch. You can view a profile if you own the query, or if you have at least CAN MONITOR on the warehouse that ran it.
Query History isn't the only way in:
- SQL editor: the link with the elapsed time and row count opens the details panel. - Notebook attached to a SQL warehouse or serverless compute: See performance under the cell. - Lakeflow pipelines UI: a Query History tab. - Jobs UI: profiles for jobs run on SQL warehouses and serverless compute.
Checkpoint 1 of 4· Check yourself
A data engineer neither owns a query nor has any permission on the SQL warehouse that ran it. What is the minimum grant that lets them open its query profile?
Viewing a profile needs ownership of the query or at least CAN MONITOR on the warehouse that executed it. CAN MANAGE on the query is what you need to share a profile URL, which is a different action.
“you must have at least CAN MONITOR permission on the SQL warehouse that executed the query”Source: docs.databricks.com
Sources1
2.Reading the DAG, Top operators and performance insights
The detailed profile has summary tabs on the left: Details, Top operators and Query text. On the right is the operator DAG. "The graph view shows metrics such as Time spent, Memory peak, and Rows." Click a metric to switch the graph to it, and click an operator to see its details. The quickest way to find the operator causing trouble is the Top operators tab, which shows the most expensive operators in the query.
Knowing what each operator does tells you what an expensive one means. A Scan reads data from a source, so an expensive scan with little pruning suggests a full table scan. A Join whose output rows far exceed its input rows suggests an exploding join. Shuffle is costly because it moves data between executors. Hash / Sort groups rows by key for aggregates, and Filter returns the subset of rows that meets a condition.
Checkpoint 2 of 4· Match them up
Match each query profile operator to what it does
Tap a term, then the definition that fits it.
These descriptions come from the documented list of the most common operations. Shuffle is singled out as expensive because it moves data between executors.
“Shuffle operations are expensive with regard to resources because they move data between executors on the cluster.”Source: docs.databricks.com
Three more things to know:
- "By default, metrics for some operations are hidden." Turn on verbose mode to see every operation and extra metrics. - For Databricks SQL queries, the kebab menu has Open in Spark UI, which takes you to the stage and task view. - Databricks may attach performance insights. The details panel lists the most impactful ones, ranked by their projected effect on total task duration. The Performance insights tab shows each one in full, and Optimize opens Genie Code to act on them.
To get help from someone else, you have two options. If they have CAN MANAGE on the query, share the profile URL. Otherwise, download the profile as JSON for them to import. An imported profile only lasts for the browser session.
Checkpoint 3 of 4· Exam question
A scheduled job cluster fails during startup, before any tasks run, and the job run shows status `FAILED` with no Spark UI available for that run. Where should the engineer look first to find the root cause of the failure?
Correct answer: A — The cluster's init script logs, since a failure this early with no Spark UI usually means a bootstrap or init script errored before Spark started.
- A. When a cluster fails before Spark itself comes up, there is no Spark UI to inspect, so the init script and cluster event logs are the first place to look for the bootstrap-time error that prevented the cluster from starting.
- B. The Spark UI depends on a running Spark driver to populate; if the cluster failed before Spark started, there is no Executors tab data to retain, which is consistent with the scenario stating no Spark UI is available.
- C. A query profile is generated for an executed SQL query or command on a running compute resource; a cluster that never finished starting has no query to profile, so this artifact would not exist for this failure.
- D. The job and task run system tables store run-level timing, status, and termination codes for reporting, but they do not capture the full stdout/stderr text a cluster produces during startup, which lives in the cluster's log files instead.
Sources1
3.System tables: compute history and resource usage
Logs and profiles cover one run. System tables let you query diagnostic history across the whole account. The compute tables in system.compute cover classic all-purpose, jobs and Lakeflow pipelines compute:
| Table | What it records | Troubleshooting use |
|---|---|---|
| system.compute.clusters | Full history of compute configurations over time (slow-changing dimension) | What configuration (node types, DBR version, access mode, init scripts) the compute had when it failed |
| system.compute.node_timeline | Minute-by-minute utilization for each instance | CPU, memory, swap, network and disk pressure on the driver or workers |
| instance_events | State transitions of classic compute instances | When instances changed state |
| system.compute.node_types | Available node types with cores, memory and GPUs | Whether a node type is big enough |
| instance_pools | Instance pool configurations | Pool settings behind the driver_instance_pool_id and worker_instance_pool_id columns in clusters |
node_timeline is the closest thing to a post-mortem resource monitor. Each row covers one minute of one instance, and the driver flag tells you whether that instance was the driver. cpu_wait_percent is the "Percentage of time the CPU spent waiting for I/O". mem_swap_percent is the "Percentage of memory usage attributed to memory swap". disk_free_bytes_per_mount_point shows ephemeral disk filling up.
In clusters, cluster_source tells you what kind of compute it was: "All job compute is logged as JOB". Pipelines show up as PIPELINE or PIPELINE_MAINTENANCE. data_security_mode records the access mode, which matters because it decides who can read the logs:
| data_security_mode value | Access mode |
|---|---|
| USER_ISOLATION | Standard |
| SINGLE_USER | Dedicated |
| NONE | No isolation shared |
To trace a failed job, start with system.lakeflow.job_task_run_timeline. Its result_state column records the outcome of each task run. For runs longer than an hour that are split across several rows, only the row for the end of the run has it filled in. Its compute_ids column lists the job clusters, interactive clusters and SQL warehouses the task used. Join those IDs to cluster_id in system.compute.clusters and system.compute.node_timeline to see the configuration and the resource usage behind the failure.
Checkpoint 4 of 4· Check yourself
A job task failed overnight, and you suspect the driver ran out of memory. Which system table shows the driver's memory and swap usage minute by minute?
node_timeline has one row per instance per minute, with a driver flag and the mem_used_percent and mem_swap_percent columns. clusters only records configuration, and node_types only records hardware specs.
“node_timeline: Includes minute-by-minute records of your compute's utilization metrics.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.If a query profile is missing, the query must have failed before it ran, or you lack permissions.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 bypass the cache.
2.Importing a shared query profile JSON saves it in your workspace for later.Why is that wrong?
An imported profile is loaded into your browser session only and is not saved in the workspace. You have to import it again each time you want to view it.
Covered in Reading the DAG, Top operators and performance insights
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.”
↩︎ Opening a query profile and reading its summary“The query is tagged with its current status: Queued, Running, Finished, Failed, or Cancelled.”
↩︎ Opening a query profile and reading its summary“You can access query profiles for jobs run on SQL warehouses and serverless compute.”
↩︎ Opening a query profile and reading its summary“Top operators: Opens the Top operators panel which shows the most expensive operators used in your query.”
↩︎ Reading the DAG, Top operators and performance insights“For Databricks SQL queries, you can also view the query profile in the Spark UI.”
↩︎ Reading the DAG, Top operators and performance insights“ranked by their projected effect on total task duration”
↩︎ Reading the DAG, Top operators and performance insights“To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT.”
↩︎ Exam trap 1“When you import a query profile, it is dynamically loaded into your browser session and does not persist in your workspace.”
↩︎ Exam trap 2“A query profile is not available for queries that run from the query cache.”
↩︎ Prediction“you must have at least CAN MONITOR permission on the SQL warehouse that executed the query”
↩︎ Checkpoint“Shuffle operations are expensive with regard to resources because they move data between executors on the cluster.”
↩︎ Checkpoint - 2.
“The cluster table is a slow-changing dimension table that contains the full history of compute configurations over time”
↩︎ System tables: compute history and resource usage“Percentage of time the CPU spent waiting for I/O.”
↩︎ System tables: compute history and resource usage“instance_events: Captures state transitions of classic compute instances.”
↩︎ System tables: compute history and resource usage“node_timeline: Includes minute-by-minute records of your compute's utilization metrics.”
↩︎ Checkpoint - 3.
“Table path: system.lakeflow.job_task_run_timeline”
↩︎ System tables: compute history and resource usage“The compute_ids array contains IDs of job clusters, interactive clusters, and SQL warehouses used by the job task”
↩︎ System tables: compute history and resource usage