- Job Started: job_start_time
- Waiting for Cluster: job_start_time -> first_task_start
- Cluster Ready: first_task_start timestamp
- Execution: first_task_start -> last_task_end
- Result: result_state + job_end_time
# Job Lifecycle Timeline Analysis
# Purpose: Derive job lifecycle phases by comparing job-level and task-level timing
# Dependencies: system.lakeflow.job_run_timeline, system.lakeflow.job_task_run_timeline, system.lakeflow.jobs
# Assumptions: Unity Catalog enabled, user has SELECT permissions on system tables
job_lifecycle_df = spark.sql("""
------------------------------------------------------------------------
-- CTE: job_runs
-- Source: system.lakeflow.job_run_timeline
-- Purpose: Extract the top-level job run records with start/end times
--
-- This table contains one row per job run with the overall run timing.
-- It does NOT break down into phases (waiting, running, etc.) - that's
-- why we need to join with task-level data to derive those phases.
--
-- Filtering to last 7 days for performance - adjust as needed.
------------------------------------------------------------------------
WITH job_runs AS (
SELECT
job_id,
run_id,
run_name,
trigger_type,
run_type,
result_state,
termination_code,
period_start_time AS job_start_time,
period_end_time AS job_end_time
FROM system.lakeflow.job_run_timeline
WHERE period_end_time >= CURRENT_DATE - INTERVAL 7 DAYS
),
------------------------------------------------------------------------
-- CTE: task_timing
-- Source: system.lakeflow.job_task_run_timeline
-- Purpose: Aggregate task-level timing to determine when actual work started/ended
--
-- The key insight: the GAP between job_start_time and the first task starting
-- represents "waiting for cluster" time. Tasks can't start until compute is ready.
--
-- We use MIN(period_start_time) to find when the first task began (cluster ready)
-- and MAX(period_end_time) to find when all tasks completed.
--
-- Join key: job_run_id in this table maps to run_id in job_run_timeline.
-- (Note: run_id in this table is the task's own run ID, not the parent job run)
------------------------------------------------------------------------
task_timing AS (
SELECT
job_id,
job_run_id,
MIN(period_start_time) AS first_task_start,
MAX(period_end_time) AS last_task_end,
COUNT(DISTINCT task_key) AS task_count
FROM system.lakeflow.job_task_run_timeline
WHERE period_end_time >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY job_id, job_run_id
),
------------------------------------------------------------------------
-- CTE: latest_jobs
-- Source: system.lakeflow.jobs
-- Purpose: Get the current job name for each job_id
--
-- The jobs table is versioned - every time a job config changes, a new row
-- is added with an updated change_time. Without deduplication, joining
-- directly causes row multiplication (one output row per job version).
--
-- ROW_NUMBER with PARTITION BY job_id ORDER BY change_time DESC assigns
-- rn=1 to the most recent version. We filter to rn=1 in the join.
------------------------------------------------------------------------
latest_jobs AS (
SELECT
job_id,
name,
ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY change_time DESC) AS rn
FROM system.lakeflow.jobs
)
------------------------------------------------------------------------
-- Main SELECT
-- Purpose: Join the three CTEs to produce the job lifecycle view
--
-- Lifecycle phases derived:
-- 1. Job Started: job_start_time (from job_run_timeline)
-- 2. Waiting for Cluster: job_start_time -> first_task_start
-- 3. Cluster Ready: first_task_start timestamp
-- 4. Execution: first_task_start -> last_task_end
-- 5. Result: result_state + job_end_time
--
-- Additional derived metrics:
-- - cleanup_duration: time between last task completing and job officially ending
-- - total_duration: end-to-end job time
--
-- LEFT JOINs used because:
-- - Some jobs may fail before any tasks start (no task_timing records)
-- - Some jobs may have been deleted (no latest_jobs record)
--
-- Filtered to run_type = 'JOB_RUN' to exclude SUBMIT_RUN and WORKFLOW_RUN
-- which have different semantics.
------------------------------------------------------------------------
SELECT
j.job_id,
j.run_id,
jobs.name AS job_name,
j.trigger_type,
j.run_type,
-- Stage 1: Job Started
j.job_start_time,
-- Stage 2: Result
j.job_end_time,
j.result_state,
j.termination_code,
-- Stage 3-4: Waiting for Cluster (gap between job start and first task start)
t.first_task_start AS cluster_ready_time,
ROUND(TIMESTAMPDIFF(MILLISECOND, j.job_start_time, t.first_task_start) / 1000.0, 3) AS waiting_for_cluster_sec,
-- Stage 5: Running (first task start to last task end)
t.last_task_end AS execution_end_time,
ROUND(TIMESTAMPDIFF(MILLISECOND, t.first_task_start, t.last_task_end) / 1000.0, 3) AS execution_duration_sec,
-- Cleanup time (last task end to job end)
ROUND(TIMESTAMPDIFF(MILLISECOND, t.last_task_end, j.job_end_time) / 1000.0, 3) AS cleanup_duration_sec,
-- Total duration
ROUND(TIMESTAMPDIFF(MILLISECOND, j.job_start_time, j.job_end_time) / 1000.0, 3) AS total_duration_sec,
-- Task count for context
t.task_count
FROM job_runs j
LEFT JOIN task_timing t
ON j.job_id = t.job_id AND j.run_id = t.job_run_id
LEFT JOIN latest_jobs jobs
ON j.job_id = jobs.job_id AND jobs.rn = 1
WHERE j.run_type = 'JOB_RUN'
ORDER BY j.job_start_time DESC
""")
display(job_lifecycle_df)
*Note: In accordance with our community Generative AI policy, I did personally verify the results in a Databricks workspace.
