Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 36 ยท Platform and Workspace

SQL Warehouses and Serverless Compute

Learn what a Databricks SQL warehouse is, how sizes and scaling work, how serverless differs from classic compute, and how the SQL editor, queries and dashboards fit in.

Not everyone who uses Databricks writes Python. Analysts live in SQL, dashboards and BI tools. For them Databricks offers Databricks SQL: a SQL editor, saved queries, dashboards, alerts and a compute type built for SQL called a SQL warehouse.

A warehouse is a managed compute resource that you point your queries at. It starts when a query arrives, runs it fast, and stops when idle. Compared with a general cluster, it is tuned for many short concurrent SQL queries and does not expose Spark configuration or libraries.

Warehouse types and sizes

Warehouses come in three flavours, and in most workspaces serverless is the recommended one:

Type Startup Notes
Serverless Seconds Compute lives in Databricks' account and scales fast
Pro Minutes Compute in your cloud account, more features than Classic
Classic Minutes Basic SQL features

Size uses T-shirt names: 2X-Small, X-Small, Small, Medium, Large and up. A bigger size gives more parallel power for each query. For many users at once, enable scaling with a minimum and maximum number of clusters; Databricks adds clusters when queries queue and removes them when idle. Set auto stop to a few minutes so an unused warehouse shuts down.

Free Edition gives you a small serverless warehouse to run queries; you cannot resize it much.

The SQL editor and saved assets

In the SQL editor you pick a warehouse, a default catalog and schema, and run statements:

SQL
USE CATALOG samples;
USE SCHEMA nyctaxi;

SELECT date_trunc('DAY', tpep_pickup_datetime) AS day,
       count(*)        AS trips,
       round(avg(fare_amount), 2) AS avg_fare
FROM trips
GROUP BY 1
ORDER BY 1
LIMIT 10;

This is read-only Databricks code; on a real workspace you would see a result table with a chart button. You can then:

  • Save the query and share it.
  • Parameterise it with named parameters like :min_fare, so users change a value without editing SQL.
  • Add a visualisation (bar, line, counter, map).
  • Place visualisations on a dashboard with filters, and schedule a refresh.
  • Create an alert that notifies you when a result crosses a threshold.

A parameterised query looks like this:

SQL
SELECT count(*) AS trips
FROM samples.nyctaxi.trips
WHERE fare_amount >= :min_fare;

Serverless in general

"Serverless" also exists beyond warehouses: serverless compute for notebooks, jobs and pipelines. What it changes:

  • No cluster to configure. You choose almost nothing; Databricks picks the size and scales it.
  • Fast start. Seconds instead of several minutes.
  • Usage-based billing. You pay for what runs, so small bursts are cheap.
  • Fewer knobs. No custom Spark configs, limited library and network options, no sc object, and no %sh.

Those limits are why some tutorials written for classic clusters do not work on Free Edition. When you hit one, look for the serverless-friendly alternative, often a built-in function or a supported library.

Stand-in: queueing and scaling

Why do warehouses scale clusters? Because concurrent queries queue up. The stand-in simulates queries that each take one "slot" and shows how many clusters are needed for different loads. It illustrates the principle, not Databricks' actual scheduler.

Python Stand-in: scale clusters with the queue

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second stand-in implements a mini query "editor": a parameterised query function over a list of rows, like the :min_fare parameter above. It uses plain Python only.

Python Stand-in: a parameterised query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Which compute should I use?

flowchart TD
    Q{"What are you doing?"} -->|Run SQL, dashboards, BI tools| WH["SQL warehouse (serverless)"]
    Q -->|Explore with Python and Spark| NB["Notebook on serverless or all-purpose cluster"]
    Q -->|Scheduled pipeline| JB["Job compute or serverless job"]
    Q -->|Heavy ML, custom libraries| CL["Classic cluster / GPU"]

Match the tool to the workload; the same table can be read by all of them.

BI tools such as Power BI and Tableau connect to a SQL warehouse using JDBC/ODBC, and applications can use drivers for Python, Node.js and Go. Credentials for these connections should be tokens or service principals, never your personal password.

Cost habits

  • Use the smallest size that gives acceptable speed; size mostly affects single-query time, scaling affects concurrency.
  • Keep auto stop short (for example 5 to 10 minutes).
  • Cache repeated dashboard queries rather than refreshing every minute.
  • Review the query history to find slow or expensive queries.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a SQL warehouse optimised for?

    SQL warehouses are managed compute tuned for SQL workloads and BI connections.
  2. 2. What does a bigger warehouse size mostly improve?

    Size gives more parallel power per query; scaling (extra clusters) handles more concurrent users.
  3. 3. Which is a limitation of serverless notebook compute?

    Serverless trades configurability for fast start and no management.
  4. 4. How do you let dashboard users change a filter value in a saved query?

    Parameters make a saved query reusable with different values.

Summary

  • Databricks SQL gives analysts a SQL editor, dashboards, alerts and SQL warehouses.
  • Warehouses scale in size (speed per query) and clusters (concurrency); serverless starts in seconds.
  • Serverless compute removes cluster management at the cost of some configurability.
  • Choose compute by workload and keep auto stop on.

Next: Files, Volumes and Git Folders