Your first Databricks audit: ten queries, one findings sheet.

Ninety-six queries is a reference shelf. An audit needs an order.

You've inherited a Databricks account. Or the bill grew a third last quarter and nobody can say why. Or a security review is coming, someone asked "are we sure nothing sensitive is exposed?", and you realised you don't actually know.

There is a query library that answers all of that: ninety-six plain SELECTs over your own system.* tables, no dashboard, nothing to install. But the honest problem with any library that size is that ninety-six queries with no order of operations is just a bigger homework assignment. Handing someone the whole folder and saying "audit the account" is like handing them a service manual and saying "fix the car."

So this is the order, in three moves — trust the numbers, chase the money, check the risk. It opens with one trust check that isn't itself a finding, just the ground the rest stands on; then ten queries — five that chase the money, five that check the risk — turn that trusted total into a findings sheet you can defend, in about ninety minutes.

Every one of the ten is the same shape as the rest of the library: a plain SELECT over your own Unity Catalog system.* tables, run as a principal that holds SELECT on system.*. The same ten are walked one by one on Crosshire Learn and open on GitHub at crosshire-audit-databricks-admin — each a self-contained .sql file that names itself on its first line (-- query_id: <name>).

The one rule that makes an audit trustworthy. A number is a claim; the query behind it is the receipt.

Before the first query, one principle runs through all ten. A dashboard shows you a total and asks you to believe it. An audit shows you which warehouse, which job, which row produced that total — so you can defend it in a budget meeting or a security review without saying "the tool told me so." Every query here is readable, rerunnable, and yours; when the engagement ends, whoever inherits the account inherits a method they can run again, not a black box they have to trust.

A number is a claim; the query behind it is the receipt.

Two conventions keep the receipts honest — and you should write both at the top of the findings sheet, before anything else:

• Every dollar is an estimate at list price — computed from published DBU rates in system.billing.list_prices, before your negotiated discount, and excluding cloud infra and egress. There is no system.billing.account_prices table on this account, so even the "negotiated" side is approximated from list_prices. It is directionally right for where the money is, never a substitute for your invoice. Don't present it as one. usage_quantity is DBUs, bytes, hours, or tokens — never dollars.
• An empty result means "not assessed," never "you're fine." System tables aren't uniformly available — a query can return nothing because a feature is off, a schema isn't enabled per-metastore, or you lack SELECT. A blank is a gap in coverage, not a clean bill of health. Record it as a gap.

A note on what follows: four of the ten come with their SQL in full and a short How to read it — not the four that matter most, but the four whose caveats matter most. Three are needs-confirmation proxies (a list-price stand-in for your negotiated rate; an EXPLODE that silently drops NULL compute; DBUs attributed to a job, not the individual failed run), and the fourth is the one risk check no dashboard can rank because it needs a join. Those are the rows you'd want the query behind before you trust the number; the other six read honestly enough from a one-line description.

Move 1 — Trust the numbers. Before any of them mean anything.

First — can you even trust the bill? Start here, before the ten, before you dollarize anything — this is the check almost everyone skips. Databricks doesn't just append usage; it restates it. Corrections arrive after the fact as retraction and restatement rows that net against the originals. If you sum only the original rows your total is inflated by every correction ever issued; if you don't know restatement is happening, you'll chase a discrepancy that was never real.

cost_restatement_trust_metric splits your usage by record_type — ORIGINAL, RETRACTION, RESTATEMENT — and measures what fraction of it was later corrected, confirming that a plain SUM(usage_quantity) already nets to the true figure (retracted quantities are stored negative, so the sum is self-correcting). On a healthy account the restated share is small and stable. If it's large, or max_ingestion_date shows recent days still settling, that uncertainty rides on every dollar figure below — so you write it at the top of the sheet first.

Move 2 — Chase the money. Now that you trust the sum, find where it goes.

1 · Turn DBUs into dollars. Usage tables count DBUs, bytes, hours, and tokens — never dollars. cost_dollarized_by_sku_day joins usage to list prices and gives you spend by SKU per day: the shape of your bill. You're looking for the big rocks and the trend line — a steady climb with no matching growth in workload is the thread you'll pull for the rest of the audit.

2 · Is your negotiated discount actually landing? You negotiated a rate — is it real? A Databricks discount isn't a flat percentage off the invoice; it applies per SKU, and the SKUs your workload lands on may not be the ones it covers. cost_actual_vs_list_by_sku puts the rate you're estimated to pay next to list, per SKU, and computes the realization ratio — what you pay divided by list. The closer that ratio sits to 1.0, the closer you're paying to full list, and the less of your negotiated discount is actually landing on that SKU.

SELECT u.cloud, u.sku_name, u.usage_unit, u.billing_origin_product,
       SUM(u.usage_quantity)                     AS net_usage_quantity,
       SUM(u.usage_quantity * dp.default_rate)   AS net_default_cost,   -- est. what you actually pay
       SUM(u.usage_quantity * lp.list_rate)      AS net_list_cost,      -- undiscounted list
       CASE
         WHEN SUM(u.usage_quantity * dp.default_rate) IS NULL
           OR SUM(u.usage_quantity * lp.list_rate)    IS NULL THEN 'NOT_ASSESSED'
         WHEN SUM(u.usage_quantity * dp.default_rate)
            / NULLIF(SUM(u.usage_quantity * lp.list_rate), 0) >= :crit_realization_ratio THEN 'CRITICAL'
         WHEN SUM(u.usage_quantity * dp.default_rate)
            / NULLIF(SUM(u.usage_quantity * lp.list_rate), 0) >= :warn_realization_ratio THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.billing.usage u
LEFT JOIN (SELECT sku_name, cloud, usage_unit, price_start_time, price_end_time,
                  CAST(pricing.default AS DOUBLE) AS default_rate
           FROM system.billing.list_prices) dp
  ON  u.sku_name = dp.sku_name AND u.cloud = dp.cloud AND u.usage_unit = dp.usage_unit
  AND u.usage_date >= DATE(dp.price_start_time)
  AND (dp.price_end_time IS NULL OR u.usage_date < DATE(dp.price_end_time))
LEFT JOIN (SELECT sku_name, cloud, usage_unit, price_start_time, price_end_time,
                  CAST(pricing.effective_list.default AS DOUBLE) AS list_rate
           FROM system.billing.list_prices) lp
  ON  u.sku_name = lp.sku_name AND u.cloud = lp.cloud AND u.usage_unit = lp.usage_unit
  AND u.usage_date >= DATE(lp.price_start_time)
  AND (lp.price_end_time IS NULL OR u.usage_date < DATE(lp.price_end_time))
WHERE u.usage_date >= dateadd(day, -:period_days, current_date())
  AND u.usage_date <  current_date()
  AND upper(u.usage_unit) = 'DBU'   -- only DBU rows get priced against a per-DBU rate
GROUP BY u.cloud, u.sku_name, u.usage_unit, u.billing_origin_product
ORDER BY net_default_cost / NULLIF(net_list_cost, 0) DESC NULLS LAST, net_list_cost DESC;

How to read it. One row is a cloud + SKU + unit, priced two ways. The higher the realization ratio, the closer to list you're paying and the less discount is landing — so a ratio at or above :warn_realization_ratio (default 0.75) is a WARN, and at or above :crit_realization_ratio (default 0.90) a CRITICAL. Either cost column NULL is NOT_ASSESSED, never a silent $0. The honest catch: with no account_prices table, both rates come from list_prices (the pay rate from pricing.default, list from pricing.effective_list.default), so this is a needs-confirmation proxy for a true negotiated rate — the query says so in its own header. Both joins are pinned to the price row active on each usage_date; drop that predicate and you silently zero out recent usage.

3 · Which jobs cost the most? cost_by_job ranks spend by job. The point isn't the total — it's the ordering: the job at the top is where fixing the cost is worth doing before you touch anything cheaper. distinct_runs tells you whether it's expensive because it runs constantly or because one run is heavy. One job eating a large share is either critical (fine, but know it) or a runaway (not fine).

4 · Is your chargeback tagging real? Everyone claims "100% tagged." The mirage is one umbrella tag stamping every DBU with the same value — full coverage that tells you nothing about who spent what. cost_chargeback_by_tag OUTER-explodes custom_tags so untagged DBUs stay in the denominator, and the tell is a single key with one distinct value absorbing almost everything: 100% tagged, 0% chargeback-ready.

5 · Are you paying premiums you didn't mean to? Serverless, Photon, and tier upgrades all carry rate premiums — often worth it, sometimes landed on by default with nobody choosing it. cost_premium_serverless_photon cuts usage by the premium levers (is_serverless, is_photon, tier) so you can ask, per workload, "did we mean to pay this?" Read each premium SKU against its own classic rows — volumes aren't comparable across SKUs.

Move 3 — Chase the waste, then check the risk.

6 · Your heaviest statements. query_costly_statements ranks finished statements by execution time — a cost proxy, since there's no per-query dollar column and DBUs allocate by execution_duration_ms — each carrying the pruning, spill, and shuffle counters that tell you why it's heavy. Duration compares cleanly only within one warehouse size, so read it as "what's expensive here," then dig into the top offenders. A heavy statement with a terrible pruning ratio is scanning far more data than it needs — a fix, not a fact of life.

7 · Warehouses idling on the clock. A SQL warehouse bills for staying warm after the last query, through its auto-stop delay — small per idle, permanent in aggregate. compute_warehouse_idle_gaps uses LEAD(event_time) over warehouse_events to surface max_running_gap_seconds: the longest continuous RUNNING stretch that never tripped auto-stop. A warehouse idling every evening and weekend is a flat, boring line nobody questions.

8 · Jobs on the expensive clock. The classic SKU arbitrage: a scheduled job on all-purpose (interactive) compute is doing batch work at interactive rates. Same work, wrong SKU. lakeflow_jobs_on_all_purpose finds those placements — often a large, low-risk saving, because the fix is a compute reassignment in the job definition, no code change.

-- trimmed: the cost rollup, the naive est_usd_list_share split and the status band
-- are omitted; the full query (incl. the NOT_ASSESSED row for null compute_ids) is on GitHub.
WITH task_compute AS (
  SELECT workspace_id, job_id, run_id, task_key, EXPLODE(compute_ids) AS compute_id
  FROM system.lakeflow.job_task_run_timeline
  WHERE period_start_time >= dateadd(DAY, -:period_days, current_date())
    AND period_end_time   <  date_trunc('DAY', current_timestamp())
    AND result_state IS NOT NULL
    AND compute_ids IS NOT NULL          -- NULL compute_ids -> a separate NOT_ASSESSED row, never zero
)
SELECT tc.workspace_id, tc.job_id, tc.compute_id,
       c.cluster_source,                 -- 'UI' / 'API' = all-purpose (the anti-pattern); 'JOB' = dedicated
       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           -- change-tracked history: take the latest row per cluster
  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
ORDER BY task_runs DESC;

How to read it. Any row where cluster_source IN ('UI','API') is a scheduled task on interactive compute — a rate-arbitrage candidate. It's a needs-confirmation query, and the reason is written into it: EXPLODE(compute_ids) drops rows where compute_ids is NULL, so the full query surfaces those as a separate NOT_ASSESSED row rather than counting them clean. (A shared all-purpose cluster shows its full DBUs against each job on it — read that caveat before you total the saving.)

9 · Failing jobs burning DBUs. lakeflow_failed_jobs_wasted_dbus: a job that fails after forty minutes already spent the compute a four-minute failure never touched, and the Jobs page shows both with the same red dot. Rank by the money burned; fix the expensive failure first.

-- trimmed for the post: the price CTE, the est_usd_list dollarization and the
-- WARN/CRITICAL status band are omitted here; the full query is on GitHub.
WITH end_rows AS (                    -- result_state + termination_code populate only in a run's END row
  SELECT workspace_id, job_id, run_id, result_state, termination_code, period_start_time
  FROM system.lakeflow.job_run_timeline
  WHERE period_start_time >= dateadd(day, -:period_days, current_date())
    AND period_end_time   <  date_trunc('DAY', current_timestamp())
    AND result_state IS NOT NULL
),
job_runs AS (
  SELECT workspace_id, job_id,
         COUNT(DISTINCT run_id) AS distinct_runs,
         COUNT(DISTINCT CASE WHEN result_state IN ('FAILED','ERROR','TIMED_OUT')
                             THEN run_id END) AS failed_runs
  FROM end_rows GROUP BY workspace_id, job_id
),
last_fail AS (                        -- the LATEST failed run's code, not MAX(termination_code)
  SELECT workspace_id, job_id, termination_code AS last_failed_termination_code
  FROM end_rows
  WHERE result_state IN ('FAILED','ERROR','TIMED_OUT')
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY workspace_id, job_id ORDER BY period_start_time DESC) = 1
),
job_dbus AS (                         -- usage_metadata.job_id joins on (workspace_id, job_id); DBU only
  SELECT u.workspace_id, u.usage_metadata.job_id AS job_id,
         SUM(u.usage_quantity) AS net_usage_quantity
  FROM system.billing.usage u
  WHERE u.usage_date >= dateadd(day, -:period_days, current_date())
    AND u.usage_date <  current_date()
    AND upper(u.usage_unit) = 'DBU'
    AND u.usage_metadata.job_id IS NOT NULL
  GROUP BY u.workspace_id, u.usage_metadata.job_id
)
SELECT r.workspace_id, r.job_id, r.distinct_runs, r.failed_runs,
       lf.last_failed_termination_code,
       COALESCE(d.net_usage_quantity, 0) AS net_dbus,
       -- wasted-DBU PROXY: job DBUs scaled by the share of its runs that failed
       COALESCE(d.net_usage_quantity, 0) * (r.failed_runs / r.distinct_runs) AS wasted_dbus_proxy
FROM job_runs r
LEFT JOIN last_fail lf ON r.workspace_id = lf.workspace_id AND r.job_id = lf.job_id
LEFT JOIN job_dbus   d ON r.workspace_id = d.workspace_id  AND r.job_id = d.job_id
WHERE r.failed_runs > 0
ORDER BY wasted_dbus_proxy DESC, failed_runs DESC;

How to read it. Top row = the job wasting the most DBUs to failure. It's a proxy: usage_metadata carries job_id but not run_id, so DBUs attribute to the whole job, not the individual failed run — a mostly-successful job over-attributes. job_id is unique only within a workspace, so every join keys on (workspace_id, job_id); and last_failed_termination_code is the most recent failure by period_start_time, not a MAX() over codes, which would silently pick the wrong one. termination_code wasn't populated before ~Aug 2024 — read historical NULLs as unknown, never as a finding.

10 · The risk backstop — run-as escalation. Cost is most of the audit, but the one query you don't want to skip catches a job or query running as a higher-privileged identity than the person who set it up. It's rare, it's serious, and no cost dashboard will ever surface it — because ranking it requires a join, and a join is not a button. access_runas_escalation reads system.access.audit for actions where the initiating principal (run_by) differs from the identity it executed as (run_as):

SELECT service_name, action_name,
       -- run_by / run_as are partial-masked in-SQL so no real identity ever leaves the query:
       CASE
         WHEN identity_metadata.run_by IS NULL OR identity_metadata.run_by = '__REDACTED__' THEN identity_metadata.run_by
         WHEN identity_metadata.run_by LIKE '%@%' THEN concat(substr(identity_metadata.run_by, 1, 2), '****@****')
         WHEN identity_metadata.run_by RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN identity_metadata.run_by
         ELSE concat(substr(identity_metadata.run_by, 1, 2), '****')
       END AS run_by,
       CASE
         WHEN identity_metadata.run_as IS NULL OR identity_metadata.run_as = '__REDACTED__' THEN identity_metadata.run_as
         WHEN identity_metadata.run_as LIKE '%@%' THEN concat(substr(identity_metadata.run_as, 1, 2), '****@****')
         WHEN identity_metadata.run_as RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN identity_metadata.run_as
         ELSE concat(substr(identity_metadata.run_as, 1, 2), '****')
       END AS run_as,
       COUNT(*)        AS event_count,
       MIN(event_time) AS first_event_time,
       MAX(event_time) AS last_event_time,
       CASE
         WHEN COUNT(*) >= :crit_runas_events THEN 'CRITICAL'
         WHEN COUNT(*) >= :warn_runas_events THEN 'WARN'
         ELSE 'OK'
       END AS status
FROM system.access.audit
WHERE identity_metadata.run_by IS NOT NULL
  AND identity_metadata.run_as IS NOT NULL
  AND identity_metadata.run_by <> identity_metadata.run_as   -- initiator != executed-as
  AND event_date >= current_date() - INTERVAL :period_days DAYS
  AND event_date <  current_date()
GROUP BY service_name, action_name, identity_metadata.run_by, identity_metadata.run_as
ORDER BY event_count DESC;

How to read it. One row is a masked run_by/run_as pair for a service and action; event_count is how often that pair fired. A low, steady count is often a legitimate job running as a service principal — the WARN / CRITICAL bands (default 5 / 25 events) flag the pairs worth confirming. The identity columns are partial-masked in-SQL, so no real identity ever leaves the query. identity_metadata is commonly NULL for ordinary single-user actions, so treat a zero-row result with suspicion, not relief: verify system.access.audit is populated for your window first. Empty is not assessed, not no escalation. (The table is Public Preview.)

The findings sheet. One page, every row with a receipt behind it.

Ninety minutes in, you should have a single page that reads roughly like the one below. The rows here are illustrative — synthetic shapes, not any real account — but the structure is the point: each finding names the query that produced it, an owner, and a next step, ready to rerun next quarter. That's the difference between an audit and a screenshot.

Every row has a receipt behind it — the exact query that produced it. An audit is repeatable; the person who inherits it inherits the method, not just your conclusions.

What this is not. The blank cells are half the honesty.

It won't give you your invoice — the dollars are list-price estimates, honest about their own limits, and there's no account_prices table on this account to price the negotiated side. It won't cover what your account doesn't expose: system.access.audit (run-as) is Public Preview, several schemas are admin-gated, and an un-enabled table throws TABLE_OR_VIEW_NOT_FOUND — a coverage gap to record, not a pass.

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

Two of the ten are labelled needs-confirmation proxies — wasted DBUs (no per-run DBU column) and all-purpose placement (EXPLODE drops NULL compute) — and they carry that label themselves. termination_code wasn't populated before ~Aug 2024, so historical NULLs read as unknown, never as a finding. And this is the opening ninety minutes, not the whole story: behind these ten sit eighty-six more, for when a finding turns into an investigation. Sample outputs in the repo are illustrative synthetic rows — the queries mask identities in-SQL precisely so nothing real ever leaves your metastore.

Getting started.

Open crosshire-audit-databricks-admin, run any .sql in a Databricks SQL editor as a principal holding SELECT on system.*, and set the look-back on :period_days. Then work the three moves in order — trust the numbers, chase the money, check the risk. Start with the trust check — can you even trust the bill? — and let the receipts do the rest.

When a finding turns into an investigation, the full ninety-six-query library has the follow-ups — each first-audit query names its own next: queries in its header. And the same ten are walked query by query on Crosshire Learn if you want the long-form commentary.