What you will be able to do
- Follow a query through its statuses on a SQL warehouse and read where its time went
- Compare serverless, pro and classic warehouses by engine features and startup time
- Explain how a warehouse admits, queues and scales out under concurrent load
- Explain what keeps a warehouse active and what Auto Stop does when it goes idle
1.A query's path through the warehouse
Once you submit a query, the SQL warehouse is responsible for running it. Query history shows each step: "The query is tagged with its current status: Queued, Running, Finished, Failed, or Cancelled." A query is Queued while it waits for capacity on the warehouse, Running while the warehouse executes it, and then Finished, Failed or Cancelled. Query history also records the compute type that ran the query and a unique ID for that execution.
The details panel shows where the time went. Wall-clock duration covers the time from the start of scheduling to the end of execution. Below that summary, "A detailed breakdown of scheduling, query optimization and file pruning, and execution time appears below the summary." The panel also shows aggregated task time, which Databricks describes as "the combined time it took to execute the query across all cores of all nodes." To see how the warehouse executed the query step by step, open the query profile, a detailed view of the query's execution plan that you use to find bottlenecks.
Checkpoint 1 of 5· Check yourself
A query's aggregated task time is much LONGER than its wall-clock duration. What does that tell you?
Aggregated task time adds up the work done on every core of every node. It exceeds wall-clock time when the warehouse runs tasks in parallel. It comes out shorter when tasks had to wait for available nodes.
“It can be significantly longer than the wall-clock duration if multiple tasks are excuted in parallel.”Source: docs.databricks.com
2.Warehouse types: which engine features run your query
Every warehouse runs SQL, but warehouse types run it with different features. Databricks SQL supports serverless, pro and classic warehouses. They differ in three performance features. Photon is "The built-in vectorized query engine on Databricks." Predictive IO is a suite of features that speeds up selective scans. Intelligent Workload Management (IWM) uses AI-powered prediction to give workloads the right amount of resources quickly.
| Warehouse type | Photon | Predictive IO | Intelligent Workload Management | Where compute runs |
|---|---|---|---|---|
| Serverless | Yes | Yes | Yes | Databricks account |
| Pro | Yes | Yes | No | Your AWS account |
| Classic | Yes | No | No | Your AWS account |
Startup time affects every query that wakes a stopped warehouse. A serverless warehouse is usually ready in seconds. "A pro SQL warehouse takes several minutes to start up (typically approximately 4 minutes)," and classic is about the same. Without IWM, pro and classic warehouses also react less quickly when demand changes a lot over time. Classic gives only entry-level performance, and Databricks positions it for interactive data exploration. Databricks recommends serverless when it is available and lists ETL, business intelligence and exploratory analysis as workloads it handles well. Pro is the choice when serverless isn't available in your region, or when custom networking has to reach databases in your own network for federation.
Checkpoint 2 of 5· Match them up
Match each warehouse type to its description.
Tap a term, then the definition that fits it.
Each step down from serverless loses a performance feature: pro has no IWM, and classic has neither IWM nor Predictive IO.
“A classic SQL warehouse supports Photon but does not support Predictive IO or Intelligent Workload Management.”Source: docs.databricks.com
3.Admitting, queuing and scaling concurrent queries
A warehouse is made of one or more clusters. "Cluster size (for example, X-Small, Medium, Large) determines the compute resources available for a single cluster", so cluster size sets how much compute one cluster can apply to a query. The number of clusters sets how many queries the warehouse can run at once. When a query arrives, the warehouse either admits it or holds it in a queue.
On serverless, IWM makes this decision: "When a new query arrives, IWM predicts its resource requirements and checks available capacity." If there is capacity, the query starts immediately. If not, it is queued. IWM keeps watching the queue, and as wait times grow, the autoscaler adds clusters to work through queued queries. When demand drops, IWM scales back down. Classic and pro warehouses "use a manual scaling model where you configure the number of clusters". You set a minimum and a maximum, and these SKUs have a fixed limit of one cluster per 10 concurrent queries. Their autoscaler adds clusters based on the estimated time to process all running and queued queries. If a query has waited in the queue for 5 minutes, the warehouse scales up.
Checkpoint 3 of 5· Check yourself
Every cluster on a pro warehouse is busy when a new query arrives. What happens to the query?
When a warehouse reaches its limits, new queries are queued, not rejected. On classic and pro warehouses, a query that waits in the queue for 5 minutes makes the warehouse scale up.
“When the warehouse hits its limits, queries get queued, not rejected.”Source: docs.databricks.com
Size and cluster count solve different problems, and the signs of each show up in different places. If queries spill to disk, the warehouse is too small for them: the query profile's Bytes spilled to disk metric "indicates that the warehouse size may be too small." If queries keep waiting, the warehouse doesn't have enough concurrency. On the monitoring tab, a consistent Peak Queued Queries value above 0 means you may need a larger cluster size or more clusters. A bigger cluster does not make every query faster, though. For simple, short-running queries, a larger size can be slower because of data shuffling.
Checkpoint 4 of 5· Exam question
During a company-wide reporting event, dozens of analysts simultaneously open the same dashboard connected to a single SQL warehouse, and queries begin queuing noticeably. The warehouse is already set to the largest available cluster size. What change would most directly relieve this queuing?
Correct answer: A — Increase the maximum cluster count so the warehouse can spin up additional clusters and distribute the concurrent queries across them
- A. Raising the maximum cluster count is correct because SQL warehouses handle high concurrency by adding clusters that share the incoming query load, rather than by making one cluster larger; more clusters means more queries can run in parallel instead of queuing.
- B. Cluster size (t-shirt size) mainly affects how much compute a single query gets to run faster, not how many separate queries can run at once, so growing it further does little to relieve queuing from many simultaneous users.
- C. Classic warehouses are not specifically optimized for concurrency; they offer entry-level performance without the intelligent workload management that helps distribute concurrent load, so switching to Classic would not resolve this queuing.
- D. Photon is a vectorized query engine that speeds up query execution rather than a resource competing with concurrency, so disabling it would not free capacity for more simultaneous queries and could slow individual queries down.
Sources5
4.When the warehouse stays active, and when it stops
A warehouse's work on a query doesn't end when the query finishes computing. "SQL warehouses remain active when queries are running or fetching results." Most results come back within seconds. Large result sets, slow fetching, or queries that a client never closes can keep a warehouse active for several minutes. The monitoring page labels these periods as Other activity: the warehouse "was active due to queries fetching results or open sessions without active queries." You can stop a query that is stuck fetching from its query profile panel.
When nothing is running, fetching or holding a session open, the warehouse is idle. Idle time still costs money: "Idle SQL warehouses continue to accumulate DBU and cloud instance charges until they are stopped." The Auto Stop setting handles this by stopping the warehouse after it has been idle for a set number of minutes. The default is 10 minutes for serverless and 45 minutes for pro and classic. Once a warehouse has stopped, the next query restarts it, so serverless's fast startup matters most when Auto Stop is set to a short interval. Separately, Databricks recycles clusters that have run for more than 24 hours. Existing queries finish on the old cluster while new queries move to the replacement.
Checkpoint 5 of 5· Check yourself
A pro warehouse with default settings finished its last query 30 minutes ago and has had no activity since. What is true?
The Auto Stop default for pro and classic warehouses is 45 minutes, so after 30 idle minutes the warehouse is still running. An idle warehouse keeps accumulating charges until it stops.
“Pro and classic SQL warehouses: The default is 45 minutes, which is recommended for typical use.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A larger cluster size makes every query run faster.Why is that wrong?
Larger clusters speed up complex queries on large datasets. For simple, short-running queries, a larger size can be slower because of data shuffling.
Covered in Admitting, queuing and scaling concurrent queries
2.Pro warehouses have every serverless performance feature and differ only in where the compute runs.Why is that wrong?
Pro supports Photon and Predictive IO but not Intelligent Workload Management. That makes it less responsive to changing demand, and it starts in minutes rather than seconds.
Covered in Warehouse types: which engine features run your query
3.A warehouse costs nothing once its queries have finished running.Why is that wrong?
A warehouse stays active while results are being fetched, and an idle warehouse keeps accumulating charges until Auto Stop stops it.
Covered in When the warehouse stays active, and when it stops
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The query is tagged with its current status: Queued, Running, Finished, Failed, or Cancelled.”
↩︎ A query's path through the warehouse“A detailed breakdown of scheduling, query optimization and file pruning, and execution time appears below the summary.”
↩︎ A query's path through the warehouse“the combined time it took to execute the query across all cores of all nodes.”
↩︎ A query's path through the warehouse“It can be significantly longer than the wall-clock duration if multiple tasks are excuted in parallel.”
↩︎ Checkpoint - 2.
“A detailed view of a query's execution plan. Use it to identify bottlenecks and optimization opportunities.”
↩︎ A query's path through the warehouse - 3.
“Photon: The built-in vectorized query engine on Databricks.”
↩︎ Warehouse types: which engine features run your query“Predictive IO: A suite of features for speeding up selective scan operations in SQL queries.”
↩︎ Warehouse types: which engine features run your query“A pro SQL warehouse takes several minutes to start up (typically approximately 4 minutes)”
↩︎ Warehouse types: which engine features run your query“Use a classic SQL warehouse to run interactive queries for data exploration with entry-level performance and Databricks SQL features.”
↩︎ Warehouse types: which engine features run your query“the compute layer exists in your AWS account”
↩︎ Warehouse types: which engine features run your query“A pro SQL warehouse supports Photon and Predictive IO, but does not support Intelligent Workload Management.”
↩︎ Exam trap 2“Rapid startup time (typically between 2 and 6 seconds).”
↩︎ Prediction“A classic SQL warehouse supports Photon but does not support Predictive IO or Intelligent Workload Management.”
↩︎ Checkpoint - 4.
“Databricks recommends using serverless SQL warehouses when available.”
↩︎ Warehouse types: which engine features run your query - 5.
“Cluster size (for example, X-Small, Medium, Large) determines the compute resources available for a single cluster.”
↩︎ Admitting, queuing and scaling concurrent queries“When a new query arrives, IWM predicts its resource requirements and checks available capacity.”
↩︎ Admitting, queuing and scaling concurrent queries“If wait times increase, the autoscaler quickly provisions more clusters to process queued queries.”
↩︎ Admitting, queuing and scaling concurrent queries“Classic and pro warehouses use a manual scaling model where you configure the number of clusters.”
↩︎ Admitting, queuing and scaling concurrent queries“These SKUs have a fixed limit of one cluster per 10 concurrent queries.”
↩︎ Admitting, queuing and scaling concurrent queries“If a query waits in the queue for 5 minutes, the warehouse scales up.”
↩︎ Admitting, queuing and scaling concurrent queries“The maximum number of queries in a queue for all warehouse types is 1,000.”
↩︎ Admitting, queuing and scaling concurrent queries“Inspect execution plans for metrics such as Bytes spilled to disk, which indicates that the warehouse size may be too small.”
↩︎ Admitting, queuing and scaling concurrent queries“A consistent value above 0 indicates that you may need a larger cluster size or more clusters.”
↩︎ Admitting, queuing and scaling concurrent queries - 6.
“SQL warehouses remain active when queries are running or fetching results.”
↩︎ When the warehouse stays active, and when it stops“Other activity: The warehouse was active due to queries fetching results or open sessions without active queries.”
↩︎ When the warehouse stays active, and when it stops - 7.
“Auto Stop determines whether the warehouse stops if it's idle for the specified number of minutes.”
↩︎ When the warehouse stays active, and when it stops“Serverless SQL warehouses: The default is 10 minutes, which is recommended for typical use.”
↩︎ When the warehouse stays active, and when it stops“Existing queries continue to run on the old cluster until they finish.”
↩︎ When the warehouse stays active, and when it stops“Idle SQL warehouses continue to accumulate DBU and cloud instance charges until they are stopped.”
↩︎ Exam trap 3“Pro and classic SQL warehouses: The default is 45 minutes, which is recommended for typical use.”
↩︎ Checkpoint
Also cited
“If you have only simple, short-running queries, don't increase the size (might be slower due to data shuffling).”
↩︎ Exam trap 1“When the warehouse hits its limits, queries get queued, not rejected.”
↩︎ Checkpoint