Databricks SQL warehouses that served no queries in 30 days

An unused warehouse is a standing liability

A SQL warehouse with no queries is either stopped and costing nothing, or running and billing for nothing. Databricks warns on its warehouse settings page that idle SQL warehouses keep accumulating DBU and cloud instance charges until they are stopped. Even when auto-stop keeps it cold, a forgotten warehouse still gets woken by a stale dashboard refresh or a BI connection, and every wake-up is billed. If nobody has queried it for a month, it is worth asking whether it should exist.

Proving a warehouse is unused

The system.query.history table records statements run on SQL warehouses, with the warehouse in compute.warehouse_id. Compare the warehouse list against 30 days of history:

SELECT compute.warehouse_id, COUNT(*) AS statements, MAX(start_time) AS last_query
FROM system.query.history
WHERE start_time >= current_date() - 30
  AND compute.type = 'WAREHOUSE'
GROUP BY compute.warehouse_id;

Any warehouse ID from databricks warehouses list that does not appear in this result had no statements in the window. Records are typically available within an hour, so the last hour may be incomplete.

Conditions for the orphan label

ZopNight needs a recorded query count for the warehouse over the last 30 days, and the count must be zero. It reads that count from the SQL Query History API (GET /api/2.0/sql/history/queries), not from the system.query.history table, so no system-table grant is needed. It also needs to know how old the warehouse is, and the warehouse must be at least 30 days old, so one created for a project that has not gone live yet is left alone. The state of the warehouse is not checked: zero usage is zero usage whether it is running or stopped.

When a quiet warehouse is left alone

If the query count was not collected, the rule does not assume the worst and stays silent. The same happens when the warehouse’s age cannot be established, which rules out flagging a warehouse that may have been created days ago. A single query anywhere in the 30 days clears it.

Why no dollar value is shown

What a dead warehouse costs depends on how often something wakes it and for how long, and ZopNight does not have a DBU quantity for that here. The finding is therefore cleanup with no saving attached. Warehouses that are in use but slow to stop are covered by SQL Warehouse Auto-Stop Disabled/Excessive.

Decommissioning the warehouse

1. Check for dashboards, alerts, scheduled queries and BI connections that name the warehouse, and repoint them to an active one.
2. If it may be needed later, stop it with databricks warehouses stop <id> and set a short auto-stop with databricks warehouses edit <id> --auto-stop-mins 10.
3. Otherwise delete it with databricks warehouses delete <id>.