Executing Queries with Databricks SQL and SQL Warehouses
What a SQL warehouse actually is
A SQL warehouse is a compute resource dedicated to Databricks SQL. Types:
- Serverless - Databricks-managed infrastructure, fastest start, no cluster configuration, scales instantly. Preferred for BI and dashboards.
- Pro - your cloud account, supports the full feature set including Photon acceleration.
- Classic - legacy; limited features.
Each warehouse has a size (2X-Small through 4X-Large), a cluster count range (min/max for auto-scaling), and an auto-stop timer. Photon is the vectorized engine that speeds up SQL workloads; it is on by default for Pro and Serverless.
Writing queries in the SQL editor
The editor supports multiple statements, CTEs, window functions, QUALIFY, MERGE, PIVOT, and parameter markers (:param). Results can be downloaded, visualized inline, or saved as a query that dashboards and alerts reuse.
Traps
- Warehouse size vs. cluster count. Size controls per-cluster memory/CPU; the min/max cluster range controls concurrency. Sizing up does not help if the problem is too few clusters.
- Auto-stop. A warehouse that stopped must start before the query runs; startup time is included in the user's wait, so set auto-stop thoughtfully for interactive use.
- Result caching. Identical queries against unchanged data return cached results almost instantly - do not misread that as a performance win from your tuning.
- Assuming one warehouse serves all workspaces; warehouses are workspace-scoped resources.
Rule of thumb
Size for the largest single query, scale for the number of concurrent users. A dashboard with many viewers wants a small/medium size with a high max-cluster count, not a 4X-Large single cluster.
Worked example
Ten analysts share a dashboard and complain about queueing at 9 a.m. Fix: set min clusters to 1 so someone is always warm, raise max clusters to 4, and keep size at Medium. Queue time drops without paying for a huge single warehouse.