Your Databricks jobs fail on the expensive clock.

Reliability is a cost line. The console shows you what ran — not what it cost you when it failed.

Databricks jobs fail on the expensive clock. A job that fails after forty minutes on a job cluster already spent the DBUs a job that fails after four minutes never touched — and the Jobs & Pipelines page shows you both runs with the same red dot. This post is the questions the run-history view can't answer on its own: which failing jobs waste the most compute, what's paying the all-purpose premium for batch work, which green runs hide a quietly failed task, and which jobs have no timeout or haven't run in a month. Each gets one plain SELECT over Unity Catalog system.* tables.

Companion: the full query library — ninety-six plain SELECTs across seven domains, open on GitHub.

Run history tells you what happened. It never tells you which failure was the expensive one.

"Which failing jobs waste the most compute?" Join run history to billing, and the failure backlog turns into a DBU-ranked list.

system.lakeflow.job_run_timeline tells you a run failed. It doesn't tell you whether failing cost you thirty seconds or thirty minutes of a job-cluster warm-up, three retries, and a chunk of an all-purpose cluster's compute along the way. The fix is a join: pull the failed-run count per job from job_run_timeline, the DBUs actually billed to that job from system.billing.usage (via usage_metadata.job_id), and rank by a wasted-DBU proxy — not by failure count alone, which flatters every job that fails often but cheaply.

WITH end_rows AS (
  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, -30, 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,
         MAX_BY(termination_code, period_start_time) AS last_termination_code
  FROM end_rows
  GROUP BY workspace_id, job_id
),
job_dbus AS (
  SELECT workspace_id, usage_metadata.job_id AS job_id,
         SUM(usage_quantity) AS net_job_dbus
  FROM system.billing.usage
  WHERE usage_date >= dateadd(day, -30, current_date())
    AND usage_date <  current_date()
    AND upper(usage_unit) = 'DBU'
    AND usage_metadata.job_id IS NOT NULL
  GROUP BY workspace_id, usage_metadata.job_id
)
SELECT r.workspace_id, r.job_id, r.distinct_runs, r.failed_runs,
       r.last_termination_code,
       COALESCE(d.net_job_dbus, 0) AS net_job_dbus,
       COALESCE(d.net_job_dbus, 0) * (r.failed_runs / r.distinct_runs) AS wasted_dbus_proxy
FROM job_runs r
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;

Four things this proxy can't see:

• usage_metadata.job_id is unique only within a workspace, so the billing join runs on (workspace_id, job_id), never job_id alone — collapse that on a multi-workspace metastore and you fold two unrelated jobs' spend together.
• usage_metadata.job_id is also only populated for runs on job compute or serverless compute — a job parked on an interactive all-purpose cluster (exactly the pattern the next question hunts for) bills under usage_metadata.cluster_id instead, so it can show net_job_dbus of zero here while it's still burning real money.
• This proxy sums at the job level, not the run level — usage_metadata does carry a job_run_id, but wasted_dbus_proxy deliberately aggregates a job's whole 30-day spend against its overall failure rate, so a job that mostly succeeds and fails once a month will over-state its own waste. That's disclosed, not hidden: the column is named _proxy for a reason.
• It's MAX_BY, not ARGMAX — Databricks SQL has no ARGMAX; MAX_BY(termination_code, period_start_time) is the value at the latest period_start_time, and a plain MAX(termination_code) would return whichever code sorts alphabetically last, which is not the same thing.

Read it like this: one row per job, wasted_dbus_proxy descending — the job at the top is the one where fixing the failure is worth doing before fixing anything cheaper.

"What's paying the all-purpose premium for batch work?" Same job, same code — the wrong compute SKU is a silent line item.

A scheduled job running on an interactive all-purpose cluster instead of a job cluster is doing identical work at a materially higher DBU rate — no different query plan, no different data, just a compute reassignment somebody made once and never revisited. system.lakeflow.job_task_run_timeline records which compute_ids ran each task; system.compute.clusters records what kind of cluster each ID actually is. Join them and the SKU mismatch names itself.

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 >= 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
ORDER BY task_runs DESC;

cluster_source IN ('UI','API') on a scheduled task is the tell — that compute was created outside the job's own cluster spec, which is almost always all-purpose. Three things keep this a needs-confirmation finding rather than a confirmed one: EXPLODE(compute_ids) silently drops rows where compute_ids is NULL, so anything scheduled before that column was populated belongs in a separate not-assessed bucket, not an implicit zero; system.compute.clusters only carries all-purpose, jobs, and pipeline compute — it has no rows for serverless job compute or SQL warehouses, so a task that ran on serverless job compute joins to nothing and reads as cluster_source = NULL, not as confirmed-correct; and the table is change-tracked, reduced here to the newest row per cluster_id with a plain ROW_NUMBER — confirm change_time is actually monotonic on your account before trusting the latest row. Read it like this: one row per job/compute pairing, task_runs descending — every row with cluster_source = UI or API is the same finding repeated: same work, wrong SKU.

"Which 'successful' runs hide failed tasks?" A job can report SUCCEEDED while one task inside it quietly FAILED.

Databricks jobs support tasks configured to continue on failure — useful for a task that's genuinely optional, silent for one that isn't. The job-level result_state and the task-level result_state live in two different tables, and nothing forces you to look at both. system.lakeflow.job_run_timeline says the run SUCCEEDED; system.lakeflow.job_task_run_timeline, joined on job_run_id, says a task inside it did not.

WITH job_end AS (
  SELECT workspace_id, job_id, run_id AS job_run_id, result_state AS job_result_state
  FROM system.lakeflow.job_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
),
task_end AS (
  SELECT workspace_id, job_id, job_run_id, task_key,
         result_state AS task_result_state
  FROM system.lakeflow.job_task_run_timeline
  WHERE period_start_time >= date_add(current_date(), -30)
    AND result_state IS NOT NULL
)
SELECT j.workspace_id, j.job_id,
       COUNT(DISTINCT j.job_run_id) AS succeeded_runs,
       COUNT(DISTINCT CASE WHEN t.task_result_state IN ('FAILED','ERROR','TIMED_OUT')
                           THEN j.job_run_id END)   AS succeeded_runs_with_failed_task
FROM job_end j
LEFT JOIN task_end t
  ON j.workspace_id = t.workspace_id
 AND j.job_id       = t.job_id
 AND j.job_run_id   = t.job_run_id
WHERE j.job_result_state = 'SUCCEEDED'
GROUP BY j.workspace_id, j.job_id;

The join key is documented but easy to miss: job_task_run_timeline.job_run_id equals job_run_timeline.run_id, not a shared task_run_id — get that wrong and every row silently fails to match, which reads as zero failed tasks everywhere, the worst kind of false negative. Both tables populate result_state only on the end row, hence the filter on both sides. This is one of the domain's confirmed checks, not a needs-confirmation one: any succeeded_runs_with_failed_task greater than zero is exactly what it says.

Green dashboard. Dropped work. No alert fired — that's not a corner case, it's the default.

"Which jobs have no timeout, or are zombies?" Two different failure modes, one table, one query.

A job with no configured timeout can run indefinitely if it hangs instead of failing cleanly — nothing kills it, so it holds a cluster until someone notices. A job nobody has run in a month is the opposite failure: it's still active in the scheduler, still eligible to fire, and answers to nobody. system.lakeflow.jobs is change-tracked, so the current definition of every job is the latest row per (workspace_id, job_id); joined to the last row it ever produced in job_run_timeline, both symptoms fall out of the same query.

WITH latest_jobs AS (
  SELECT workspace_id, job_id, name, timeout_seconds, delete_time
  FROM system.lakeflow.jobs
  QUALIFY ROW_NUMBER() OVER (PARTITION BY workspace_id, job_id ORDER BY change_time DESC) = 1
),
last_run AS (
  SELECT workspace_id, job_id, MAX(period_start_time) AS last_run_start
  FROM system.lakeflow.job_run_timeline
  GROUP BY workspace_id, job_id
)
SELECT
  j.workspace_id, j.job_id, j.name,
  j.timeout_seconds,
  CASE WHEN j.timeout_seconds IS NULL OR j.timeout_seconds = 0
       THEN true ELSE false END                       AS no_timeout,
  r.last_run_start,
  CASE WHEN r.last_run_start IS NULL
         OR r.last_run_start < date_add(current_date(), -30)
       THEN true ELSE false END                        AS stale_30d
FROM latest_jobs j
LEFT JOIN last_run r
  ON j.workspace_id = r.workspace_id AND j.job_id = r.job_id
WHERE j.delete_time IS NULL
  AND ( j.timeout_seconds IS NULL OR j.timeout_seconds = 0
        OR r.last_run_start IS NULL
        OR r.last_run_start < date_add(current_date(), -30) )
ORDER BY stale_30d DESC, no_timeout DESC;

One column here needs its own caveat: timeout_seconds was not populated before early December 2025, so a NULL is ambiguous on an older account — it can mean no timeout was ever set, or it can mean the column hadn't started recording yet. Treat NULL as not assessed on any job whose history predates that rollout, and reserve no_timeout = true as a real finding only once you've confirmed the column is live on your account. last_run_start IS NULL is its own edge case too — a job that has never run once isn't stale, it's unused; both belong in the same backlog but for different reasons. Read it like this: a short list, delete_time IS NULL only — every job that can either hang forever or hasn't been touched in thirty days, in one place.

The receipt. Four questions, four SELECTs, and reliability stops being a guess.

None of these four needed a dashboard. They needed system.lakeflow.jobs, system.lakeflow.job_run_timeline, system.lakeflow.job_task_run_timeline, a join to system.compute.clusters, and a join to system.billing.usage — the same tables the Jobs & Pipelines domain's other seventeen queries build on, covering queueing, cold starts, retries, pipeline cost, and ownership. Run these four first; they're the ones that come up in nearly every reliability conversation, because they answer questions run history alone can't: which failure is expensive, which SKU is wrong, which green run lied, and which job is still armed with nobody watching it.

The full domain — all 21 Jobs & Pipelines queries, plus the other six domains — lives in the query library, open on GitHub. Three companion posts from the same pass through the account: governance blind spots, what your tables actually weigh, and idle serving endpoints. And the full worked reference sits at learn.crosshire.ch's Databricks audit chapter.

Every query reads Databricks Unity Catalog system.* tables via plain SELECT; figures are DBU-only, directional, answer shapes rather than billed numbers. Empty is treated as not assessed. — Crosshire