Databricks Cost Leak Hunter

Databricks Cost Leak Hunter

Audits a Databricks workspace for real-dollar cost leaks — idle compute, jobs on the wrong SKU, overprovisioned clusters, and the Photon premium paid without the speedup — then emits a CFO-grokkable, dollar-ranked FinOps remediation report.

Overview

This skill finds where Databricks money is leaking and how much, in dollars, per month. Confirmed-spend figures come from the customer's own system.billing.usage joined to system.billing.list_prices — never an estimate. Two of the four categories (overprovisioning, Photon premium) are explicitly modeled/at-risk amounts, labeled as such so a CFO never confuses them with recoverable spend. The skill surfaces four named leak categories, ranks them by monthly dollar impact, and explains each root cause in FinOps language a CFO can act on without an engineer to translate.

It is architecturally distinct from the v1 databricks-cost-tuning skill: that one AUTHORS policy (creates cluster policies, spot configs). This one DETECTS leaks and reports them, dollarized and ranked. The math is deterministic — bundled scripts in scripts/ do the arithmetic so the agent never eyeballs numbers — and deep domain knowledge lives in references/ loaded only when a leak needs it.

The skill uses two data planes. Dollar figures come from the Databricks CLI Statement Execution API (databricks api post /api/2.0/sql/statements) reading system.* — authenticated by the CLI's own DATABRICKS_HOST+DATABRICKS_TOKEN / databricks auth login. The live config/event evidence that explains why a leak exists (auto-termination setting, node type, autoscale floor, pool min_idle) comes from the custom databricks-workspace-mcp control-plane tools — the one MCP dependency. The SQL produces the number; the workspace MCP turns it into a verified, single-config-change fix.

Prerequisites

Read access to the billing system tables is the hard dependency and the most common failure. system.billing.usage requires a metastore-admin grant chain — the skill detects a missing grant upfront and reports it, rather than failing mid-flow.

• Databricks Premium or Enterprise workspace with Unity Catalog (system tables are UC-governed; not available on Standard).
• Metastore-admin grant chain on the billing schema, granted by a metastore admin to the running principal:
  • GRANT USE CATALOG ON CATALOG system TO <principal>
  • GRANT USE SCHEMA ON SCHEMA system.billing TO <principal>
  • GRANT SELECT ON TABLE system.billing.usage TO <principal>
  • GRANT SELECT ON TABLE system.billing.list_prices TO <principal>
• Compute system schema for config/utilization corroboration (same chain on system.compute): system.compute.clusters, system.compute.node_timeline.
• Databricks CLI authenticated (databricks auth login, or DATABRICKS_HOST + DATABRICKS_TOKEN) and jq for parsing JSON tool output. The dollar queries run through the CLI Statement Execution API — UC enforces the grant chain above.
• DATABRICKS_WAREHOUSE_ID env var set to a running SQL warehouse — every statement-execution call (Step 1's probe included) requires it.
• databricks-workspace-mcp registered (its own PAT/U2M/M2M auth; PAT unsupported in Databricks-App deployment mode). It reads the live REST API, not system.*, so it needs no system-table grants. If it is absent the skill still produces dollar figures but cannot corroborate live config — it then accepts pasted config input.

Authentication. The CLI Statement Execution API uses DATABRICKS_HOST + DATABRICKS_TOKEN or databricks auth login; UC enforces the metastore grant chain on every system.* read. The custom databricks-workspace-mcp authenticates separately via its own PAT / U2M / M2M token. No secrets are hardcoded — all auth comes from the environment or the registered MCP server.

Run the upfront grant check before any analysis — see Step 1.

Instructions

The pipeline is detect → compute → rank → report. SQL detection runs through the CLI Statement Execution API; config corroboration runs on databricks-workspace-mcp; the dollar arithmetic runs in scripts/; deep knowledge loads from references/ on demand.

Step 1: Verify the Grant Chain (fail fast, not mid-flow)

Probe the billing tables before anything else. If the probe errors with a permission message, STOP and report the exact missing grant — do not continue into the leak scans. Requires DATABRICKS_WAREHOUSE_ID (a running SQL warehouse).

databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id": "'"$DATABRICKS_WAREHOUSE_ID"'",
  "statement": "SELECT 1 FROM system.billing.usage LIMIT 1",
  "wait_timeout": "30s"
}' | jq -r '.status.state, .status.error.message // "ok"'

If state is not SUCCEEDED, load ${CLAUDE_SKILL_DIR}/references/system-tables-setup.md and report the missing grant chain to the user verbatim. Stop here.

Step 2: Pull the Spend Baseline

Establish the trailing-30-day total spend so every leak can be expressed as a share of a real number, and capture the window's MAX(usage_date) to stamp into the report. The price-window join (usage × list_prices.pricing.default, matched on sku_name AND usage_unit within the price-effective window, currency_code='USD') is the dollar primitive reused by every category query.

# The CLI does NOT expand ${VARS} inside a --json @file, so inject the warehouse
# id with jq at call time (the static template carries only wait_timeout + statement).
databricks api post /api/2.0/sql/statements --json "$(
  jq --arg wh "$DATABRICKS_WAREHOUSE_ID" '. + {warehouse_id: $wh}' \
    "${CLAUDE_SKILL_DIR}/scripts/sql/spend-baseline.sql.json"
)"

The canonical CTE and full per-category SQL live in ${CLAUDE_SKILL_DIR}/references/cost-leak-categories.md. Load it now — the four detection queries below all reference its priced CTE.

Step 3: Detect Leak 1 — Clusters That Never Auto-Terminate

Join priced All-Purpose usage to system.compute.clusters; flag clusters whose latest-change auto_termination_minutes = 0. Rank by 30-day idle spend. This is confirmed spend — money actually billed for idle compute.

SELECT p.usage_metadata.cluster_id AS cluster_id,
       COALESCE(c.cluster_name, 'unknown') AS cluster_name,
       c.auto_termination_minutes,
       ROUND(SUM(p.usd), 2) AS spend_30d_usd
FROM priced p
JOIN cluster_cfg c ON p.usage_metadata.cluster_id = c.cluster_id
WHERE p.billing_origin_product = 'ALL_PURPOSE'
  AND c.auto_termination_minutes = 0
GROUP BY p.usage_metadata.cluster_id, c.cluster_name, c.auto_termination_minutes
HAVING SUM(p.usd) > 0
ORDER BY spend_30d_usd DESC;

Corroborate each flagged cluster's live config with databricks-workspace-mcp clusters_get (confirm autotermination_minutes = 0 right now) and clusters_events (measure the idle gap between RUNNING and TERMINATING).

Step 4: Detect Leak 2 — Scheduled Jobs on All-Purpose Compute

The signature leak: a usage row with a job_id in usage_metadata AND billing_origin_product = 'ALL_PURPOSE' ($0.55/DBU) instead of JOBS_COMPUTE ($0.15/DBU). Re-price the same DBUs at the current Jobs rate to compute savings. This is confirmed savings — a deterministic re-pricing delta. The jobs_rate CTE is deduped to one USD rate per usage_unit so the join cannot fan out (see cost-leak-categories.md).

SELECT p.usage_metadata.job_id AS job_id,
       ROUND(SUM(p.usd), 2) AS spend_on_all_purpose_30d_usd,
       ROUND(SUM(p.usd) - SUM(p.usage_quantity * jr.jobs_unit_price), 2)
         AS potential_savings_30d_usd
FROM priced p
JOIN jobs_rate jr ON p.usage_unit = jr.usage_unit
WHERE p.billing_origin_product = 'ALL_PURPOSE'
  AND p.usage_metadata.job_id IS NOT NULL
GROUP BY p.usage_metadata.job_id
HAVING SUM(p.usd) - SUM(p.usage_quantity * jr.jobs_unit_price) > 0
ORDER BY potential_savings_30d_usd DESC;

Confirm the live compute type is All-Purpose (not Jobs) with clusters_list / clusters_get (cluster_source) before recommending the move. The job_id + ALL_PURPOSE billing signal is itself dollar-accurate; the REST check is belt-and-suspenders.

Step 5: Detect Leak 3 — Overprovisioned Clusters Idling Below Floor

Aggregate mean CPU from system.compute.node_timeline, join to 30-day spend, flag clusters burning real dollars at chronically low utilization (< 25%). This figure is an estimate (est_overprovision = spend × (1 − CPU%)), not billed waste — it is the one modeled number in the pipeline and is labeled est_* everywhere.

SELECT s.cluster_id,
       ROUND(u.avg_cpu_pct, 1) AS avg_cpu_pct,
       ROUND(s.spend_30d_usd, 2) AS spend_30d_usd,
       ROUND(s.spend_30d_usd * (1 - LEAST(u.avg_cpu_pct,100)/100.0), 2)
         AS est_overprovision_30d_usd
FROM spend s
JOIN util u ON s.cluster_id = u.cluster_id
WHERE u.avg_cpu_pct < 25 AND s.spend_30d_usd > 0
ORDER BY est_overprovision_30d_usd DESC;

Corroborate the configured floor with clusters_get (REST nested autoscale.min_workers / autoscale.max_workers); for the idle-pool variant use instance_pools_list (min_idle_instances + stats.idle_count) — pool waste is NOT a billing row.

Step 6: Detect Leak 4 — Photon Premium Without the Speedup

Photon is not a column on system.compute.clusters; it is billing-visible via the SKU. Isolate usage whose sku_name ILIKE '%PHOTON%' and surface the ~2× premium portion as the at-risk amount — money for review against actual runtime gain, not confirmed waste.

SELECT p.usage_metadata.cluster_id AS cluster_id,
       ROUND(SUM(p.usd), 2)        AS photon_spend_30d_usd,
       ROUND(SUM(p.usd) / 2.0, 2)  AS photon_premium_at_risk_30d_usd
FROM priced p
WHERE p.sku_name ILIKE '%PHOTON%'
  AND p.billing_origin_product IN ('ALL_PURPOSE','JOBS_COMPUTE')
  AND p.usage_metadata.cluster_id IS NOT NULL
GROUP BY p.usage_metadata.cluster_id
HAVING SUM(p.usd) > 0
ORDER BY photon_premium_at_risk_30d_usd DESC;

Confirm Photon is live and worth keeping with databricks-workspace-mcp clusters_get (REST runtime_engine — a config-plane field, not a system column); for DLT pipelines use pipelines_get (spec.photon / serverless / edition). See ${CLAUDE_SKILL_DIR}/references/dlt-tier-cost-tradeoffs.md when the leak touches DLT/serverless tiers.