SQL warehouses (serverless/pro/classic)
Databricks · Compute
Theory
A SQL warehouse is compute built for SQL: the SQL Editor, dashboards, Genie, alerts and BI tools such as Power BI or Tableau connect to it. You choose a T-shirt Cluster Size (2X-Small to 4X-Large), an Auto Stop idle time and Scaling (minimum and maximum clusters, so more clusters are added when many queries queue).
There are three main types. Serverless warehouses run in Databricks' account, start in seconds and add Intelligent Workload Management; they are the default and recommended type where available. Pro warehouses run in your cloud account with Photon and Predictive IO, and are used when serverless is not available or when you need custom networking. Classic warehouses also run in your account with Photon only and give entry-level performance. For example, the BI team's Power BI reports point at a Small serverless warehouse with Scaling 1 to 3 and Auto Stop of 10 minutes.
Why we use it
SQL users and BI tools need fast, concurrent, always-available SQL without managing Spark clusters. Warehouses give a JDBC/ODBC endpoint, scale for concurrency and stop themselves when idle.
When to use it
Use a SQL warehouse for SQL queries, dashboards and BI connections. Use notebooks on serverless or a cluster for Python, ML or complex ETL. Prefer serverless warehouses; pick pro only for networking needs or regions without serverless.
How it works
- Need SQL compute for analysts or BI
- SQL Warehouses → Create SQL warehouse
- Is serverless available and no custom networking needed? Yes: Type: Serverless. No: Type: Pro (or Classic).
- Pick Cluster Size, Auto Stop and Scaling
- Grant Can use to the analyst group
- Connect the SQL Editor or BI tool
Syntax
Sidebar → SQL Warehouses → Create SQL warehouse
Fields: Name, Cluster Size, Auto Stop, Scaling (min/max clusters), Type (Serverless | Pro | Classic)
Connection details tab: Server hostname, HTTP pathExample
-- Run in SQL Editor on the 'bi_wh' warehouse
SELECT c.city,
COUNT(*) AS orders,
SUM(o.amount) AS revenue
FROM main.sales.orders o
JOIN main.sales.customers c USING (customer_id)
GROUP BY c.city
ORDER BY revenue DESC;Key points
- Serverless is the default type where enabled and starts in seconds
- Cluster Size makes each query faster; Scaling (more clusters) handles more concurrent users
- Auto Stop stops the warehouse after idle minutes; it restarts on the next query
- Pro and classic warehouses run in your cloud account and start more slowly
- BI tools connect with the Server hostname and HTTP path from Connection details