Why Databricks SQL warehouse costs climb, and how to find your own number

Your own number

Measure your own SQL warehouse cost and repeat queries

Two read-only queries get you most of the picture. First, what each warehouse consumed per day over the last 30 days:

SELECT
  usage_metadata.warehouse_id  AS warehouse_id,
  usage_date,
  SUM(usage_quantity)          AS dbus
FROM system.billing.usage
WHERE usage_metadata.warehouse_id IS NOT NULL
  AND usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY usage_date, warehouse_id;

Then, what is asking, and how often it asks the same thing, from system.query.history:²

SELECT
  compute.warehouse_id                  AS warehouse_id,
  executed_by,
  COUNT(*)                              AS statements,
  COUNT(DISTINCT statement_text)        AS distinct_statements,
  ROUND(1 - COUNT(DISTINCT statement_text) / COUNT(*), 3) AS repeat_share
FROM system.query.history
WHERE compute.type = 'WAREHOUSE'
  AND statement_type = 'SELECT'
  AND start_time >= current_timestamp() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY statements DESC
LIMIT 50;

A service account with thousands of statements, a handful of distinct texts and a warehouse that never reaches zero DBUs in a day is the pattern to look for. The system tables have to be enabled, and both are read by an admin or someone they grant.