ExamFinch.com My Study

Study guide

4.2 SQL Warehouses Sizing & Management

Practice this exam

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

4.2 SQL Warehouses Sizing & Management

Key Takeaways Explain This Topic AI

Databricks SQL Warehouses are available in two primary compute types: Serverless (boots in 2–6 seconds, managed by Databricks) and Pro/Classic (boots in approximately 4 minutes, managed in customer VNet/VPC).

SQL Warehouse t-shirt sizes range from 2X-Small (1 cluster node) up to 4X-Large (128 cluster nodes), where each size step doubles the underlying cluster compute resources and DBU consumption rate.

Auto-stop default for Serverless SQL Warehouses is 10 minutes (configurable down to 1 minute), preventing idle cost accumulation compared to Pro/Classic warehouses which default to 45 minutes.

Multi-cluster auto-scaling scales out clusters from Min Clusters to Max Clusters (up to 30 clusters) based on query queue backlogs using Scaling Factors (Low Latency vs High Throughput).

Last updated: July 2026

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:

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).

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.

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.

  • Feature Serverless SQL Warehouse Pro SQL Warehouse Classic SQL Warehouse
  • Startup Latency 2 - 5 seconds (Instant) 2 - 5 minutes (Cold start) 2 - 5 minutes (Cold start)
  • Compute Location Databricks-managed cloud Customer cloud VPC / VNet Customer cloud VPC / VNet
  • Min Auto-Stop Limit 1 minute 10 minutes 10 minutes
  • Python UDF Support Yes Yes No
  • Predictive Optimization Yes Yes No
  • Primary Use Case Ad-hoc analytics, BI dashboards Advanced SQL, Python UDFs Legacy basic SQL querying
  • 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.

  • T-Shirt Size Cluster Compute Scale Relative Resource Capacity Recommended Usage
  • 2X-Small 1 Node 1x Baseline Small ad-hoc queries, developer testing
  • X-Small 2 Nodes 2x Baseline Light BI querying, small team exploration
  • Small 4 Nodes 4x Baseline Standard BI dashboards, scheduled reporting
  • Medium 8 Nodes 8x Baseline Medium-sized data marts, moderate concurrency
  • Large 16 Nodes 16x Baseline Large enterprise datasets, complex joins
  • X-Large to 4X-Large 32 to 128 Nodes 32x to 128x Baseline Massive enterprise data warehousing, high SLA
  • 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.