05.2 SQL Warehouses
Phase 5 — SQL in Databricks
This chapter explains the compute layer behind Databricks SQL. The objective is not to memorize warehouse sizes. The objective is to understand how to select, size, scale, isolate, monitor and govern SQL compute for enterprise workloads.
1. Core mental model
A SQL warehouse is a Databricks compute resource used to execute SQL workloads.
SQL workload
|
+--------------+--------------+
| | |
latency concurrency cost
| | |
+--------------+--------------+
|
v
SQL Warehouse
|
+-----------+-----------+
| |
cluster size cluster count
single-query concurrent capacity
performance throughput
| |
+-----------+-----------+
|
v
Query execution
|
v
Unity Catalog
|
v
Delta / lakehouse
Architect rule: size for query complexity; scale for concurrency.
Databricks recommends serverless SQL warehouses when available. See Connect to a SQL warehouse.
2. Where the warehouse fits
User / BI / API / Job
|
v
SQL statement
|
v
SQL Warehouse
|
+--> authentication / authorization
+--> parsing
+--> analysis
+--> optimization
+--> physical execution
|
v
Unity Catalog
|
v
Delta / lakehouse data
|
v
Cloud storage
The warehouse is the compute layer, not the authoritative data store.
3. Why have a dedicated SQL warehouse?
SQL can also run from Spark notebooks, but enterprise SQL workloads have different requirements:
- interactive latency
- high concurrency
- autoscaling
- workload isolation
- BI connectivity
- predictable operations
- query monitoring
- minimal infrastructure management
Typical consumers include:
- analysts
- BI developers
- analytics engineers
- dashboards
- applications
- scheduled SQL jobs
Current Databricks compute guidance recommends serverless SQL warehouses for SQL-based analytics and reporting. See Compute selection recommendations.
4. SQL warehouse types
Current Databricks documentation describes:
| Type | Typical use |
|---|---|
| Serverless | Recommended starting point for many BI, ETL and exploratory workloads |
| Pro | Serverless unavailable or custom networking requirements |
| Classic | Entry-level or legacy scenarios |
Serverless
Serverless is usually the preferred starting point because Databricks manages the compute infrastructure and dynamically manages capacity.
Strengths:
- fast startup
- automatic scaling
- reduced infrastructure administration
- high concurrency
- good fit for BI
- good fit for exploratory SQL
Serverless uses Intelligent Workload Management to dynamically manage workload and capacity.
Pro
Use Pro when serverless is unavailable or when requirements such as custom networking make Pro more appropriate. Pro compute runs in the customer cloud account.
Classic
Classic is an entry-level option. For new enterprise designs, choose it only when current platform constraints justify it.
See SQL warehouse types.
5. Size versus scale
This distinction is fundamental.
Increase warehouse size when one query needs more resources
Typical evidence:
- long execution time
- memory pressure
- disk spill
- large joins
- expensive aggregations
- large scans
Bigger cluster
|
v
More resources per execution
|
v
Potentially faster individual query
Increase cluster count when concurrency is the problem
Typical evidence:
- queries waiting for capacity
- many users at the same time
- dashboard latency during peak hours
- independent queries competing for the warehouse
More clusters
|
v
More simultaneous capacity
|
v
Less queueing under concurrency
Databricks exposes running queries, queued queries and cluster count in warehouse monitoring. See Monitor a SQL warehouse.
6. Diagnose before resizing
A mature troubleshooting sequence is:
Slow workload
|
v
Is it queued?
/ \
yes no
| |
v v
Capacity Query execution
problem problem
| |
v +--> Query Profile
Scale +--> data scanned
capacity +--> joins
+--> aggregations
+--> spill
+--> data layout
Do not respond to every slow query by increasing compute.
A 40-second query can mean:
Queue = 30 sec
Execution = 10 sec
or:
Queue = 0 sec
Execution = 40 sec
Those are completely different problems.
7. Warehouse sizing is not table size
Do not say:
Our lake is 20 TB, therefore we need an X-Large warehouse.
Warehouse selection depends on:
- bytes scanned per query
- column pruning
- filtering
- joins
- aggregations
- query complexity
- concurrency
- SLA
- data layout
- spill
- workload frequency
- result size
Two organizations can have the same storage footprint and require completely different compute.
8. Serverless workload management
The high-level serverless model is:
New query
|
v
Estimate workload
|
+--> capacity available? ---- yes ---> execute
|
no
|
v
Queue / acquire capacity
|
v
Execute
|
v
Demand decreases
|
v
Scale down
The architect-level takeaway is:
Serverless shifts more resource-management responsibility from the platform team to the managed service.
See SQL warehouse sizing, scaling and queuing.
9. Autoscaling
Think about two boundaries:
minimum clusters
|
v
normal capacity
|
v
maximum clusters
|
v
peak concurrency capacity
Too low can cause:
- queueing
- poor dashboard experience
- SLA violations
Too high can cause:
- unnecessary compute consumption
- inefficient workload isolation
- poor cost discipline
Current Databricks guidance recommends using observed queueing and query behavior to tune capacity rather than guessing. See Warehouse sizing and scaling.
10. Workload isolation
One warehouse for every workload is often a poor enterprise design.
SQL platform
|
+--------------+--------------+
| | |
BI SLA Ad-hoc ETL
| | |
BI warehouse Explore WH ETL WH
BI
- high concurrency
- short queries
- strict latency
- business-critical
Ad-hoc analytics
- unpredictable
- occasional large scans
- exploratory
ETL
- scheduled
- potentially long-running
- resource-intensive
Separate warehouses when their SLAs, ownership, concurrency or cost characteristics conflict.
See Lakehouse compute architecture guidance.
11. Enterprise warehouse pattern
A mature platform might use:
Enterprise SQL
|
+-------------------+-------------------+
| | |
BI / Prod Analytics ETL
| | |
SQL-WH-BI SQL-WH-ADHOC SQL-WH-ETL
| | |
+-------------------+-------------------+
|
Unity Catalog
|
Gold / Silver data
This is not mandatory. The decision should be driven by SLA, concurrency, workload volatility, security boundaries and cost ownership.
12. Auto-stop and idle cost
Idle compute can create unnecessary cost.
Use auto-stop for workloads where occasional startup latency is acceptable.
For always-on BI, overly aggressive auto-stop can conflict with latency expectations.
Current Databricks warehouse configuration exposes Auto Stop and scaling controls. See Create a SQL warehouse.
Cost rule: optimize idle time without violating the business SLA.
13. Query Profile
When a query is slow, inspect the Query Profile before changing compute.
Use it to identify:
- expensive operators
- rows processed
- execution time
- memory consumption
- full scans
- exploding joins
- expensive aggregations
- inefficient execution paths
See Query Profile.
The production loop is:
Symptom
↓
Evidence
↓
Root cause
↓
Targeted change
↓
Benchmark
14. Query History
Query History is an operational record for SQL execution.
Useful information includes:
- status
- duration
- user
- compute
- statement text
- rows returned
- I/O metrics
- execution details
- query profile
The Query History system table is available as:
system.query.history
Example:
SELECT
statement_id,
executed_by,
execution_status,
total_duration_ms,
waiting_at_capacity_duration_ms,
execution_duration_ms,
start_time,
statement_text
FROM system.query.history
WHERE start_time >= CURRENT_TIMESTAMP() - INTERVAL 7 DAYS
ORDER BY total_duration_ms DESC;
See Query History and Query History system table.
15. Queue time versus execution time
Use this decomposition:
Total latency
=
queue / scheduling time
+
execution time
+
result fetching
Case A — capacity problem
Queue = 30 sec
Execution = 10 sec
Investigate concurrency and warehouse capacity.
Case B — query problem
Queue = 0 sec
Execution = 40 sec
Investigate SQL and data layout.
Case C — result-consumption problem
Queue = 0 sec
Execution = 5 sec
Fetching = 60 sec
Investigate the client or result consumption.
Warehouse monitoring can identify fetching activity that keeps a warehouse active. See Monitor a SQL warehouse.
16. Warehouse access versus data access
Do not confuse:
- permission to use a warehouse
- permission to run a query
- permission to access a table
- permission to create an object
Think in layers:
User / Group
|
v
Workspace access
|
v
Warehouse permission
|
v
Unity Catalog privileges
|
v
Catalog / Schema / Table
|
v
Data
Use groups and service identities for production access wherever practical.
17. BI architecture
A common enterprise pattern is:
Gold tables
|
v
Unity Catalog
|
v
BI SQL Warehouse
|
+-----------+-----------+
| | |
Power BI Tableau AI/BI
The important architectural boundaries are:
- governed data
- SQL compute
- BI workload
- user access
- monitoring
18. SQL warehouse versus job compute
| Requirement | Typical compute |
|---|---|
| Interactive SQL | SQL warehouse |
| BI dashboard | SQL warehouse |
| SQL-heavy workflow | SQL warehouse / SQL task |
| PySpark transformation | Serverless or job compute |
| Python libraries | Serverless/classic job compute depending on requirement |
| RDD API | Classic compute where required |
Current Databricks compute-selection guidance recommends serverless compute for most automated workloads and SQL warehouses for SQL tasks. See Compute selection recommendations.
19. Cost model
A useful conceptual model is:
Total SQL cost
=
compute consumed
+
idle time
+
unnecessary concurrency capacity
+
inefficient queries
Four optimization levers:
- Reduce work — filter early, project fewer columns, optimize joins.
- Reduce idle time — auto-stop and eliminate abandoned workloads.
- Improve concurrency efficiency — scale according to demand and isolate workloads.
- Fix inefficient SQL — an inefficient query can dominate the cost of an otherwise small warehouse.
20. Production monitoring
A production warehouse should have measurable signals.
Performance
- p95/p99 latency
- execution duration
- queue duration
- spill
- I/O
- rows returned
Capacity
- running queries
- queued queries
- cluster count
- capacity wait
Reliability
- failed queries
- canceled queries
- repeated failures
Cost
- DBU consumption
- idle time
- workload ownership
- cost per business workload
The exact dashboards should reflect the organization's SLA.
21. Hands-on lab
Objective
Prove the difference between query performance and concurrency capacity.
Step 1 — Baseline
Run a representative aggregation:
SELECT
order_priority,
COUNT(*) AS order_count,
SUM(total_price) AS revenue
FROM demo.sales_orders
GROUP BY order_priority
ORDER BY revenue DESC;
Record:
- warehouse type
- size
- duration
- execution time
- input
- output
- spill
Step 2 — Query Profile
Identify:
- scan
- aggregation
- exchange/shuffle
- longest operator
- I/O
Step 3 — Concurrency test
Run multiple representative queries.
Observe:
- queueing
- active clusters
- waiting-at-capacity time
- total latency
Step 4 — Change one variable
Test either:
- warehouse size
or:
- maximum cluster count
Do not change both simultaneously.
Step 5 — Benchmark
| Test | Size | Max clusters | Queue | Runtime | Spill | Cost |
|---|---|---|---|---|---|---|
| Baseline | ||||||
| Size changed | ||||||
| Concurrency changed |
Your conclusion must explain why the change helped or did not help.
22. Troubleshooting matrix
| Symptom | First investigation |
|---|---|
| Query queued | Capacity / cluster count |
| Single query slow | Query Profile |
| High spill | Query plan + warehouse size |
| BI slow only at peak | Concurrency |
| Warehouse always running | Auto-stop / sessions |
| One query dominates | SQL optimization |
| Many small queries queue | Concurrency / isolation |
| Large scan | Filtering / data layout |
| Cost unexpectedly high | Query history + idle time |
| Dashboard latency varies | Workload isolation + queueing |
23. Architect exercise
Design SQL compute for:
Workload A — Executive BI
- 500 users
- short queries
- strict SLA
- business-critical
Workload B — Analysts
- 50 users
- unpredictable
- occasional large scans
Workload C — SQL ETL
- nightly
- large transformations
- 2-hour SLA
Workload D — Application analytics
- high concurrency
- low latency
Your design must justify:
- number of warehouses
- warehouse type
- size
- scaling
- auto-stop
- permissions
- monitoring
- workload isolation
- failure behavior
- metric that triggers resizing
There is no universally correct configuration. A good architecture connects every choice to workload evidence.
24. Interview questions
Beginner
What is a SQL warehouse?
A compute resource optimized for SQL workloads in Databricks.
What are the main warehouse types?
Serverless, Pro and Classic.
Intermediate
What is the difference between size and cluster count?
Size affects resources available to process work; cluster count primarily addresses concurrent capacity.
A query is queued. What do you investigate?
Capacity, concurrent workload, cluster count and queueing metrics.
A single query is slow with no queue. What do you investigate?
Query Profile, scan volume, joins, aggregations, spill and data layout.
Architect
Why use multiple warehouses?
To isolate workloads whose latency, concurrency, security or cost requirements conflict.
How do you prove that resizing helped?
Use a controlled benchmark and compare queueing, execution time, spill, throughput and cost.
When would you choose Pro over Serverless?
When serverless is unavailable or custom networking requirements make Pro appropriate.
25. Architect mental model
Remember:
A SQL warehouse is a workload-management boundary, not a bucket of compute that you keep making bigger.
The architect aligns:
Business SLA
↓
Workload pattern
↓
Concurrency
↓
Warehouse type
↓
Warehouse size
↓
Scaling
↓
Workload isolation
↓
Query optimization
↓
Governance
↓
Cost
If you can explain that chain, you understand SQL warehouse architecture.
26. Current official references
- Databricks SQL
- Connect to a SQL warehouse
- SQL warehouse types
- SQL warehouse sizing, scaling and queuing
- Create a SQL warehouse
- Monitor a SQL warehouse
- Query History
- Query Profile
- Compute selection recommendations
Next
05.3 SQL Queries
We now move from SQL compute architecture into SQL itself: SELECT, filtering, expressions, NULL semantics, grouping, joins, subqueries, CTEs, date/time handling and production query patterns.