Setting Up and Configuring SQL Warehouses
Who this is for:
Architecture / Concept Overview: Setting Up and Configuring SQL Warehouses
A SQL warehouse is an elastic cluster of compute nodes managed by Databricks. When a query arrives, the warehouse routes it through the Photon engine, reads Delta Lake data from cloud storage, and returns results to the calling surface.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
flowchart LR
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
CLIENT[SQL Editor / BI Tool / API] -->|SQL Statement| ROUTER[Query Router]
ROUTER --> PHOTON[Photon Engine]
PHOTON --> CACHE[Result Cache]
PHOTON --> DELTA[Delta Lake Files<br/>S3 / ADLS / GCS]
CACHE --> CLIENT
DELTA --> PHOTON
CLIENT:::source
ROUTER:::processing
PHOTON:::processing
CACHE:::serving
DELTA:::storage
*Figure 1 — Query lifecycle inside a SQL warehouse, from client submission through Photon execution to result caching.*
Scaling behaviour differs across warehouse types. Serverless warehouses abstract all infrastructure, while pro and classic give more control.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
graph TD
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
IDLE[Warehouse Idle] -->|Query arrives| START[Start / Resume]
START --> EXEC[Execute Queries]
EXEC -->|Concurrency exceeds threshold| SCALE[Scale Out<br/>Add clusters]
SCALE --> EXEC
EXEC -->|No queries for auto-stop period| STOP[Auto-Stop]
STOP --> IDLE
IDLE:::source
START:::ingestion
EXEC:::processing
SCALE:::processing
STOP:::source
*Figure 2 — Warehouse lifecycle: start, execute, scale-out, and auto-stop.*
Key Terms
Prerequisites and Setup
- Workspace admin or
CAN MANAGEprivilege on SQL warehouses - Unity Catalog enabled on the workspace
- Cloud networking configured (for pro warehouses: VPC peering or Private Link)
Step-by-Step Implementation
Configuration Reference
| Setting | UI Label | API Field | Recommended Value |
|---|---|---|---|
| Size | Cluster size | cluster_size | 2X-Small for dev, Small–Medium for prod |
| Type | Warehouse type | warehouse_type | PRO or serverless |
| Max clusters | Max scaling | max_num_clusters | 1 (dev), 2–10 (prod based on concurrency) |
| Auto-stop | Auto stop | auto_stop_mins | 10 (dev), 30 (prod) |
| Photon | Enable Photon | enable_photon | true |
| Spot policy | Spot instance policy | spot_instance_policy | COST_OPTIMIZED for non-SLA |
| Channel | Channel | channel.name | CHANNEL_NAME_CURRENT |
| Tags | Tags | tags.custom_tags | Team, env, cost-centre |