Databricks system tables for cost - SQL cheat sheet for DBU usage by job, cluster, warehouse, user and tag

#Access

System tables live in the system catalog and need Unity Catalog. A metastore admin or account admin grants access:

GRANT USE CATALOG ON CATALOG system TO `finops`;
GRANT USE SCHEMA ON SCHEMA system.billing TO `finops`;
GRANT SELECT ON SCHEMA system.billing TO `finops`;

Some schemas must be enabled before they appear. Account admins enable them in the account console or with the system schemas API. Check the documentation for your account.

Tip

list_prices holds list prices. Your actual Azure charges depend on your agreement and any commit discounts. Use these queries for relative comparisons and trends; use Azure Cost Management for the invoice amount.

#Usage by SKU, last 30 days

SELECT sku_name, usage_unit, round(sum(usage_quantity), 1) AS quantity
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY quantity DESC;

#Estimated list cost by day and SKU

SELECT u.usage_date, u.sku_name,
       round(sum(u.usage_quantity * p.pricing.default), 2) AS list_cost
FROM system.billing.usage u
JOIN system.billing.list_prices p
  ON u.sku_name = p.sku_name
 AND u.usage_unit = p.usage_unit
 AND u.usage_end_time >= p.price_start_time
 AND (p.price_end_time IS NULL OR u.usage_end_time < p.price_end_time)
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY u.usage_date, list_cost DESC;

Reuse that join in the queries below to turn DBUs into list cost.

#Top jobs

SELECT u.workspace_id, u.usage_metadata.job_id AS job_id,
       round(sum(u.usage_quantity), 1) AS dbus
FROM system.billing.usage u
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
  AND u.usage_metadata.job_id IS NOT NULL
GROUP BY ALL
ORDER BY dbus DESC
LIMIT 20;

Add job names from the Lakeflow jobs system table (latest row per job):

WITH jobs AS (
  SELECT workspace_id, job_id, name,
         row_number() OVER (PARTITION BY workspace_id, job_id ORDER BY change_time DESC) AS rn
  FROM system.lakeflow.jobs
)
SELECT j.name, round(sum(u.usage_quantity), 1) AS dbus
FROM system.billing.usage u
JOIN jobs j ON u.workspace_id = j.workspace_id AND u.usage_metadata.job_id = j.job_id AND j.rn = 1
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL ORDER BY dbus DESC LIMIT 20;

#Top all-purpose clusters

SELECT u.usage_metadata.cluster_id AS cluster_id, round(sum(u.usage_quantity), 1) AS dbus
FROM system.billing.usage u
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
  AND u.sku_name LIKE '%ALL_PURPOSE%'
GROUP BY ALL ORDER BY dbus DESC LIMIT 20;

Join system.compute.clusters on cluster_id (latest row per cluster) to see the cluster name and owner. All-purpose clusters running scheduled work are a common saving: move that work to job compute.

#SQL warehouses

SELECT u.usage_metadata.warehouse_id AS warehouse_id, u.usage_date,
       round(sum(u.usage_quantity), 1) AS dbus
FROM system.billing.usage u
WHERE u.usage_date >= current_date() - INTERVAL 14 DAYS
  AND u.usage_metadata.warehouse_id IS NOT NULL
GROUP BY ALL ORDER BY u.usage_date, dbus DESC;

A warehouse using DBUs every hour of the night usually has auto-stop set too high or a dashboard refresh keeping it awake.

#By user (serverless and jobs)

SELECT identity_metadata.run_as AS run_as, sku_name, round(sum(usage_quantity), 1) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
  AND identity_metadata.run_as IS NOT NULL
GROUP BY ALL ORDER BY dbus DESC LIMIT 20;

#By custom tag (cost centre, team)

SELECT custom_tags['CostCenter'] AS cost_center, round(sum(usage_quantity), 1) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL ORDER BY dbus DESC;

A NULL cost centre shows how much usage is untagged. Enforce tags with cluster policies and budget policies (for serverless).

#Day-over-day spike finder

WITH daily AS (
  SELECT usage_date, workspace_id, sku_name, sum(usage_quantity) AS q
  FROM system.billing.usage
  WHERE usage_date >= current_date() - INTERVAL 15 DAYS
  GROUP BY ALL
)
SELECT *, round(q / nullif(avg_prev, 0), 1) AS ratio
FROM (
  SELECT *, avg(q) OVER (PARTITION BY workspace_id, sku_name ORDER BY usage_date
                         ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_prev
  FROM daily)
WHERE usage_date = current_date() - INTERVAL 1 DAY AND q > 2 * avg_prev
ORDER BY q DESC;

Schedule this as a SQL alert to catch runaway compute the next morning.

#Notes

• Usage data arrives with a delay of a few hours. Don't alert on the current day.
• Column names in system tables can be added or change between releases. Run DESCRIBE system.billing.usage to check yours.
• Records can be corrected later (the record_type column shows corrections), so sum all records rather than picking one.

#Checklist

• system.billing access granted to FinOps
• Daily cost by SKU dashboard
• Top jobs, clusters and warehouses reviewed monthly
• Tag coverage measured and enforced
• Spike alert scheduled