Oversized Databricks SQL Warehouses

The warehouse bills for that size for every minute it runs, whether queries use the capacity or not. When the size is larger than the workload needs, every running hour - including idle time before Auto Stop - costs a multiple of what a smaller warehouse would.

Oversizing is common because the default cluster size when creating a warehouse is X-Large, and Databricks' sizing guidance says it is usually more efficient to start with a larger warehouse and size down if necessary, so the size-down step is easy to skip. Size is also raised to fix a slow period or to absorb more concurrent users, when queuing is better handled by adding clusters through scaling. Databricks' cost-optimization best practices advise sizing SQL warehouses on concurrent users and query complexity, starting with Small or Medium and enabling autoscaling.

Cluster size

Sets the driver instance and worker count per cluster; each step up doubles the workers, and DBU consumption rises with size

Running time

DBUs accrue per second for as long as the warehouse runs, including idle time until Auto Stop

Warehouse type

Serverless SQL DBU prices include cloud instance cost; Pro and Classic warehouses also incur cloud instance charges from the cloud provider

Scaling

Each additional cluster added for concurrency bills at the same size, so size and cluster count multiply

• Join system.billing.usage (billing_origin_product = 'SQL', usage_metadata.warehouse_id) to the latest system.compute.warehouses record to rank warehouses by DBUs alongside warehouse_size, warehouse_type, min_clusters and max_clusters
• Flag warehouses still at the X_LARGE default, or at LARGE and above, that serve dashboards, BI tools or ad hoc queries
• In system.query.history, filter by compute.warehouse_id and check spilled_local_bytes, execution_duration_ms and read_bytes; Databricks' sizing guidance ties the need for a larger size to queries spilling to disk, so large warehouses whose queries never spill and finish quickly are candidates to size down
• Check waiting_at_capacity_duration_ms and SCALED_UP events in system.compute.warehouse_events; if queuing is the problem, it points to more clusters rather than a larger size
• Use the query profile for the heaviest statements to confirm that a smaller size would not push them into spilling

• Step the cluster size down one level at a time and compare query duration, spill and queue time for a representative period before the next step
• Handle concurrency with scaling instead of size: raise max_clusters for peak concurrency and keep min_clusters low, since each cluster bills at the full size
• Separate heavy SQL ETL or large ad hoc scans from dashboard traffic so dashboards can run on a small warehouse and heavy jobs on a larger one only while they run
• Set a smaller size in warehouse templates, Terraform modules or policies so new warehouses do not inherit the X-Large default
• Review sizing periodically as dashboards and data volumes change, since a size chosen for a one-time backfill or launch tends to stick

• SQL warehouse sizing, scaling, and queuing behaviordocs.databricks.com
• Create a SQL warehousedocs.databricks.com
• Best practices for cost optimizationdocs.databricks.com
• Query history system table referencedocs.databricks.com
• Warehouses system table referencedocs.databricks.com
• Databricks Lakehouse Pricingdatabricks.com