Databricks DE · Topic 10.1

SQL Warehouses

Compute optimized for SQL & BI — serverless/pro/classic, t-shirt sized, autoscaling for concurrency, auto-stop for cost, Photon-accelerated. This deep dive pairs the mechanism with config-as-code.

serverlesssize vs scaleauto_stop_minsAsset Bundle

The one-liner

A SQL Warehouse is purpose-built compute for SQL/BI: pick a type (serverless recommended), a size (power per query), and autoscaling (clusters for concurrency); it auto-starts on demand and auto-stops when idle, with Photon for speed.

☕ Analogy: an espresso machine for the analytics café — sized for the rush, spins up more machines when the queue grows (autoscaling), switches off when empty (auto-stop). The all-purpose cluster is the full kitchen; the warehouse does one fast job.

Size vs scale — tune the right knob

▸ size = power per query · max clusters = concurrent users

Size (per query):

Max clusters:

Types & lifecycle — click each

Serverless (recommended) — starts in seconds, elastic, fully Databricks-managed in their account; Intelligent Workload Management routes queries. Lowest ops & idle cost.

Pro — runs in your account with more control; slower start than serverless.

Classic — legacy, manual provisioning. Use only when serverless/pro aren't options.

Auto-start / auto-stop — a query, job, JDBC/ODBC connection, or dashboard auto-starts the warehouse; idle past auto_stop_mins auto-stops it (cold start next time; serverless cold starts are seconds).

1 · Configure in the UI

SQL → SQL Warehouses → Create.
Set name, size (per-query power), scaling (min/max clusters), auto-stop.
Toggle Serverless (recommended) for instant start & lowest idle cost.
It auto-starts on a query/job/JDBC connection/dashboard.

2 · …or as code (Asset Bundle + CLI)

# databricks.yml (resources)
resources:
  sql_warehouses:
    bi_wh:
      name: bi_serverless
      cluster_size: "Small"            # per-query power (2X-Small…4X-Large)
      min_num_clusters: 1
      max_num_clusters: 4              # concurrency (autoscale; max 40)
      auto_stop_mins: 10               # idle shutdown
      warehouse_type: PRO
      enable_serverless_compute: true   # → Serverless

databricks bundle deploy -t prod
# or imperatively:
databricks warehouses create --json '{"name":"bi_serverless","cluster_size":"Small","enable_serverless_compute":true,"auto_stop_mins":10}'

Point a job's sql_task at it

sql_task: { warehouse_id: "${resources.sql_warehouses.bi_wh.id}", file: { path: ../sql/report.sql } }

Types at a glance

	Serverless	Pro	Classic
Startup	Seconds	Slower	Slower
Managed by	Databricks	You	You (legacy)
Use when	Default SQL/BI	In-account control	Legacy only

Why it matters

BI/SQL demand is bursty & concurrent — warehouses scale and auto-stop to balance speed vs cost far better than a manual cluster.
"What compute powers a dashboard / SQL query?" → SQL Warehouse (serverless), not an all-purpose cluster.

📈 Real-world use case: a dashboard hits a serverless warehouse — auto-starts on first query, autoscales during the 9am rush, auto-stops after hours so you don't pay for idle compute.

Uses, edge cases & limitations

Uses: dashboards, ad-hoc SQL, BI tools, scheduled SQL in jobs, Genie.
Edge cases: don't confuse size (power per query) with max clusters (concurrency, ≤ 40); aggressive auto-stop trades cost for cold-start latency.
Limitation: warehouses run SQL — for arbitrary Python/Spark ETL use jobs/all-purpose compute; serverless availability varies by region.

Common gotchas

❌ Running SQL/dashboards on an all-purpose cluster instead of a SQL Warehouse.
❌ Bumping size when the problem is concurrency (raise max clusters) — or vice versa.
❌ Disabling auto-stop → paying for an idle warehouse overnight.
❌ Assuming Classic is current — serverless is the recommended default.
❌ Clicking the warehouse together in prod and never capturing it as a bundle.