Databricks SQL Warehouses & Serverless Compute: Sizing, Photon & Cost Control

databricks sql warehouse is the compute you point a dashboard, a BI tool, or an ad-hoc SQL editor at — a pool of Photon-accelerated nodes that runs SQL against your lakehouse tables and nothing else. It is deliberately not the same thing as the general-purpose cluster you attach a notebook to. A warehouse has one job: answer SQL queries fast, share itself across many concurrent users, and get out of the way (and off the bill) the moment nobody is asking. Everything an interviewer will drill you on — types, t-shirt sizing, Photon, caching, scaling, and DBUs — is a consequence of that single-purpose design.

That focus is why sizing a warehouse feels different from sizing a Spark job. You are not tuning executors and shuffle partitions by hand; you are choosing a t-shirt size that controls how big each query can go, a cluster count that controls how many users can run at once, and an auto-stop that controls how much idle time you pay for. Get those three dials right and the same hardware serves a Monday-morning dashboard storm and a quiet Sunday for wildly different money. This guide walks the four ideas the interview actually probes — the Classic / Pro / Serverless types and t-shirt sizing, the Photon engine and its cache layers, concurrency with multi-cluster scaling, and DBU-based cost control with materialized views — and pairs each with a Solution-Tail answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the Spark SQL practice library →, rehearse the tuning decisions on the query-optimization practice set →, and size your compute on the capacity-planning practice set →.

---

On this page

• Why SQL Warehouses are their own compute class
• Warehouse types & t-shirt sizing
• Photon & the two cache layers
• Concurrency, queuing & multi-cluster scaling
• Cost control — DBUs, auto-stop & materialized views
• Cheat sheet — Databricks SQL recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why SQL Warehouses are their own compute class

A SQL Warehouse is Photon-first SQL compute, not an all-purpose cluster — that one fact decides its cost and speed

The one-sentence invariant: a SQL Warehouse is a managed, Photon-accelerated pool that only runs SQL, so it is sized, scaled, and billed for concurrent query serving rather than arbitrary code. Everything that makes a warehouse cheaper and faster for BI than an all-purpose cluster follows from that narrowing. You cannot attach a Python notebook to a warehouse or run an arbitrary Scala job on it; in exchange, it starts faster, shares itself across dozens of analysts, scales out on demand, and shuts down when idle without you writing a scheduler.

The two compute shapes — and why you keep them apart.

• All-purpose (interactive) clusters run notebooks, arbitrary Spark/Python/Scala, and mixed workloads. They are flexible but pricey to leave running and are not tuned for many small concurrent SQL queries.
• SQL Warehouses run only SQL, always with Photon, and are optimized for concurrency and fast startup. They expose a JDBC/ODBC endpoint that BI tools (dashboards, notebooks in SQL mode, external BI) connect to.
• Job clusters are the third shape — ephemeral compute created for one scheduled job and torn down after. Warehouses are for serving; job clusters are for batch.

The three warehouse types in one breath.

• Classic — runs in your cloud account (the classic compute plane). Lowest Databricks DBU rate, but you also pay your cloud provider for the VMs, and cold starts take minutes.
• Pro — also in your account, but adds the newer features: materialized views, streaming, Predictive I/O, and the latest Photon. The middle option for teams that need those features but not serverless.
• Serverless — runs in Databricks' account, starts in seconds, and is fully managed. Highest DBU rate, but no separate cloud-VM bill and near-instant start/stop, which usually makes it cheaper end-to-end for spiky BI.

What interviewers listen for.

• Do you say "a warehouse only runs SQL and is always Photon" before anything else? — senior signal.
• Do you separate "size scales one query, cluster count scales concurrency"? — the sizing mental model most candidates miss.
• Do you reach for Serverless because it starts in seconds and auto-stops aggressively, not just because it is newest? — cost-awareness signal.
• Do you talk about DBUs, auto-stop, and caching as the cost levers, rather than treating the bill as fixed? — the whole point of this post.

Worked example — estimate what a warehouse burns per hour

Detailed explanation. Before you size anything you need the unit economics in your head. A warehouse's Databricks cost is its size in DBUs per hour multiplied by its runtime multiplied by a per-DBU dollar rate; Serverless folds the infrastructure into that one rate, while Classic adds a separate cloud-VM bill. The single most common cost mistake is leaving a warehouse running idle, because you pay the full DBU/hr whether or not a query is in flight.

Question. A Small Serverless warehouse bills 12 DBU/hr at an illustrative $0.70/DBU. What does one hour of active use cost, and what does an 8-hour workday cost if it never auto-stops but only serves queries for 90 minutes?

Input.

item	value
size	Small
size rate	12 DBU/hr
$/DBU (illustrative)	0.70
working window	8 hours
actual query time	1.5 hours

Code.

-- Illustrative cost math expressed as SQL; real numbers come from
-- system.billing.usage (see the cheat sheet).
SELECT
  12            AS size_dbu_per_hr,       -- Small warehouse
  0.70          AS usd_per_dbu,           -- illustrative list rate
  1.5           AS active_hours,          -- queries actually ran
  8.0           AS wall_clock_hours,      -- warehouse left "on"
  12 * 0.70 * 1.5 AS cost_if_billed_active,
  12 * 0.70 * 8.0 AS cost_if_left_running;

Step-by-step explanation. The size fixes the DBU/hr (12 for Small). Multiply by the dollar rate to get $8.40 per running hour. If the warehouse only truly worked 1.5 hours, the value delivered cost about $12.60. But a warehouse bills for wall-clock time it is on, not just querying — so leaving it up for the whole 8-hour window at Small burns $67.20 for the same work. The gap between those two numbers is exactly what auto-stop reclaims.

Output.

scenario	hours billed	cost (illustrative)
billed only while querying	1.5	$12.60
left running all day	8.0	$67.20
waste from no auto-stop	6.5	$54.60

Rule of thumb. A warehouse costs money whenever it is on, not whenever it is busy — so the first cost lever, before sizing, is a short auto-stop.

---

2. Warehouse types & t-shirt sizing

Classic, Pro, Serverless — and why size scales one query while cluster count scales concurrency

Sizing a warehouse is two independent decisions people constantly conflate. The t-shirt size (2X-Small through 4X-Large) sets how much horsepower a single query gets — bigger size, bigger cluster, faster scans and joins. The cluster count (min/max clusters) sets how many queries can run at once before new ones queue. Turning the size dial to fix a concurrency problem, or adding clusters to fix a slow single query, is the classic wrong move — and interviewers ask exactly that.

The three types compared.

• Classic — compute in your cloud account; lowest DBU rate but a separate VM bill and multi-minute cold starts. Fine for a steady internal warehouse you keep warm.
• Pro — your account, richer feature set (materialized views, streaming, Predictive I/O, newest Photon). Choose it when you need those features without leaving your compute plane.
• Serverless — Databricks-managed, starts in seconds, scales clusters fastest, auto-stops in as little as a minute. Highest DBU rate but no VM bill; usually the cheapest for bursty BI because it is almost never idle-on.

T-shirt sizing — each step roughly doubles the cluster.

• Sizes run 2X-Small → X-Small → Small → Medium → Large → X-Large → 2X-Large → 3X-Large → 4X-Large.
• Each step up roughly doubles the worker count and the DBU/hr, so it roughly halves the runtime of a large, well-partitioned query — until the query is too small to use the extra parallelism.
• Size helps big queries: large scans, wide joins, heavy aggregations. It does not help a 50 ms point lookup, and it does not add concurrency.

Cluster count — the other axis.

• A warehouse can run min → max clusters. Extra clusters are identical copies that share the incoming query load.
• Add clusters when queries queue (many users, each query fine on its own).
• Increase size when a single query is slow even with no queue.

Auto-stop — the idle switch.

• A warehouse stops after N minutes idle and restarts on the next query. Serverless restarts in seconds, so you can set auto-stop to 1–5 minutes; Classic/Pro cold-start slower, so people leave a longer idle window and pay more.

Worked example — read the DBU/hr ladder and pick a size

Detailed explanation. Because each size step doubles the DBU/hr, the size ladder is a geometric series, and picking a size is really picking a point on a speed-versus-cost curve. Doubling the size roughly halves a big query's runtime, so the cost of that one query stays about flat while its latency drops — right up to the point where the query is too small to parallelize further, after which you pay double for nothing.

Question. A 200 GB scan-and-aggregate query takes 240 s on a Small (12 DBU/hr) warehouse. Estimate runtime and per-query DBU cost on Small, Medium, and Large, assuming it still parallelizes well.

Input.

size	DBU/hr	relative power
Small	12	1x
Medium	24	2x
Large	40	~3.3x

Code.

-- Model runtime as inversely proportional to power, DBU cost as
-- DBU/hr * runtime_hours. Illustrative, not a benchmark.
SELECT size, dbu_per_hr, runtime_s,
       ROUND(dbu_per_hr * runtime_s / 3600.0, 3) AS dbu_for_query
FROM VALUES
  ('Small',  12, 240),
  ('Medium', 24, 120),
  ('Large',  40,  73)
AS t(size, dbu_per_hr, runtime_s);

Step-by-step explanation. On Small the query runs 240 s and consumes 12 * 240/3600 = 0.80 DBU. Medium doubles power, so runtime falls to ~120 s but DBU/hr doubles to 24 — DBU per query stays ~0.80. Large is ~3.3x Small, so ~73 s at 40 DBU/hr is ~0.81 DBU. The cost per query barely moves while latency drops from 240 s to 73 s — this is why you size up for slow big queries: you buy speed at roughly constant DBU, as long as the query keeps parallelizing.

Output.

size	runtime (s)	DBU for the query
Small	240	0.80
Medium	120	0.80
Large	73	0.81

Rule of thumb. Size up when one big query is slow — you trade near-constant DBUs for lower latency. Stop sizing up when doubling the size stops halving the runtime; that is the point where the query no longer fills the cluster.

Databricks SQL interview question on sizing

Question. Analysts say a 500 GB nightly aggregation query is slow, but the warehouse monitoring shows zero queued queries and only one query running at a time. Do you increase the t-shirt size or add clusters — and why?

Solution Using t-shirt size, not cluster count

Code.

-- The symptom (slow single query, no queue) points to per-query
-- horsepower, i.e. SIZE. Cluster count only helps a QUEUE.
-- Resize the warehouse up one or two steps and re-measure.

-- Before: Small (12 DBU/hr), 1 cluster, no queue, query = 500s
-- Change: size = Large (40 DBU/hr), keep min/max clusters = 1

-- Verify with the query profile after resizing:
SELECT statement_id, total_duration_ms, read_bytes, spilled_local_bytes
FROM system.query.history
WHERE statement_text ILIKE '%big_aggregation%'
ORDER BY start_time DESC
LIMIT 5;

Step-by-step trace.

observation	meaning	correct dial
single query slow	needs more parallelism per query	size up
queued queries = 0	concurrency is not the bottleneck	not cluster count
spill bytes > 0	cluster too small for the shuffle	size up
CPU pinned, no spill	already parallel; bigger may not help	re-measure

1. Diagnose the symptom, not the fear. One slow query with an empty queue is a per-query problem, and per-query power is the t-shirt size.
2. Cluster count would do nothing here — extra clusters only absorb additional concurrent queries; a lone query runs on exactly one cluster no matter how many exist.
3. Check spill. spilled_local_bytes > 0 means the aggregation overflowed memory; a larger size adds memory and cores, so the spill shrinks and the query speeds up more than linearly.
4. Re-measure after each step. Size up until doubling the size stops meaningfully lowering total_duration_ms — beyond that you pay double for idle cores.

Output:

decision	value
dial changed	t-shirt size (Small → Large)
clusters	unchanged (min=max=1)
expected result	runtime falls, spill drops, DBU/query ~flat

Why this works — concept by concept:

• Size vs concurrency — size is horsepower for one query; cluster count is lanes for many queries. Matching the dial to the symptom is the entire sizing skill.
• Spill as a signal — local spill means the working set exceeds memory; a larger size adds memory, so speedup can be super-linear when spill disappears.
• Empty queue — with no queued queries, adding clusters cannot help; they would sit idle and cost DBUs.
• Measure, don't guess — the query profile (system.query.history) tells you whether the next size step still pays; sizing is iterative, not one-shot.
• Cost — sizing up is roughly DBU-neutral per query (double rate, half time) until parallelism saturates, so the correct size is the smallest one that still halves runtime.

Spark SQL
Topic — spark-sql
Spark SQL aggregation and join problems

Practice →

Capacity Topic — capacity-planning Warehouse sizing and capacity-planning problems

Practice →

---

3. Photon & the two cache layers

Photon vectorized execution plus result and disk cache — three layers that cut both latency and DBUs

The reason a SQL Warehouse is fast is not one feature but three stacked ones: Photon executes the query on a vectorized C++ engine, the result cache returns an identical query for free, and the disk cache keeps recently read Parquet on local SSD so repeat scans skip cloud storage. Every one of them lowers latency and DBUs, and interviewers love the layering question because most candidates only know Photon exists.

Photon — the vectorized engine.

• Photon is a C++ query engine that replaces parts of the JVM-based Spark SQL execution with columnar, SIMD-vectorized operators. It is always on for SQL Warehouses.
• It accelerates the heavy relational work: scans, filters, joins, aggregations, and writes. Large analytical queries see the biggest wins.
• It has limits: Python/Scala UDFs and unsupported operators fall back to the non-Photon path, so a query wrapped around a Python UDF may not benefit. Push logic into native SQL to stay on Photon.

Result cache — identical query, zero compute.

• If the exact same query text runs against unchanged data, the warehouse returns the stored result without running the query — no DBUs for the compute.
• There are two levels: a local cache in the warehouse's memory, and on Serverless a remote/global result cache that survives restarts and is shared more broadly.
• The cache is invalidated when the underlying tables change, when the query text differs by even a byte, or when the query uses non-deterministic functions like now() / random().

Disk cache — SSD-resident Parquet.

• The disk cache (formerly "Delta cache") keeps recently read Parquet/Delta files on the workers' local SSDs, so a repeat scan of the same data reads from SSD instead of cloud object storage.
• It is automatic on SQL Warehouses and accelerates repeat scans even when the query text changes (unlike the result cache, which needs identical text).
• It warms as queries run and is lost when the warehouse stops — a reason to weigh a longer auto-stop against cold-cache latency for latency-critical dashboards.

Worked example — the same query twice, one hits the result cache

Detailed explanation. The cleanest way to see the result cache is to run an identical aggregation twice with no data change in between. The first run executes on Photon and consumes DBUs; the second returns from cache in milliseconds with zero query compute. Change one character — or let the table change — and the second run executes again.

Question. Run the same SELECT twice against an unchanged sales table and show what the warehouse does on each run.

Input.

run	query text	table changed since?
1	SELECT region, SUM(amount) ... GROUP BY region	(first run)
2	identical text	no

Code.

-- Run 1: executes on Photon, populates the result cache.
SELECT region, SUM(amount) AS revenue
FROM sales
WHERE order_date >= '2026-01-01'
GROUP BY region;

-- Run 2: identical text, sales unchanged -> served from result cache,
-- ~milliseconds, 0 DBUs for compute.
SELECT region, SUM(amount) AS revenue
FROM sales
WHERE order_date >= '2026-01-01'
GROUP BY region;

Step-by-step explanation. Run 1 has no cached result, so Photon scans sales (accelerated further by the disk cache if the files are warm), computes the aggregation, returns the rows, and stores the result keyed by the normalized query text plus the table version. Run 2 arrives with byte-identical text and the same table version, so the warehouse skips execution entirely and returns the stored rows. If a single row had been inserted into sales, the table version would change and Run 2 would re-execute.

Output.

run	path taken	compute DBUs	latency
1	Photon execution	> 0	full query time
2	result cache hit	0	~milliseconds

Rule of thumb. Identical-text + unchanged-data means free. Parameterize dashboards so the SQL text is stable, and avoid now()/random() in cached queries or you defeat the result cache on every run.

Databricks SQL interview question on caching

Question. A dashboard tile reruns the same aggregation every 30 seconds during business hours, yet you still see steady DBU burn on that warehouse. Explain the cache layers involved and how you would guarantee near-zero compute for that tile.

Solution Using stable query text and the result cache

Code.

-- 1) Make the query text byte-stable: no now(), no random(), fixed
--    literals or bound parameters, so the result cache key is constant.
SELECT region, SUM(amount) AS revenue
FROM sales
WHERE order_date BETWEEN :start_date AND :end_date   -- bound params, stable text
GROUP BY region;

-- 2) Confirm caching is enabled for the session (default is on):
SET use_cached_result = true;

-- 3) If the table updates hourly, the cache is only invalidated hourly,
--    so 30s reruns between updates are free. Inspect history to prove it:
SELECT statement_id, from_result_cache, total_duration_ms
FROM system.query.history
WHERE statement_text ILIKE '%SUM(amount)%'
ORDER BY start_time DESC LIMIT 10;

Step-by-step trace.

rerun	table changed since last load?	cache result	compute DBUs
t=0s	first load	miss → execute	> 0
t=30s	no	hit	0
t=60s	no	hit	0
after hourly load	yes	miss → execute	> 0

1. Stabilize the text. A tile that injects now() or a changing timestamp produces a new cache key every run, so it always executes; bound parameters or fixed literals keep the key constant.
2. The first run per data-version executes on Photon and populates the result cache; every identical rerun until the next table change is a cache hit at zero compute.
3. from_result_cache = true in system.query.history is the proof — if it is false on reruns, the text or data is changing and you hunt down why.
4. Match the refresh to the data. If sales only loads hourly, only ~1 of the 120 reruns per hour actually computes; the other 119 are free, collapsing the burn.

Output:

period	reruns	executed	served from cache
one hour (30s cadence)	120	1	119

Why this works — concept by concept:

• Photon — the one execution that does run is vectorized C++, so even the cache-miss path is as cheap as the engine allows.
• Result cache — identical text against an unchanged table version returns stored rows at zero compute; stability of the query key is the whole trick.
• Disk cache — even when a rerun does execute (after a data change), warm SSD-resident Parquet keeps the scan off cloud storage, shrinking that one run too.
• Invalidation model — the cache tracks table version, so correctness is automatic: you never serve stale results, you just serve free ones between changes.
• Cost — burn drops from O(reruns) to O(data changes); a 30s tile over hourly data costs ~1/120th of the naive execution.

Optimization
Topic — optimization
Query-optimization and caching problems

Practice →

Spark SQL Topic — spark-sql Photon-friendly Spark SQL rewrite problems

Practice →

---

4. Concurrency, queuing & multi-cluster scaling

One cluster runs a fixed set of queries; multi-cluster load balancing is how a warehouse survives a Monday-morning dashboard storm

A single warehouse cluster runs only so many queries at once (on the order of ten, depending on size and query cost); everything past that queues. The fix for a queue is not a bigger size — it is more clusters. Multi-cluster load balancing lets a warehouse spin up additional identical clusters when queries pile up and spin them down when the rush passes, which is exactly the shape of BI traffic: quiet, then a 9 a.m. spike, then quiet again.

Per-cluster concurrency and the queue.

• Each cluster admits a bounded number of concurrently running queries; additional queries wait in a FIFO-ish queue until a slot frees or another cluster picks them up.
• Queue depth, not query runtime, is the signal that you are concurrency-bound rather than size-bound.

Multi-cluster load balancing.

• Set min clusters and max clusters. The warehouse starts at min, adds clusters when the queue grows, and removes them (down to min) when demand falls.
• New queries are load-balanced across the running clusters; each cluster is an identical copy of the chosen t-shirt size.
• Scale-up is driven by queuing; scale-down by sustained idle capacity — so you size the range, and the autoscaler picks the point.

Workload isolation.

• Put different workloads on different warehouses: a Serverless BI warehouse that scales out for dashboards, a separate warehouse for heavy ad-hoc analysts, and job clusters for ETL. One noisy SELECT * cannot then starve the executive dashboard.
• Isolation also makes cost attribution clean — each warehouse maps to a team or workload in system.billing.usage.

Why Serverless wins for spiky traffic.

• Serverless clusters start in seconds, so scale-up keeps pace with a sudden spike and scale-down (plus a short auto-stop) reclaims cost the moment the spike ends. Classic/Pro cold-start slower, so they either lag the spike or stay warm and cost more.

Worked example — model the queue with min=1, max=3 clusters

Detailed explanation. The autoscaler reacts to queue depth. If each cluster runs ~10 queries at once and 25 arrive simultaneously, one cluster leaves 15 queued; the warehouse adds clusters until the queue drains or it hits max. Watching the arithmetic makes the size-versus-count distinction concrete.

Question. A Medium warehouse runs ~10 concurrent queries per cluster, configured min=1 / max=3. Twenty-five identical dashboard queries arrive at once. How many clusters spin up, and how many queries queue at the peak?

Input.

parameter	value
per-cluster concurrency	10
min clusters	1
max clusters	3
simultaneous queries	25

Code.

-- Model capacity vs demand as the autoscaler adds clusters.
SELECT clusters,
       clusters * 10           AS capacity,
       GREATEST(25 - clusters * 10, 0) AS still_queued
FROM VALUES (1),(2),(3) AS t(clusters);

Step-by-step explanation. With 1 cluster, capacity is 10 and 15 queries queue — the autoscaler sees the queue and adds a cluster. With 2 clusters, capacity is 20 and 5 still queue, so it adds the third. With 3 clusters (the max), capacity is 30, which absorbs all 25 with headroom, so the queue drains to 0. If demand exceeded 30, the extra would queue because max caps scale-out; that is your signal to raise max or the size.

Output.

clusters	capacity	queued
1	10	15
2	20	5
3	30	0

Rule of thumb. Set max clusters from your peak concurrency ÷ per-cluster capacity, and let min be low (often 1) so quiet hours are cheap. Size the cluster for query speed; count the clusters for the crowd.

Databricks SQL interview question on concurrency

Question. A BI workload idles most of the day but spikes to ~60 concurrent dashboard queries at 9 a.m., each individually fast. Design the warehouse configuration — type, size, cluster range, and auto-stop — and justify each choice.

Solution Using a Serverless warehouse with multi-cluster scaling

Code.

// Serverless SQL warehouse tuned for a spiky, high-concurrency BI load.
// POST /api/2.0/sql/warehouses
{
  "name": "bi-dashboards",
  "warehouse_type": "PRO",            // Serverless is enabled at the workspace level
  "enable_serverless_compute": true,
  "cluster_size": "Small",            // each query is fast; small is enough
  "min_num_clusters": 1,              // cheap when idle
  "max_num_clusters": 6,              // ~60 queries / ~10 per cluster
  "auto_stop_mins": 5,                // reclaim cost fast after the spike
  "spot_instance_policy": "COST_OPTIMIZED"
}

Step-by-step trace.

time	concurrent queries	clusters needed	state
03:00	0	0 (auto-stopped)	stopped, $0
09:00	60	6	scaled to max
09:20	8	1	scaled in
09:40	0	0	auto-stopped

1. Type = Serverless because the load is spiky: seconds-fast start means the 9 a.m. wall of queries does not wait on a cold VM, and aggressive auto-stop means the idle night costs nothing.
2. Size = Small because each query is individually fast — the bottleneck is the crowd, not any one query, so horsepower per query is not the lever.
3. Clusters = 1..6 because ~60 concurrent ÷ ~10 per cluster ≈ 6; min=1 keeps the first-query latency low without paying for six clusters overnight.
4. auto_stop = 5 min because Serverless restarts fast, so a short idle window reclaims cost within minutes of the spike ending without punishing the next user with a long cold start.

Output:

dial	choice	reason
type	Serverless	seconds-fast scale + aggressive auto-stop
size	Small	queries are individually fast
clusters	1..6	absorb ~60 concurrent
auto-stop	5 min	reclaim idle cost quickly

Why this works — concept by concept:

• Concurrency vs size — 60 fast queries is a concurrency problem, solved by cluster count, so the size stays small and cheap.
• Serverless elasticity — seconds-fast start lets scale-out track the spike and scale-in plus auto-stop reclaim cost the instant it ends.
• Cluster range — min low for cheap idle, max sized from peak concurrency ÷ per-cluster capacity, so you pay for the crowd only while it exists.
• Workload isolation — a dedicated bi-dashboards warehouse keeps ETL and ad-hoc queries from stealing dashboard slots at 9 a.m.
• Cost — spend is O(peak concurrency × spike duration), not O(24h × max clusters); the warehouse is off or minimal for the ~23 quiet hours.

Concurrency
Topic — concurrency
Concurrency and queue-scaling problems

Practice →

Capacity Topic — capacity-planning Peak-load capacity-planning problems

Practice →

---

5. Cost control — DBUs, auto-stop & materialized views

DBUs × size × time is the whole bill — auto-stop, right-sizing, and materialized views are the three levers

Every dollar a warehouse costs reduces to one product: the DBU/hr of its size, times how long it runs, times a per-DBU rate (with Classic adding a separate VM bill). That means there are exactly three levers — shrink the size (right-size), shrink the runtime (auto-stop), or eliminate the query entirely (materialize it). Interviewers frame cost as "cut the bill without hurting p95 latency," and the good answer names all three.

The DBU model.

• Cost = $/DBU × size DBU/hr × runtime hours. Serverless has a higher $/DBU but no separate VM bill and near-zero idle-on time; Classic has a lower $/DBU plus cloud-VM cost and slower start/stop.
• Because the bill scales with runtime the warehouse is on, idle-on time is pure waste — the number one thing to kill.

Auto-stop and right-sizing.

• Auto-stop ends idle runtime; on Serverless set it aggressively (1–5 min) because restarts are seconds. This is usually the biggest single saving.
• Right-sizing means dropping to the smallest size that still meets latency. An over-sized warehouse pays double DBU/hr for cores a small query cannot use.

Materialized views — precompute the repeated aggregation.

• A materialized view stores the result of a query and incrementally refreshes it as base tables change (backed by the Delta Live Tables engine, on Pro/Serverless). Dashboards then read the small precomputed table instead of re-aggregating raw data every load.
• They shift cost from many expensive reads to one incremental refresh, which is the winning trade when a heavy aggregation is read far more often than the data changes.

Monitoring — attribute the cost.

• system.billing.usage attributes DBUs to each warehouse over time, so you can see which workload actually spends.
• system.query.history shows per-query duration, bytes read, spill, and whether a run was a result-cache hit — the data you need to right-size and to prove a materialized view paid off.

Worked example — always-on versus auto-stop over a month

Detailed explanation. The single most common overspend is a warehouse left running around the clock for a workload that only queries during business hours. Putting the two monthly numbers side by side makes the case for auto-stop unarguable.

Question. A Medium Serverless warehouse (24 DBU/hr, illustrative $0.70/DBU) serves an 8-hour business day but is actively querying only ~3 hours of it. Compare monthly cost (22 working days) if it runs 24/7 versus if auto-stop limits it to the ~3 active hours per day.

Input.

item	value
size rate	24 DBU/hr
$/DBU	0.70
active hours/day	3
always-on hours/day	24
working days	22

Code.

SELECT scenario, hours_per_day,
       ROUND(24 * 0.70 * hours_per_day * 22, 2) AS monthly_cost
FROM VALUES
  ('always_on_24x7', 24),
  ('auto_stop_active_only', 3)
AS t(scenario, hours_per_day);

Step-by-step explanation. Always-on bills 24 h × 22 days = 528 warehouse-hours at 24 DBU/hr and $0.70, i.e. 24 * 0.70 * 528 = $8,870. With auto-stop trimming to ~3 active hours/day, it bills 66 warehouse-hours: 24 * 0.70 * 66 = $1,108. Same queries, same latency for users, roughly 1/8th the bill — because you stopped paying for the 21 idle hours a day.

Output.

scenario	warehouse-hours/month	monthly cost
always-on 24/7	528	$8,870
auto-stop, active only	66	$1,108
saving	462	$7,762

Rule of thumb. Before you argue about size, kill idle-on time — auto-stop is almost always the largest, safest saving and it does not touch query latency.

Databricks SQL interview question on cutting cost

Question. Finance says the BI warehouse costs about $9,000/month. Give three concrete levers to roughly halve it without hurting p95 dashboard latency, and explain why each is safe.

Solution Using auto-stop, right-sizing, and materialized views

Code.

-- Lever 1: shorten auto-stop (kills idle-on runtime, no latency hit).
--   PATCH /api/2.0/sql/warehouses/{id}  -> { "auto_stop_mins": 5 }

-- Lever 2: right-size down one step and verify p95 holds.
SELECT PERCENTILE(total_duration_ms, 0.95) AS p95_ms
FROM system.query.history
WHERE compute.warehouse_id = :wh_id AND start_time > current_timestamp() - INTERVAL 7 DAYS;

-- Lever 3: materialize the heaviest repeated aggregation so dashboards
-- read a tiny precomputed table instead of scanning raw sales each load.
CREATE MATERIALIZED VIEW daily_region_revenue AS
SELECT order_date, region, SUM(amount) AS revenue, COUNT(*) AS orders
FROM sales
GROUP BY order_date, region;

Step-by-step trace.

lever	what it shrinks in DBU×size×time	latency effect	est. saving
auto-stop 30→5 min	idle runtime	none (cache re-warms fast)	large
right-size L→M	size DBU/hr	none if p95 holds	~half the rate
materialized view	runtime of heavy reads	faster (small read)	high on hot tiles

1. Auto-stop first because idle-on time is pure waste; cutting a 30-minute idle window to 5 removes runtime you were billed for while nobody queried, with zero effect on active-query latency.
2. Right-size down one step and confirm p95_ms is unchanged — if the workload never used the extra cores, you halve the DBU/hr for free; if p95 regresses, you step back up.
3. Materialize the hot aggregation so dashboards read a small precomputed table; the heavy scan runs once per incremental refresh instead of on every dashboard load, which also lowers latency.
4. Verify with billing. Compare system.billing.usage for the warehouse before and after; the three levers stack, and together they routinely halve a bill without users noticing.

Output:

lever	dial	safe because
auto-stop → 5 min	runtime	doesn't touch active queries
right-size → Medium	size	p95 verified unchanged
materialized view	runtime	reads get smaller and faster

Why this works — concept by concept:

• DBU decomposition — since cost = rate × size × time, every saving must shrink one of those factors; naming which factor each lever pulls is the senior framing.
• Auto-stop — attacks the time factor with no latency cost, so it is the first and safest cut.
• Right-sizing — attacks the size factor; safe only when the query profile proves the extra cores were idle, which p95 confirms.
• Materialized views — attack the time factor of repeated heavy reads and improve latency, trading many big scans for one incremental refresh.
• Cost — the levers are multiplicative: halving idle time and halving size and offloading hot reads compounds well past a single 2× saving.

Optimization
Topic — optimization
Cost and query-optimization problems

Practice →

Warehouse Topic — data-warehouse Data-warehouse modelling and aggregation problems

Practice →

---

Cheat sheet — Databricks SQL recipes

Create a Serverless SQL warehouse (REST API).

// POST /api/2.0/sql/warehouses
{
  "name": "analytics",
  "cluster_size": "Small",
  "enable_serverless_compute": true,
  "min_num_clusters": 1,
  "max_num_clusters": 4,
  "auto_stop_mins": 5
}

Set (or shorten) auto-stop.

// PATCH /api/2.0/sql/warehouses/{id}
{ "auto_stop_mins": 5 }

Force or disable the result cache for a session.

SET use_cached_result = true;   -- default; identical query + unchanged data = free
SET use_cached_result = false;  -- benchmark true execution, bypass the cache

Create an incrementally refreshed materialized view.

CREATE MATERIALIZED VIEW daily_region_revenue AS
SELECT order_date, region, SUM(amount) AS revenue
FROM sales
GROUP BY order_date, region;

Attribute DBU cost from system tables.

SELECT usage_metadata.warehouse_id,
       date_trunc('day', usage_date) AS day,
       SUM(usage_quantity)           AS dbus
FROM system.billing.usage
WHERE billing_origin_product = 'SQL'
GROUP BY 1, 2
ORDER BY day DESC;

Inspect slow queries and cache hits.

SELECT statement_id, total_duration_ms, read_bytes,
       from_result_cache, spilled_local_bytes
FROM system.query.history
ORDER BY total_duration_ms DESC
LIMIT 20;

Size / dial picker.

Situation	Dial to change
One big query slow, no queue	Increase t-shirt size
Many queries queuing, each fast	Increase max clusters
Warehouse cheap but idle-on	Shorten auto-stop
Same aggregation read all day	Add a materialized view
Spiky BI, quiet nights	Use Serverless + low min clusters

---

Frequently asked questions

What is a Databricks SQL Warehouse?

A SQL Warehouse is managed compute that runs only SQL against your lakehouse tables, always accelerated by the Photon engine and exposed through a JDBC/ODBC endpoint that dashboards and BI tools connect to. Unlike an all-purpose cluster, you cannot attach a notebook or run arbitrary code on it; in return it starts faster, serves many concurrent users, scales out with multiple clusters, and auto-stops when idle. You choose a type (Classic, Pro, or Serverless), a t-shirt size, and a cluster range, and Databricks handles the rest.

Classic vs Pro vs Serverless — which should I use?

Classic runs in your own cloud account at the lowest DBU rate but with a separate VM bill and multi-minute cold starts. Pro also runs in your account and adds features like materialized views, streaming, and the newest Photon. Serverless runs in Databricks' account, starts in seconds, scales fastest, and auto-stops aggressively — its DBU rate is higher but there is no separate VM bill, so it is usually cheapest for spiky BI. Reach for Serverless for bursty dashboards, Pro when you need its features inside your compute plane, and Classic for steady always-warm workloads where the lower rate wins.

What do the t-shirt sizes actually change?

The size (2X-Small through 4X-Large) sets the horsepower of a single query — each step up roughly doubles the worker count and DBU/hr, which roughly halves the runtime of a large, parallelizable query. Size does not add concurrency: it will not clear a queue of many small queries. To serve more queries at once you add clusters (raise max clusters), not size. In short, size scales one query's speed; cluster count scales how many queries run simultaneously.

What is Photon and do I pay extra for it?

Photon is Databricks' vectorized, columnar C++ query engine that accelerates scans, filters, joins, aggregations, and writes; it is always on for SQL Warehouses, so there is no separate switch. Its cost is already reflected in the warehouse's DBU/hr — you do not buy it as an add-on. It speeds native SQL the most and falls back to the standard engine for Python/Scala UDFs and unsupported operators, so keeping logic in native SQL is how you stay on the fast path.

How does Databricks SQL result caching work?

If an identical query (byte-for-byte text) runs against unchanged table data, the warehouse returns the stored result without executing — zero query compute and millisecond latency. There is a local per-warehouse cache and, on Serverless, a remote/global result cache that survives restarts. The cache is invalidated when the underlying tables change, when the query text differs at all, or when the query uses non-deterministic functions like now() or random(). A separate disk cache keeps recently read Parquet on worker SSDs to speed repeat scans even when the query text changes.

How do I control Databricks SQL costs?

Cost is DBU rate × size DBU/hr × runtime, so there are three levers: shorten auto-stop to kill idle-on time, right-size down to the smallest size that still meets p95 latency, and add materialized views to precompute repeated heavy aggregations so dashboards read a small table instead of rescanning raw data. Result and disk caching cut compute further, and system.billing.usage plus system.query.history let you attribute spend and verify each change. Auto-stop is usually the largest, safest single saving because it never touches active-query latency.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every Databricks SQL idea above, from t-shirt sizing and multi-cluster scaling to the Photon-friendly rewrite and the DBU cost model, maps to a hands-on practice room where you tune real queries against graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "size up or add clusters?" holds up under a senior interviewer's depth probes.

Practice Spark SQL problems now →
Query-optimization drills →