What is a Warehouse in Databricks SQL?

[gowri_databrick]

Hi everyone,

I’m learning Databricks SQL and came across SQL warehouses.

I understand that a SQL warehouse provides compute resources for running SQL queries, but I’m not clear about how it is different from other compute options in Databricks.

When should we use a SQL warehouse, and what are the main things to consider when choosing one?

Thanks!

[szymon_dybczak]

Hi  @gowri_databrick ,

A SQL warehouse is Databricks compute specifically optimized for SQL analytics workloads. It is commonly used behind Databricks SQL, dashboards, BI tools, ad-hoc SQL analysis, and SQL-based jobs.

The main difference from general Databricks compute is the workload it is designed for.
General-purpose or jobs compute is better when you need Spark notebooks, Python/Scala workloads, custom libraries, or more control over cluster configuration.
A SQL warehouse is aimed at users and applications that mainly need fast, scalable SQL query execution. .

A simple rule of thumb is:

Mostly SQL, dashboards, or BI -> SQL warehouse.
Python/Scala/Spark development or custom compute requirements -> general/serverless notebook or job compute.

If my answer was helpful, please consider marking it as accepted solution.

[ivanvyd]

@gowri_databrick you have the core idea right. A SQL warehouse is SQL-optimized compute that executes queries against your lakehouse data. Your tables remain in cloud storage, while admins use Unity Catalog to govern access to most assets.

Use a SQL warehouse for SQL analytics, AI/BI dashboards, Power BI or Tableau, SQL jobs, and SQL-focused notebook work. Choose notebook or job compute for data engineering, ML, custom libraries, or non-SQL Spark workloads.

Databricks recommends using serverless SQL warehouses when available.

Source: Databricks documentation

Choose Pro when you need custom networking or serverless is unavailable. Classic provides entry-level performance.

Choose the warehouse size based on each query's compute needs and the maximum cluster count based on concurrency. Configure auto-stop to control idle spend, then review queued queries, query history, and spill metrics when tuning.

Hope this helps! Please, let us know if you need more specific answers for your specific scenarios.

[balajij8]

@gowri_databrick 

SQL warehouse is a managed compute resource inside Databricks optimized exclusively for running SQL queries, powering AI/BI dashboards and handling SQL based analytics workloads. The main difference between a warehouse and an all purpose cluster is specialization - all purpose compute is designed for multi language notebook execution (Python, Scala, R, and SQL) whereas SQL warehouse focuses purely on SQL execution. It uses intelligent query compilation, automated query optimization, result caching and default Photon acceleration for high-performance query execution.

You can use warehouses for Databricks SQL queries, scheduled alerts and external BI integrations (Power BI / Tableau). You can use SQL warehouses for most SQL analytical use cases as it removes infrastructure management and provides quick startup times, dynamic auto-scaling based on incoming query concurrency.

You can route the dashboard queries, BI tool connections and ad hoc analytical SQL via a SQL warehouse. Use all purpose / serverless compute / job clusters for workflows that require custom library installations, multi-language notebooks, data engineering pipelines or machine learning tasks.

[data_pulse]

@gowri_databrick 
Good Question and it's useful to understand this early because databricks gives you different compute options for different ways of getting insights from data. I have elaborated bit more in the below.

The simplest way to think about it is: the compute options aren’t really competing with each other, they’re optimized for different use cases.

Compute	Best Fit	Life Cycle	concurrency/ scaling	Typical Users
SQL Warehouse	SQL analytics, BI tools, dashboards, SQL Editor, JDBC/ODBC clients	Long lived service, can auto start/ auto stop.	optimized for many concurrent sql queries.	Analysts, BI Tools
All-purpose / interactive compute	Interactive notebooks, development and exploration using Python/Scala/SQL	usually kept running while the users work	Shared interactive compute	Data Engineers, Data Scientists
Job Compute	Scheduled production pipelines and automated workloads	Starts for the Job and Terminates after.	Scales for job workload.	Production workflows
Serverless	Fast starting for SQL/ notebook/job workloads	Fully managed, quick warm up.	Databricks manages scaling/ infrastructure	Teams requiring low ops overhead.

So the thumb rule is:

BI tool / SQL analytics → SQL Warehouse
Interactive notebook development → interactive compute
Scheduled pipeline → job compute.
For quick Warm up and No maintenance Overhead → Serverless 

SQL warehouses are optimized for SQL query performance and BI-style concurrency, and Databricks generally recommends Serverless SQL Warehouses for most SQL workloads. The docs here talks about the compute recommendations. 

The three SQL warehouse types:

Serverless: Usually the default choice. Includes Photon, Predictive I/O and Intelligent Workload Management, starts quickly, and scales well for concurrent users.

Pro: Includes Photon and Predictive I/O. Useful when Serverless is unavailable or you need custom networking/hybrid connectivity.

Classic: Older option, with fewer performance and automation features than Serverless or Pro.

A few practical points to consider:

• Warehouse size helps when an individual query needs more compute.
• Max clusters helps when many users/queries are running concurrently.
• Auto stop is important for avoiding idle cost.
• Serverless is usually the easiest option for BI workloads because startup and scaling are much faster.
• Concurrency matters as BI tools/ dashboards generate multiple queries, so test with realistic load.
• Don't scale blindly as High execution time points to query tuning/size, high queue time points to concurrency/ max clusters.
• watch cost vs run time as large warehouses sometime finish faster and cost less overall than small one running longer. 

For Example in the below use case:
Power BI / Tableau / SQL Editor -> SQL Warehouse -> Gold tables

Multiple analysts querying dashboards during the day could use a Small Serverless warehouse with autoscaling and auto-stop, then tune further based on query latency and queueing (Can check this from Query History -> filter by warehouse -> check the duration/ total execution time, waiting/queue time/ rows read or bytes read or spill).

But if same Gold tables are re-built nightly using python/pyspark (Nightly ETL notebook -> Job compute -> Gold tables), then the workload belongs on Job compute, not the BI warehouse.

[hayoni]

Hello, @gowri_databrick 

Here is a quick example that makes it super clear.

Try creating a new notebook and changing the attached compute resource to Serverless SQL Warehouse.

You'll immediately get an error saying: 

"SQL Warehouse only support SQL and Markdown cells. Cells of other language types will fail during execution." 

In other words, it's an environment strictly configured to run only SQL! (and Markdown)

As others have mentioned in their replies, Databricks has different compute options depending on your purpose. It's easiest to think of it like this:

  "I'm mostly going to use SQL!" -> Use SQL Warehouse.

  "I want to work in notebooks using both Python and SQL!" -> Use All-purpose compute.

  "I need to run scheduled jobs!" -> Use Job compute.

While we're on the topic, let me also explain how to create one.

You just go to "Workspace -> Compute -> SQL warehouses tab and click the [Create SQL warehouse]"

(Of course, not just anyone can do this—you need to have cluster creation permissions!)

Once you click it, the settings screen pops up. Here are the main options:

(Image source: Databricks Documentation : https://docs.databricks.com/aws/en/compute/sql-warehouse/create )

(1) Name: Just name your SQL warehouse.

(2) Cluster size: You can choose sizes from 2X-Small to 4X-Large, just like t-shirt sizes. The larger the size, the faster it processes heavy and complex queries. If it's just for development, starting with 2X-Small or Small is usually more than enough. ++ You'll see the term "DBU" on the screen. Think of it as a billing coin or metric Databricks uses to measure how much compute resource you've consumed. You can get a rough feel for it by checking the Databricks pricing page.

https://www.databricks.com/product/pricing/product-pricing/instance-types

(3) Auto stop: A must-have setting that automatically turns off the warehouse (and stops the billing) if no one uses it for a specified time.

(4) Scaling: By setting Min-Max values, it will automatically scale out when the number of concurrent users increases.

(5) Type: There are 3 options—Serverless, Pro, and Classic.

• Serverless: Literally turns on super fast (in seconds). Databricks manages all the backend infrastructure, and it includes all the optimization features. It's the highly recommended "Best Practice" choice.
• Pro: The compute resources are created inside your own cloud account, so it takes a few minutes to start up. You might wonder, "Why use this then?" It's used when Serverless isn't supported in your region yet, or when your company's security policies require the compute to be spun up inside your own cloud network (VPC/VNet).
• Classic: You can think of this as an entry-level option that provides basic performance without the advanced optimization features.

+ Recently, they also introduced a new SQL warehouse type called "Lakehouse Real-Time," which is capable of sub-second latency. (Though this is still in Beta!)

+ If you're curious about the detailed optimization features I briefly skipped over (like Predictive IO or Intelligent Workload Management), please refer to the official docs below.

https://docs.databricks.com/aws/en/compute/sql-warehouse/warehouse-types

Hope this helps!