Databricks job reliability from system tables: SQL cheat sheet for failure rates, flaky tasks, duration regressions and risky job settings

Run history in the Jobs UI covers one job at a time. system.lakeflow covers every job in every workspace attached to the metastore's region, for up to a year. These queries answer the platform-level questions: which jobs fail most, which tasks only pass on retry, what got slower, and which jobs are one bad day away from an incident.

#Know the table semantics first

Most wrong answers from these tables come from three mistakes:

Access: you need USE and SELECT on system.lakeflow. Metastore admins grant it.

GRANT USE SCHEMA ON SCHEMA system.lakeflow TO `platform-oncall`;
GRANT SELECT ON SCHEMA system.lakeflow TO `platform-oncall`;

#Reusable views

Create these once in an ops schema. Every query below builds on them.

CREATE OR REPLACE VIEW ops.monitor.jobs_current AS
SELECT * FROM system.lakeflow.jobs
QUALIFY row_number() OVER (PARTITION BY workspace_id, job_id ORDER BY change_time DESC) = 1
  AND delete_time IS NULL;

CREATE OR REPLACE VIEW ops.monitor.job_runs AS
SELECT workspace_id, job_id, run_id,
       min(period_start_time)                       AS start_time,
       max(period_end_time)                         AS end_time,
       max_by(result_state, period_end_time)        AS result_state,
       max_by(termination_code, period_end_time)    AS termination_code,
       any_value(trigger_type)                      AS trigger_type,
       timestampdiff(SECOND, min(period_start_time), max(period_end_time)) AS duration_s
FROM system.lakeflow.job_run_timeline
WHERE period_start_time >= current_date() - INTERVAL 90 DAYS
GROUP BY ALL;

CREATE OR REPLACE VIEW ops.monitor.task_runs AS
SELECT workspace_id, job_id, job_run_id, run_id AS task_run_id, task_key,
       min(period_start_time)                    AS start_time,
       max(period_end_time)                      AS end_time,
       max_by(result_state, period_end_time)     AS result_state,
       max_by(termination_code, period_end_time) AS termination_code,
       timestampdiff(SECOND, min(period_start_time), max(period_end_time)) AS duration_s
FROM system.lakeflow.job_task_run_timeline
WHERE period_start_time >= current_date() - INTERVAL 90 DAYS
GROUP BY ALL;

#Failure rates

#Worst jobs in the last 7 days

SELECT r.workspace_id, r.job_id, j.name,
       count(*)                                             AS runs,
       count_if(r.result_state IN ('FAILED','TIMED_OUT','ERROR')) AS failed,
       round(100 * failed / runs, 1)                        AS fail_pct
FROM ops.monitor.job_runs r
LEFT JOIN ops.monitor.jobs_current j USING (workspace_id, job_id)
WHERE r.start_time >= now() - INTERVAL 7 DAYS AND r.result_state IS NOT NULL
GROUP BY ALL
HAVING failed > 0
ORDER BY failed DESC
LIMIT 25;

#What kind of failure: termination codes

SELECT termination_code, count(*) AS runs, count(DISTINCT job_id) AS jobs
FROM ops.monitor.job_runs
WHERE start_time >= now() - INTERVAL 7 DAYS
  AND result_state NOT IN ('SUCCEEDED','SKIPPED')
GROUP BY ALL
ORDER BY runs DESC;

Read the result like this. CLUSTER_ERROR, CLOUD_FAILURE and INVALID_CLUSTER_REQUEST point to compute: quota, subnets or policy. DRIVER_ERROR usually means memory. REPOSITORY_CHECKOUT_FAILED is Git credentials or networking. MAX_JOB_QUEUE_SIZE_EXCEEDED means a concurrency backlog. The code list grows over time, so check the jobs system table reference for your release.

#Failures that cluster in time (shared cause)

SELECT date_trunc('HOUR', end_time) AS hour, count(DISTINCT job_id) AS failing_jobs,
       collect_set(termination_code) AS codes
FROM ops.monitor.job_runs
WHERE end_time >= now() - INTERVAL 3 DAYS AND result_state = 'FAILED'
GROUP BY ALL
HAVING failing_jobs >= 5
ORDER BY hour DESC;

When many jobs fail in the same hour, look for a platform change: a policy edit, a revoked grant, a storage firewall change. Don't debug each job.

#Flaky tasks and retries

A task with retries enabled produces several task runs for the same job_run_id and task_key. Tasks that fail and then succeed in the same job run hide problems and burn compute:

SELECT t.workspace_id, t.job_id, j.name, t.task_key,
       count(DISTINCT t.job_run_id)                    AS job_runs_with_retry,
       sum(attempts - 1)                               AS extra_attempts
FROM (
  SELECT workspace_id, job_id, job_run_id, task_key,
         count(*) AS attempts,
         count_if(result_state = 'SUCCEEDED') AS ok
  FROM ops.monitor.task_runs
  WHERE start_time >= now() - INTERVAL 30 DAYS
  GROUP BY ALL
  HAVING attempts > 1 AND ok = 1
) t
LEFT JOIN ops.monitor.jobs_current j USING (workspace_id, job_id)
GROUP BY ALL
ORDER BY extra_attempts DESC
LIMIT 25;

Tip

Treat any task in this list with extra_attempts above roughly 10% of its runs as a defect. Retries are for transient faults, not for a race condition or memory pressure that hits every third run.

#Duration regressions

Compare p50 and p95 this week with the previous three weeks, per task:

WITH d AS (
  SELECT job_id, task_key, duration_s,
         start_time >= now() - INTERVAL 7 DAYS AS this_week
  FROM ops.monitor.task_runs
  WHERE result_state = 'SUCCEEDED' AND start_time >= now() - INTERVAL 28 DAYS)
SELECT job_id, task_key,
       percentile_approx(duration_s, 0.5)  FILTER (WHERE NOT this_week) AS p50_before,
       percentile_approx(duration_s, 0.5)  FILTER (WHERE this_week)     AS p50_now,
       percentile_approx(duration_s, 0.95) FILTER (WHERE this_week)     AS p95_now,
       round(p50_now / p50_before, 2)                                   AS ratio
FROM d
GROUP BY ALL
HAVING p50_before > 60 AND ratio >= 1.5
ORDER BY ratio DESC;

The p50_before > 60 filter drops tiny tasks whose ratios are noise. A regression across many unrelated tasks on the same day usually means a runtime upgrade, a policy change to smaller VMs, or growth in a shared input table.

#Runs still going much longer than normal

WITH open_runs AS (
  SELECT workspace_id, job_id, run_id, min(period_start_time) AS started
  FROM system.lakeflow.job_run_timeline
  WHERE period_start_time >= now() - INTERVAL 2 DAYS
  GROUP BY ALL
  HAVING count_if(result_state IS NOT NULL) = 0),
typical AS (
  SELECT job_id, percentile_approx(duration_s, 0.95) AS p95_s
  FROM ops.monitor.job_runs WHERE result_state = 'SUCCEEDED' GROUP BY job_id)
SELECT o.*, timestampdiff(SECOND, o.started, now()) AS running_s, t.p95_s
FROM open_runs o JOIN typical t USING (job_id)
WHERE timestampdiff(SECOND, o.started, now()) > 2 * t.p95_s;

System tables lag real time, typically by minutes. Use this for a sweep every 15-30 minutes, not as your only hang detector.

#Risky job settings

#No timeout, no health rule, or still owned by a person

SELECT workspace_id, job_id, name, creator_user_name, run_as_user_name,
       timeout_seconds, size(health_rules) AS health_rules, paused
FROM ops.monitor.jobs_current
WHERE NOT paused
  AND (coalesce(timeout_seconds, 0) = 0
       OR coalesce(size(health_rules), 0) = 0
       OR run_as_user_name LIKE '%@%');

run_as_user_name with an @ means a human identity. That job breaks when the person leaves. Health rules are what drive duration-threshold alerts.

#Active jobs that haven't succeeded in 14 days

SELECT j.workspace_id, j.job_id, j.name, max(r.end_time) AS last_success
FROM ops.monitor.jobs_current j
LEFT JOIN ops.monitor.job_runs r
  ON r.workspace_id = j.workspace_id AND r.job_id = j.job_id AND r.result_state = 'SUCCEEDED'
WHERE NOT j.paused AND j.trigger_type IS NOT NULL
GROUP BY ALL
HAVING last_success IS NULL OR last_success < now() - INTERVAL 14 DAYS;

#Operating notes

• Run these on a small serverless SQL warehouse and schedule the alerting ones as Databricks SQL alerts.
• Records are regional. A workspace in another region needs its own metastore's system tables.
• Columns get added over time (termination_type, compute, per-phase durations). Run DESCRIBE TABLE system.lakeflow.job_run_timeline on your release before you build dashboards on new fields.