Hunt down Databricks cost leaks — wasted DBUs, idle clusters, oversized SQL
warehouses, and untagged runaway spend — and produce a FinOps cost report.
Use when a user asks why their Databricks bill is high, wants to find cost
leaks / wasted DBUs / idle clusters, or needs a FinOps cost report.
Trigger with "databricks cost", "why is my databricks bill",
"find wasted spend", "cost leak".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
The command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
File Explorer
12 files
Showing SKILL.md
SKILL.md
Source instructions · Read-only preview
name
databricks-cost-leak-hunter
description
Hunt down Databricks cost leaks — wasted DBUs, idle clusters, oversized SQL
warehouses, and untagged runaway spend — and produce a FinOps cost report.
Use when a user asks why their Databricks bill is high, wants to find cost
leaks / wasted DBUs / idle clusters, or needs a FinOps cost report.
Trigger with "databricks cost", "why is my databricks bill",
"find wasted spend", "cost leak".
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"'
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"
)"
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 =0GROUPBY p.usage_metadata.cluster_id, c.cluster_name, c.auto_termination_minutes
HAVINGSUM(p.usd) >0ORDERBY spend_30d_usd DESC;
Corroborate each flagged cluster's live config with databricks-workspace-mcpclusters_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 ISNOT NULLGROUPBY p.usage_metadata.job_id
HAVINGSUM(p.usd) -SUM(p.usage_quantity * jr.jobs_unit_price) >0ORDERBY 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.
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 <25AND s.spend_30d_usd >0ORDERBY 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 ISNOT NULLGROUPBY p.usage_metadata.cluster_id
HAVINGSUM(p.usd) >0ORDERBY photon_premium_at_risk_30d_usd DESC;
Confirm Photon is live and worth keeping with databricks-workspace-mcpclusters_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.
Step 7: Compute, Rank, and Write the Report
Pass each category's query result to the deterministic ranker — the LLM does NOT do
the arithmetic. Each leak object carries a kind field
(confirmed / estimated / at-risk) so the renderer can split the headline into
confirmed-recoverable vs estimated/at-risk-pending-review and stamp a Confidence
column. The script sums per-category figures by kind, ranks descending by monthly
dollar impact, annualizes the headline and #1 line, stamps the trailing-30-day window
end date, and renders the CFO-grokkable report.
# Per-category results and the rendered report are RUNTIME outputs — they go to# a working dir ($OUT), never the skill package. Steps 3–6 wrote leak-*.json here.
OUT="${OUT:-$(pwd)/cost-leak-out}" && mkdir -p "$OUT"
jq -s '.'"$OUT"/leak-*.json | \
python3 "${CLAUDE_SKILL_DIR}/scripts/rank-and-report.py" \
--monthly-spend 100000 \
--window-end "$WINDOW_END_DATE" \
--out "$OUT/cost-leak-report.md"
Use Glob to collect the per-category leak-*.json results, Write the rendered
report, and Edit it if the user wants the headline spend rescaled. Render the
output using the verbatim template in
${CLAUDE_SKILL_DIR}/references/cfo-output-format.md.
Output
A CFO-grokkable report file ($OUT/cost-leak-report.md in the working dir) leading
with a split headline that never sums confirmed and unconfirmed dollars under
one verb — ### A $<spend>/month workspace is burning **~$<confirmed>/month** (confirmed), plus up to **~$<at-risk>/month** pending review — each with its
~$<annualized>/year companion.
A trailing-30-day window stamp under the headline
(Trailing 30 days ending <window-end>) so every figure has an explicit calendar
window, not just a /month cadence label.
The ranked leak table with a Confidence column
(# | Where it's leaking | $/month | Confidence | The fix), one row per category,
ranked highest dollar impact first, $/month right-aligned, each fix a single
config change. Root-cause cells use plain-business language — no raw DBU unit in
the CFO-visible text (DBU detail stays in the per-leak detail artifacts).
The #1-line callout — the top leak annualized, named, with its confidence, and
stated as fixed in one setting.
The assumed-vs-cited disclosure — only the workspace-spend input is assumed;
on a live run confirmed figures are computed from system.billing.usage, while
overprovision (estimated) and Photon premium (at-risk) are labeled as modeled.
Per-leak detail artifacts: idle-cluster list, all-purpose-job migration list
with per-job savings, overprovisioned-cluster rightsizing list, Photon-premium
at-risk list — each with the corroborating live config from databricks-workspace-mcp
and the underlying $/DBU rates for engineers.
Error Handling
Error
Cause
Solution
PERMISSION_DENIED on system.billing.usage
Metastore-admin grant chain missing
Run Step 1; report the exact GRANT USE CATALOG / USE SCHEMA / SELECT chain from system-tables-setup.md. Stop, do not continue.
CLI not authenticated / token expired
No valid DATABRICKS_HOST + token
Re-run databricks auth login; verify with databricks current-user me.
Empty / unset DATABRICKS_WAREHOUSE_ID
Required warehouse for statement execution not set
Set DATABRICKS_WAREHOUSE_ID to a running SQL warehouse before Step 1.
list_prices join returns NULL usd
Custom/negotiated pricing not in list_prices, or usage_unit mismatch
Join on sku_name AND usage_unit within the price window with currency_code='USD'; if still NULL, use the customer's contracted rate card from references/cost-leak-categories.md.
Workspace MCP missing
Server not registered
Degrade gracefully: report it absent, run the dollar half, accept pasted config for corroboration. Never fail silently mid-flow.
node_timeline empty for a cluster
Serverless/short-lived compute, or monitoring lag
Skip Leak 3 for that cluster; note "utilization unavailable" rather than reporting $0 overprovision.
Untagged spend / no cluster_name
Clusters lack CostCenter/Team tags
Attribute by cluster_id; flag attribution as incomplete in the report footer.
Examples
Example 1: "Why is my Databricks bill high?"
Runs the full pipeline. The grant check passes, the four scans return rows, and the
ranker emits the CFO report with a split, confidence-stamped headline:
### A $100K/month Databricks workspace is burning **~$19,000/month** (confirmed), plus up to **~$8,000/month** pending review
Trailing 30 days ending 2026-06-22. Confirmed ~$228K/year; up to ~$96K/year more pending review. Every line below is one config change.
| # | Where it's leaking | $/month | Confidence | The fix |
|---|---|--:|---|---|
| 1 | Clusters that never shut themselves off — paying around the clock for compute nobody is using | **$12,000** | Confirmed | Set auto-shutoff (e.g. 30 min) |
| 2 | Scheduled batch jobs running on the premium notebook tier — ~3.6× the batch rate for identical work | **$7,000** | Confirmed | Move job clusters to the batch tier |
| 3 | Clusters sized for peak, idling most of the time — typically 30–50% oversized | **$5,000** | Estimated | Turn on autoscaling, drop the floor |
| 4 | Paying a ~2× speed-engine premium on jobs that don't run faster | **$3,000** | At-risk | Turn off the speed engine where it adds no gain |
**The #1 line alone — idle clusters (confirmed) — is ~$144K/year, fixed in one setting.**
Example 2: Idle-Cluster Sweep
User asks "find idle clusters wasting money." The skill runs Step 3 only, joins the
spend to clusters_get, and reports each auto_termination_minutes = 0 cluster with
its 30-day idle spend and the live idle gap from clusters_events.
Example 3: All-Purpose-Job Rightsizing
User asks "are any jobs on the wrong compute?" Step 4 returns each job_id running
on All-Purpose with potential_savings_30d_usd, corroborated by clusters_get
confirming cluster_source is not JOB — the single fix is "move to Jobs Compute."
