Databricks query library: the receipts, unbundled.

The number, and the receipt behind it. The dashboard writes the finding; this hands you the SELECT behind it.

When your Databricks bill jumps, every tool hands you a bigger number and asks you to trust it. This library is what we run instead — ninety-six plain SELECTs against your own system.* tables, with nothing between you and the row that produced the figure.

A number is not a receipt. A receipt shows you which warehouse, which query, which row — and hands you the SELECT that got there.

That is the opposite of consultant lock-in. A tool that emits a dollar figure and keeps its method is one you have to trust; a query you can read, rerun, and modify is a method you own — after a handoff, your team reproduces every figure without us in the room. And you can only own a method whose blind spots are labelled, which is why so much of what follows is about knowing when a blank result means not enabled rather than not spending.

A sibling product — Crosshire Audit — writes these findings for you in an in-browser dashboard, the fast path when you just want the ranked list. This is the same method with nothing hidden: walked query by query on Crosshire Learn, and open on GitHub at github.com/darshanmeel/crosshire-audit-databricks-admin — each a self-contained .sql file that names itself on its first line (-- query_id: <name>) and runs as-is against your own metastore.

What's in the box.

Each domain ships a README whose Availability section states, per table, whether it is GA, Public Preview, or feature-gated — because knowing when a blank result means not enabled is half the honesty in this library. More on that in section 07.

Cost & Billing: the bill tells you that you spent. Not whether you can trust the number.

The first question is not "how much." It is "can I trust the sum at all." cost_restatement_trust_metric answers it by reading record_type off system.billing.usage and refusing the intuitive mistake:

SELECT cloud,
       SUM(usage_quantity)                                                             AS net_usage_quantity,
       SUM(CASE WHEN record_type = 'ORIGINAL'    THEN usage_quantity      ELSE 0 END)  AS original_usage_quantity,
       SUM(CASE WHEN record_type = 'RETRACTION'  THEN ABS(usage_quantity) ELSE 0 END)  AS retracted_abs_quantity,
       SUM(CASE WHEN record_type = 'RESTATEMENT' THEN usage_quantity      ELSE 0 END)  AS restatement_usage_quantity,
       MAX(ingestion_date)                                                             AS max_ingestion_date
FROM system.billing.usage
WHERE usage_date >= dateadd(day, -:period_days, current_date())
  AND usage_date <  current_date()
GROUP BY cloud;

Filter to record_type = 'ORIGINAL' — the obvious move — and you inflate the bill by every correction Databricks ever issued. RETRACTION and RESTATEMENT rows already net against ORIGINAL; the true figure only balances when you SUM across all three. ingestion_date (distinct from usage_date) is the lag signal that flags the most recent day as provisional. This is a confidence: confirmed trust check, and it runs before any dollar figure is allowed to matter.

Then cost_actual_vs_list_by_sku puts negotiated dollars next to list dollars per SKU, joining system.billing.account_prices (pricing.default) against system.billing.list_prices (pricing.effective_list.default) on the price row active on each usage_date. A discount is only as real as the SKUs it lands on; this surfaces the DBUs where the contracted discount realized zero, and any SKU with no matching price row appears as a NULL rate — a coverage gap, never a silent $0.

And cost_chargeback_by_tag explodes custom_tags with LATERAL VIEW OUTER explode, so untagged DBUs stay in the denominator instead of dropping out. It exposes the subtler trap too: count distinct tag_value per tag_key, and an umbrella key that stamps every DBU with one cost center reveals itself.

Query Performance: Databricks bills per warehouse-hour and shows you zero dollars per query.

All 11 queries here read one table, system.query.history — one row per completed statement. There is no per-query DBU column anywhere in Databricks. So query_costly_statements uses the documented proxy: warehouse DBUs allocate roughly in proportion to execution_duration_ms, so ranking by execution time is ranking by cost.

SELECT
  workspace_id, statement_id, statement_type,
  compute.warehouse_id AS warehouse_id,
  execution_duration_ms, total_task_duration_ms,
  read_bytes, read_files, pruned_files,
  spilled_local_bytes, shuffle_read_bytes, from_result_cache
FROM system.query.history
WHERE start_time >= dateadd(day, -:period_days, current_date())
  AND start_time <  current_date()
  AND execution_status = 'FINISHED'
  AND from_result_cache = false
  AND execution_duration_ms > 0
ORDER BY execution_duration_ms DESC
LIMIT 1000;

The counters bolted onto each row tell you which fix each expensive statement needs. query_pruning_effectiveness computes pruned_files / (pruned_files + read_files): when pruning is a rounding error next to files read, you are paying to scan whole tables you meant to skip — the receipt for a Z-ORDER or liquid-clustering project. query_local_spillage rolls up statements where spilled_local_bytes > 0: every byte is a query that ran out of RAM and paid for it in wall-clock DBUs. The file is honest that there is no spilled_remote_bytes column, so remote spill is unmeasurable, and classic-cluster work never lands in system.query.history at all.

Compute & Jobs: the idle you're paying for is a ratio you can read off, not a hunch.

compute_idle_node_ratio reads per-minute telemetry from system.compute.node_timeline — driver and every worker — and counts the node-minutes below 5% CPU (cpu_user_percent + cpu_system_percent < 5). It returns idle_slices over total_slices per cluster: measured hardware idle, not a config guess, plus the average-CPU and average-memory columns that are the right-sizing evidence for shrinking the node type. compute_warehouse_idle_gaps puts a stopwatch on the RUNNING-but-idle tail of every SQL warehouse via LEAD(event_time) over system.compute.warehouse_events — the exact number that justifies dropping auto_stop_minutes. And classic_clusters_config_current reduces the change-tracked (SCD) history of system.compute.clusters to the newest row per cluster_id, so policy_id IS NULL and a never-terminating auto_termination_minutes name themselves.

The jobs domain turns reliability into a dollar-ranked backlog. lakeflow_jobs_on_all_purpose catches scheduled task-runs paying the all-purpose premium to do batch work a job cluster would run cheaper — same compute, wrong SKU:

WITH task_compute AS (
  SELECT workspace_id, job_id, run_id, EXPLODE(compute_ids) AS compute_id
  FROM system.lakeflow.job_task_run_timeline
  WHERE period_start_time >= date_add(current_date(), -30)
    AND period_end_time   <  date_trunc('DAY', current_timestamp())
    AND result_state IS NOT NULL AND compute_ids IS NOT NULL
)
SELECT tc.workspace_id, tc.job_id, tc.compute_id, c.cluster_source,
       COUNT(DISTINCT tc.run_id) AS task_runs
FROM task_compute tc
LEFT JOIN (
  SELECT workspace_id, cluster_id, cluster_source
  FROM system.compute.clusters
  QUALIFY ROW_NUMBER() OVER (PARTITION BY workspace_id, cluster_id ORDER BY change_time DESC) = 1
) c ON tc.workspace_id = c.workspace_id AND tc.compute_id = c.cluster_id
GROUP BY tc.workspace_id, tc.job_id, tc.compute_id, c.cluster_source;

Any row where cluster_source IN ('UI','API') is a strong rate-arbitrage candidate — a compute reassignment, no code change — pending confirmation that compute_ids is populated on the older rows. The query is self-flagged confidence: needs_confirmation for exactly that reason: EXPLODE(compute_ids) drops rows with NULL compute_ids, and those belong in a separate not assessed bucket, never an implicit zero. That is why the window here is a fixed 30 days you edit in the SQL, not a :period_days parameter — see section 08.

lakeflow_failed_jobs_wasted_dbus ranks the flaky-job backlog by a wasted-DBU proxy (net job DBUs × failed_runs / distinct_runs, joining system.lakeflow.job_run_timeline to system.billing.usage) and carries the latest termination_code by MAX_BY — so you know whether it is a code bug or a quota problem before opening a log. And lakeflow_succeeded_with_failed_tasks counts the worst lie in the scheduler: runs that reported job-level SUCCEEDED while a task quietly FAILED. Green dashboard, dropped work, no alert. It carries a confidence: confirmed label — one of the domain's confirmed checks.

Serving, Storage, Governance: waste you never approved, and risk nobody is watching.

These three domains are where the receipts stop being about the bill and start being about things nobody is looking at — and the sharpest of them is a clean anti-join.

access_classified_unmasked anti-joins Databricks' own classifier (system.data_classification.results, confidence = 'HIGH') against system.information_schema.column_masks. The platform's own tooling found and tagged your PII at high confidence; this query proves how many of those exact columns carry no mask at all. It is not a heuristic and not our opinion of what looks sensitive — it is Databricks' HIGH-confidence classification, minus the columns that have a mask, and the remainder is the masking backlog with catalog, schema, table, and column named. access_pii_propagation_untagged catches the next failure mode: a tagged source column copied into a new physical column that shed its sensitivity tag on the way out — and hands you the created_by principal who did it.

On the storage side, po_maintenance_cost_by_table sums the estimated maintenance DBUs of every automatic OPTIMIZE / VACUUM / CLUSTERING run in system.storage.predictive_optimization_operations_history — spend you never approved a job for. It reports estimated_dbu = SUM(usage_quantity) (its usage_unit is fixed to ESTIMATED_DBU, so the summed usage_quantity is a DBU figure, not a priced dollar), and grouping on operation_status surfaces the tables whose maintenance is silently FAILED. And compute_serving_dormant_endpoints LEFT-joins the latest config of every served entity in system.serving.served_entities to windowed traffic in system.serving.endpoint_usage; a NULL last_request_date is an endpoint you provisioned, still pay for, and that served nobody.

The honest edge: "treat every query as best-effort" is a feature, not an apology.

Databricks system.* tables are not uniformly available, and pretending otherwise is how audits lie. A query here can return nothing, or fail outright with TABLE_OR_VIEW_NOT_FOUND or insufficient_privileges, for reasons that are all normal:

• Public Preview / off-by-default. system.billing.attributed_usage, system.storage.predictive_optimization_operations_history, system.data_classification.results, and the Lakeflow pipelines tables are Preview; several must be enabled per-metastore by an admin. An un-enabled schema throws TABLE_OR_VIEW_NOT_FOUND — that is not enabled, not zero.
• Feature-gated. No Model Serving means an empty system.serving.*. No AI Gateway (opt-in per endpoint) means an empty system.ai_gateway.usage. No Predictive Optimization means no maintenance rows.
• Unity-Catalog-only and permission-gated. Every system.* read needs UC plus an admin GRANT of SELECT. information_schema views are privilege-aware, so results are always partial — column_masks excludes BROWSE-only tables and can overstate an unmasked count.
• Compute-limited coverage. system.query.history covers SQL-warehouse and serverless-SQL statements — not classic clusters — while system.compute.node_timeline covers classic clusters only, with no rows for SQL warehouses or serverless. So a zero-spill or zero-idle result may just mean the work ran on compute that table never records. node_timeline retains 90 days; exceed :lookback_days and it silently truncates.
• Late-populated columns. termination_code predates nothing before ~Aug-2024; several Lakeflow duration columns not before late 2025. Missing columns degrade to not assessed, never to a finding.

And the pricing is labelled for what it is: every dollar not joined to account_prices is est · at list, exactly as the Provenance note says.

Empty is not zero. A blank result means not assessed — never all-clear.

Each domain README carries this availability map per table, so you always know whether a gap is a saving you haven't found or a feature you haven't turned on.

Getting started.

Open crosshire-audit-databricks-admin on GitHub, pick a domain, and run any .sql in a Databricks SQL editor as the account admin (or any principal holding SELECT on system.*). Every file names itself in its first line — -- query_id: <name>. Most windowed queries expose a :period_days or :lookback_days parameter; a number of them — the performance and predictive-optimization families in particular, plus several Lakeflow and governance queries — instead use a fixed 30-day window (INTERVAL 30 DAYS or date_add(current_date(), -30)) that you edit directly in the SQL, as the featured all-purpose query above shows.

Start with cost_restatement_trust_metric to confirm your billing data is trustworthy, then cost_actual_vs_list_by_sku for the discount-realization headline, then walk the performance and compute domains for the tuning and idle backlog. Read the README's Availability section first; it tells you which queries your metastore can actually answer.

The dashboard will still write the findings for you when you want the fast path. This library is here for when you want to own the method — or hand it to the team that inherits the bill.