4.2 SQL Warehouses Sizing & Management

4.2 SQL Warehouses Sizing & Management

In Databricks SQL, query processing is powered by SQL Warehouses (formerly known as SQL Endpoints). A SQL Warehouse is a specialized, fully managed compute resource optimized specifically for executing SQL queries, running ETL transformations, serving Business Intelligence (BI) dashboards (such as PowerBI, Tableau, and Databricks AI/BI Dashboards), and powering AI/BI Genie spaces. Unlike general-purpose Databricks clusters used for data engineering notebooks or machine learning pipelines, SQL Warehouses abstract away cloud infrastructure configuration, delivering instant elasticity, automatic optimization, and vectorized query execution via the Photon engine.

Compute Types: Serverless vs. Pro vs. Classic

Databricks SQL Warehouses are available in three compute types, each tailored to distinct operational requirements, security boundaries, and startup latency SLAs:

1. Serverless SQL Warehouses: Serverless warehouses run on compute infrastructure managed directly within the Databricks cloud account rather than the customer's cloud Virtual Private Cloud (VPC) or Virtual Network (VNet). Because Databricks maintains a warm pool of compute resources, Serverless warehouses start up in 2 to 6 seconds. Serverless warehouses feature intelligent auto-scaling, automatic software patching, and aggressive idle auto-stop policies down to 1 minute, drastically reducing total cost of ownership (TCO).
2. Pro SQL Warehouses: Pro warehouses run inside the customer's cloud VPC/VNet. They provide advanced operational capabilities, including support for Python User-Defined Functions (UDFs), Predictive Optimization, query federation to external data sources, and advanced geospatial features. However, because cloud VMs must be provisioned on-demand inside the customer's cloud subscription, Pro warehouses take approximately 4 minutes to cold-start.
3. Classic SQL Warehouses: Classic warehouses represent standard SQL compute running in the customer VPC. They provide core SQL query execution but lack support for advanced features like Python UDFs, serverless instant startup, and predictive optimizations.

Warehouse Sizing & T-Shirt Sizes

Databricks SQL Warehouses simplify compute provisioning using a standard T-Shirt Sizing model ranging from 2X-Small up to 4X-Large. Each size increment doubles the underlying cluster compute resources (CPU cores, RAM, and network bandwidth) and proportionally increases the Databricks Unit (DBU) consumption rate per hour.

Vertical vs. Horizontal Scaling (Scale-Up vs. Scale-Out)

Understanding when to scale up versus scale out is a critical competency for the Databricks Certified Data Analyst exam:

• Scaling Up (Increasing T-Shirt Size): Increases the compute capacity of a single cluster. Choose a larger T-Shirt size when executing individual queries that process multi-terabyte datasets, perform complex window aggregations, or execute heavy multi-table joins. Larger sizes provide more memory per node, preventing disk spilling during hash joins and sorts.
• Scaling Out (Multi-Cluster Auto-Scaling): Adds additional identical clusters in parallel to handle high user concurrency. When multiple users or automated BI dashboards execute queries simultaneously, a single cluster can become saturated, causing queries to wait in a queue. Multi-cluster auto-scaling specifies a Min Clusters (minimum 1) and Max Clusters (up to 30) boundary.

Databricks SQL manages multi-cluster auto-scaling using two distinct scaling policies:

• Low Latency: Aggressively provisions new clusters as soon as query queuing is detected. Recommended for interactive BI dashboards with tight response SLAs.
• High Throughput / Standard: Waits until a sustained queue backlog builds before launching additional clusters, optimizing for cost efficiency over instantaneous response times.

Auto-Stop, Auto-Resume & Cost Management

To eliminate idle compute costs, SQL Warehouses support automatic lifecycle management features:

• Auto-Stop: Automatically shuts down the SQL Warehouse after a specified period of inactivity (no queries executed). For Serverless warehouses, auto-stop can be configured as low as 1 minute (default is 10 minutes). For Pro and Classic warehouses, the recommended minimum auto-stop threshold is 10 to 15 minutes (default is 45 minutes).
• Auto-Resume: Automatically boots up an idle, stopped SQL Warehouse as soon as a user submits a query or opens a connected BI dashboard. Because Serverless warehouses resume in 2 to 6 seconds, end users experience virtually no delay when auto-resume triggers.
• Maintenance Channels: SQL Warehouses can be assigned to either the Current channel (the default, highly stable Databricks runtime release) or the Preview channel. The Preview channel allows analysts and administrators to test upcoming SQL functionality and performance optimizations two weeks prior to general release.

Monitoring & Access Controls

SQL Warehouses are governed by Unity Catalog and workspace access controls. Administrators assign permissions to users, groups, or service principals using three privilege levels:

• CAN USE: Allows users to connect to the warehouse and execute SQL queries.
• CAN MANAGE: Allows users to edit warehouse settings, scale sizes, modify auto-stop timers, and restart/stop the warehouse.
• IS OWNER: Full administrative ownership over the warehouse configuration and access permissions.

The Query History tab allows analysts and admins to monitor execution metrics, identify queued or long-running queries, inspect execution profiles, and diagnose query bottlenecks across all active warehouses.