Serverless warehouse billed around the clock for a 9-to-5 dashboard
Symptoms
- DBU usage is flat across nights and weekends
- Query History shows no statements after 6pm
- The warehouse is already Running every morning without anyone starting it
Interactive Databricks interview questions on SQL Warehouses: compute shaped for BI. Practice with the matching lesson. Serverless vs Pro vs Classic, the caches behind a fast dashboard, and why size and cluster count are different dials.
Question 1 of 10
The BI team wants to point Tableau at Databricks. Why hand them a SQL warehouse instead of the cluster they already have access to?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 4 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 10 of 10 questions.
The BI team wants to point Tableau at Databricks. Why hand them a SQL warehouse instead of the cluster they already have access to?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: A warehouse is a SQL endpoint built for many short concurrent queries: Photon on by default, result caching, auto-stop, and extra clusters when concurrency rises.
Why: An all-purpose cluster runs notebooks and BI inside the same Spark application, so one runaway cell starves the dashboard and there is no per-query profile for the analyst. A warehouse gives JDBC/ODBC endpoints, Query History with a profile per statement, and scale-out for concurrency instead of fighting notebooks for slots.
Common mistake: Handing BI the JDBC URL of the team's interactive cluster because it is already warm.
Follow-up they will ask: Classic, Pro, or Serverless — which would you pick and what changes?
One analyst is fine, but at 9am with forty people the dashboards crawl. Do you bump the T-shirt size?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: No — size is scale-up for one query's horsepower; concurrency needs more clusters, so raise max clusters.
Why: Cluster size (2X-Small through 4X-Large) decides how much compute a single query can use; min/max clusters decides how many queries run in parallel before they queue. If Query History shows long queue time against short execution time, it is a concurrency problem, and upsizing just makes each idle cluster more expensive.
Common mistake: Jumping from Small to 2X-Large, watching the bill grow, and finding the queue still there.
Follow-up they will ask: Where in Query History do you separate queue time from execution time?
Finance says the SQL warehouse bill tripled but query volume is flat. Where do you look?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: At uptime, not queries: auto-stop disabled, min clusters pinned above 1, a warehouse left running, or someone changed the size or type.
Why: Warehouses bill DBUs for the time they are up, so an idle warehouse with no auto-stop bills all night for zero queries. Chart warehouse DBUs from system.billing.usage next to query counts per warehouse, then check the warehouse settings for auto-stop, min clusters, size, and a Classic-to-Pro or Serverless switch.
Senior depth: Uptime × size × SKU is the bill; query count is not in that formula. Flat query volume with rising DBU hours means idle time, so the fix is auto-stop plus min clusters back to 1 — not a smaller size that makes every real query slower. Serverless changes the tradeoff because startup is seconds, so aggressive auto-stop stops being painful.
Common mistake: Opening the query profile of the slowest statement — the queries did not change, the running hours did.
Follow-up they will ask: What auto-stop value would you set for a 9-to-6 BI workload, and what breaks if it is too aggressive?
The same dashboard query took 3 seconds yesterday afternoon and 90 seconds after the overnight load. The SQL is identical. Explain it.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: The result cache was invalidated by the new table version and the warehouse's local disk cache is cold for the new files, so the first run pays a full read.
Why: Databricks serves byte-identical queries from a result cache, and that cache is invalidated when the underlying Delta table commits a new version. Below it, each warehouse cluster keeps a local SSD disk cache of Parquet it has already read, which is also empty after auto-stop, a restart, or a rewrite by OPTIMIZE — and if the load produced many small files, that cold read is expensive on top.
Senior depth: Three caches with different lifetimes: the result cache (per identical query, killed by a new table version), the disk cache on the cluster's local SSDs (killed by restart or auto-stop), and Spark's in-memory cache that BI never touches. I would warm it deliberately with a small scheduled job that runs the top dashboard queries right after the load commits, and keep gold compacted so the cold read is cheap when it does happen.
Common mistake: Blaming Photon or asking for a bigger warehouse when the first query after every load is simply uncached.
Follow-up they will ask: How would you make 9am fast without keeping the warehouse hot all night?
A dashboard is pointed at a warehouse that is currently stopped. What does the user experience, and what starts it?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: The first query starts the warehouse on demand, so the user waits through startup — seconds on Serverless, minutes on Classic or Pro.
Remember these
Why: A stopped warehouse is not an error state; an incoming query through the JDBC or ODBC endpoint triggers a start and then runs. On Classic and Pro that start means waiting for VMs in your own cloud account, which is why the first dashboard load of the morning feels like the platform is broken. On Serverless the compute comes from a pool Databricks already maintains, so the same start takes seconds. Query History and the warehouse monitoring tab show the start and any queue time, so you can tell a slow query from a slow start. The predictable wrong reaction is to disable auto stop, which converts a one-time morning wait into a bill that runs all night.
Common mistake: Turning auto stop off so nobody ever waits, then being unable to explain the invoice.
Follow-up they will ask: How do you make 9am fast without paying for the whole night?
The CFO asks why Serverless costs more per DBU than Classic. Answer it.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: The Serverless rate includes the compute infrastructure Databricks runs for you; on Classic and Pro you pay Databricks less per DBU and your cloud provider separately for the VMs.
Remember these
Why: Classic and Pro warehouses launch instances in your own cloud account, so the real cost is the Databricks DBU rate plus a cloud invoice for VMs, disks, and the idle minutes you failed to stop. Serverless runs in the Databricks account from a warm pool, so there is one rate and no second bill, and startup is seconds rather than VM provisioning. Which is cheaper depends on utilisation: bursty BI with genuine idle gaps usually wins on Serverless because you can set a short auto stop without punishing users, while a warehouse busy twelve hours a day is a closer comparison. The axis that is not about money is where the compute runs, which is a data-residency conversation for some organisations. Compare them with DBU hours per warehouse from system.billing.usage against query counts from Query History, not with list prices side by side.
Common mistake: Comparing per-DBU rates without adding the cloud VM cost and the idle hours to the Classic side.
Follow-up they will ask: What would make you keep a Classic warehouse despite the startup time?
A single dashboard query is slow. Walk me through the query profile, in order.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: Queue versus execution time first, then files pruned versus files read, then the operator holding the wall clock, then spill and the task-duration tail.
Remember these
Why: Query History gives you the statement and its total time; the profile gives a tree of operators with time, rows, and bytes each. Start by splitting queue time from execution time, because queue time is a concurrency problem and nothing in the plan will fix it. Next read the scan node: files pruned against files read tells you whether partitioning or clustering did any work, and bytes from cache against bytes from cloud storage tells you whether the disk cache was cold. Then find the operator that owns the wall clock — usually a shuffle, a join, or a point where the estimated row count is wildly below actual, which means statistics misled the optimizer. Spill bytes and a long tail in task duration point at skew or an undersized warehouse. Resizing is defensible only after those four reads, and it is the last move, not the first.
Senior depth: The profile is a decision tree, and each branch has a different owner. Queue time goes to the warehouse configuration, poor pruning goes to the table owner for clustering or partitioning, a bad join order goes to statistics and the model, and spill with a skewed task tail goes to the data distribution. I make people name which branch they are on before they file a request, because 'the warehouse is too small' is the one conclusion the profile almost never supports on its own.
Common mistake: Opening the profile, seeing a large bytes-read number, and resizing the warehouse.
Follow-up they will ask: Which number tells you the file layout is the problem rather than the warehouse size?
Two analysts run identical SQL on the same warehouse; one gets a result in 200ms and the other waits 30 seconds. Explain it.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: The warehouse had scaled out and their queries landed on different clusters — one had the Parquet in its local disk cache, the newly added one did not.
Remember these
Why: Scale-out puts several identical clusters behind one endpoint. A byte-identical query against unchanged tables can return from the result cache with no compute at all, but once you miss that, the read happens on whichever cluster admitted the query, and only that cluster's local SSD disk cache is warm. A cluster that just started to absorb concurrency has an empty disk cache, so it reads from S3 or ADLS and pays full I/O. The profile shows it plainly as bytes read from cache versus bytes read from cloud storage. The same cold-read effect appears after auto stop, after a restart, and after OPTIMIZE rewrites the files the cache was holding, which is why the first query after a maintenance job is reliably the slow one.
Senior depth: I describe it as three caches with three lifetimes: the result cache keyed on identical text and invalidated by a new table version, the per-cluster disk cache killed by restart or scale-out, and Spark's in-memory cache that BI never touches. Once that is clear, the fix is deliberate warming rather than more hardware — a small scheduled job that runs the top dashboard queries after the load commits, and compacted gold tables so the cold read is cheap when it happens. Variance between users is a symptom of scale-out working, not of something broken.
Common mistake: Concluding the slow user has a network or driver problem when the two queries ran on different clusters.
Follow-up they will ask: What does OPTIMIZE do to a warm disk cache, and when does that matter?
Dashboards are fine except for twenty minutes every hour, when a dbt run blocks everything. Design the fix.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: Isolate the workloads on separate warehouses so ETL-shaped queries cannot occupy the admission slots that interactive BI needs.
Remember these
Why: A warehouse admits a bounded number of concurrent queries per cluster and queues the rest, and a long transforming statement holds its slot for its entire runtime. Sort Query History by duration for that warehouse and the picture is unambiguous: a handful of dbt statements running for minutes with dozens of two-second dashboard queries queued behind them. Raising max clusters does not solve it the way people hope, because one query runs on one cluster — it just adds capacity next to the blockage. Two warehouses is the real fix: one sized for the heavy serial transforms, one scaled out for concurrency, which also makes spend attributable per workload in system.billing.usage. If the dbt models are large MERGE-shaped writes, the better answer is that they do not belong on a warehouse at all and should run on job compute.
Senior depth: I separate workloads by shape, not by team. Interactive BI wants many short queries and therefore scale-out and a small size; transforms want one query to go fast and therefore size and no competition. Mixing them means the loudest workload sets everyone's latency. Once they are split I add a statement timeout on the BI warehouse so one runaway ad-hoc query cannot recreate the problem, and I watch queue time per warehouse as the signal rather than waiting for someone to complain.
Common mistake: Raising max clusters and expecting the heavy query to spread across them.
Follow-up they will ask: Which part of a dbt project should not be on a warehouse at all?
You are standardising warehouses for a 300-person analytics org. What do you fix in the standard, and what do you leave to teams?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: Fix type, auto stop, tags, and default catalog; leave size and max clusters to the team that owns the workload and its bill.
Remember these
Why: Standardise the settings people get wrong silently: Serverless wherever residency allows so a short auto stop is painless, a short auto stop itself, custom tags so each warehouse's DBUs land attributable in system.billing.usage, and a default catalog so nobody accidentally queries hive_metastore. Set the granularity too — one warehouse per BI domain rather than per person, because per-person warehouses multiply idle time and per-org warehouses destroy isolation. Leave sizing with the owner, because only they know the query shape, but hand them the rule that queue time means more clusters and long execution on large scans means a bigger size. Then monitor the thing that drifts: DBU hours per warehouse against query counts, so an idle warehouse is visible within a day rather than at month end. The governance point is that a warehouse is a permanently visible cost surface, so it needs a named owner the same way a gold table does.
SELECT usage_metadata.warehouse_id, sum(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 7 DAYS
GROUP BY 1
ORDER BY dbus DESCSenior depth: I split the decision into safety and performance. Safety settings are platform-owned and non-negotiable, because the failure mode is a silent bill or an accidental hive_metastore query. Performance settings are team-owned, because the failure mode is their own dashboard being slow and they get the feedback immediately. Then I give every owner the same two numbers each week — DBU hours and query count for their warehouse — so sizing conversations are about evidence instead of entitlement.
Common mistake: One giant always-on warehouse for the whole org because it looked cheaper than several small ones.
Follow-up they will ask: How would you handle the one team whose workload genuinely needs a 4X-Large?