Jobs system table reference

Article
08/29/2024

Important

This system table is in Public Preview. To access the table, the schema must be enabled in your system catalog. For more information, see Enable system table schemas.

Note

The lakeflow schema was previously known as workflow. The content of both schemas is identical. To make the lakeflow schema visible, you must enable it separately.

This article provides a reference for how to use the lakeflow system tables to monitor jobs in your account. These tables include records from all workspaces in your account deployed in the same cloud region. To see records from another region, you need to view the tables from a workspace deployed in that region.

system.lakeflow must be enabled by an account admin. You can enable it using the SystemSchemas API.

For examples of using these tables for job cost and health observability, see Monitor job costs with system tables.

Available job tables

All jobs-related system tables live in the system.lakeflow schema. Currently, the schema hosts four tables:

jobs: Tracks creation, deletion, and basic information of jobs.
job_tasks: Tracks creation, deletion, and basic information of job tasks.
job_run_timeline: Records the start, end, and resulting state of job runs.
job_task_run_timeline: Records the start, end, and resulting state of job tasks.

Job table schema

The jobs table is a slowly changing dimension table. When a row changes, a new row is emitted, logically replacing the previous one.

The table uses the following schema:

Column name	Data type	Description
`account_id`	string	The ID of the account this job belongs to.
`workspace_id`	string	The ID of the workspace this job belongs to.
`job_id`	string	The ID of the job. This key is only unique within a single workspace.
`name`	string	The user-supplied name of the job.
`description`	string	The user-supplied description of the job. Not populated for rows emitted before late August 2024.
`creator_id`	string	The ID of the principal who created the job.
`tags`	string	The user-supplied custom tags associated with this job.
`change_time`	timestamp	The time when the job was last modified. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`delete_time`	timestamp	The time when the job was deleted by the user. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`run_as`	string	The ID of the user or service principal whose permissions are used for the job run.

Job task table schema

The job_tasks table is a slowly changing dimension table. When a row changes, a new row is emitted, logically replacing the previous one.

The table uses the following schema:

Column name	Data type	Description
`account_id`	string	The ID of the account this job belongs to.
`workspace_id`	string	The ID of the workspace this job belongs to.
`job_id`	string	The ID of the job. This key is only unique within a single workspace.
`task_key`	string	The reference key for a task in a job. This key is only unique within a single job.
`depends_on_keys`	array	The task keys of all upstream dependencies of this task.
`change_time`	timestamp	The time when the task was last modified. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`delete_time`	timestamp	The time when a task was deleted by the user. Timezone information is recorded at the end of the value with `+00:00` representing UTC.

Job run timeline table schema

The job_run_timeline table is immutable and complete at the time it is produced.

The table uses the following schema:

Column name	Data type	Description
`account_id`	string	The ID of the account this job belongs to.
`workspace_id`	string	The ID of the workspace this job belongs to.
`job_id`	string	The ID of the job. This key is only unique within a single workspace.
`run_id`	string	The ID of the job run.
`period_start_time`	timestamp	The start time for the run or for the time period. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`period_end_time`	timestamp	The end time for the run or for the time period. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`trigger_type`	string	The type of trigger that can fire a run. For possible values, see Trigger type values
`run_type`	string	The type of job run. For possible values, see Run type values.
`run_name`	string	The user-supplied run name assosiated with this job run.
`compute_ids`	array	Array containing the IDs of the non-serverless jobs compute and non-serverless SQL warehouses used by the parent job run. For task-specific compute information, refer to the `job_task_run_timeline` table. Not populated for rows emitted before late August 2024.
`result_state`	string	The outcome of the job run. For possible values, see Result state values.
`termination_code`	string	The termination code of the job run. For possible values, see Termination code values. Not populated for rows emitted before late August 2024.
`job_parameters`	map	The job-level parameters used in the job run. Not populated for rows emitted before late August 2024.

Trigger type values

The possible values for the trigger_type column are:

CONTINUOUS
CRON
FILE_ARRIVAL
ONETIME
ONETIME_RETRY

Run type values

The possible values for the run_type column are:

JOB_RUN
SUBMIT_RUN: One-time run created via POST /api/2.1/jobs/runs/submit.
WORKFLOW_RUN: Job run initiated from the notebook workflow.

Result state values

The possible values for the result_state column are:

SUCCEEDED
FAILED
SKIPPED
CANCELLED
TIMED_OUT
ERROR
BLOCKED

Termination code values

The possible values for the termination_code column are:

SUCCESS
CANCELLED
SKIPPED
DRIVER_ERROR
CLUSTER_ERROR
REPOSITORY_CHECKOUT_FAILED
INVALID_CLUSTER_REQUEST
WORKSPACE_RUN_LIMIT_EXCEEDED
FEATURE_DISABLED
CLUSTER_REQUEST_LIMIT_EXCEEDED
STORAGE_ACCESS_ERROR
RUN_EXECUTION_ERROR
UNAUTHORIZED_ERROR
LIBRARY_INSTALLATION_ERROR
MAX_CONCURRENT_RUNS_EXCEEDED
MAX_SPARK_CONTEXTS_EXCEEDED
RESOURCE_NOT_FOUND
INVALID_RUN_CONFIGURATION
CLOUD_FAILURE
MAX_JOB_QUEUE_SIZE_EXCEEDED

Job task run timeline table schema

The job_task_run_timeline table is immutable and complete at the time it is produced.

The table uses the following schema:

Column name	Data type	Description
`account_id`	string	The ID of the account this job belongs to.
`workspace_id`	string	The ID of the workspace this job belongs to.
`job_id`	string	The ID of the job. This key is only unique within a single workspace.
`run_id`	string	The ID of the task run.
`job_run_id`	string	The ID of the job run. Not populated for rows emitted before late August 2024.
`parent_run_id`	string	The ID of the parent run. Not populated for rows emitted before late August 2024.
`period_start_time`	timestamp	The start time for the task or for the time period. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`period_end_time`	timestamp	The end time for the task or for the time period. Timezone information is recorded at the end of the value with `+00:00` representing UTC.
`task_key`	string	The reference key for a task in a job. This key is only unique within a single job.
`compute_ids`	array	Array containing the IDs of the non-serverless jobs compute and non-serverless SQL warehouses used by the job task.
`result_state`	string	The outcome of the job task run.
`termination_code`	string	The termination code of the task run. See the possible values below this table. Not populated for rows emitted before late August 2024.

The possible values for the result_state column are:

SUCCEEDED
FAILED
SKIPPED
CANCELLED
TIMED_OUT
ERROR
BLOCKED

The possible values for the termination_code column are:

SUCCESS
CANCELLED
SKIPPED
DRIVER_ERROR
CLUSTER_ERROR
REPOSITORY_CHECKOUT_FAILED
INVALID_CLUSTER_REQUEST
WORKSPACE_RUN_LIMIT_EXCEEDED
FEATURE_DISABLED
CLUSTER_REQUEST_LIMIT_EXCEEDED
STORAGE_ACCESS_ERROR
RUN_EXECUTION_ERROR
UNAUTHORIZED_ERROR
LIBRARY_INSTALLATION_ERROR
MAX_CONCURRENT_RUNS_EXCEEDED
MAX_SPARK_CONTEXTS_EXCEEDED
RESOURCE_NOT_FOUND
INVALID_RUN_CONFIGURATION
CLOUD_FAILURE
MAX_JOB_QUEUE_SIZE_EXCEEDED

Example queries

This section includes sample queries you can use to get the most out of the lakeflow tables.

Get the most recent version of jobs
Daily job count by workspace
Daily job status distribution by workspace
Longest running jobs overview
Job run time for jobs executed via runSubmit (ie. Airflow)
Job run analysis
Jobs running on all-purpose compute
Retried job runs

Get the most recent version of jobs

Because the jobs and job_tasks tables are slowly changing dimension tables, a new record is created every time a change is made. To get the most recent version of a job, you can order by the change_time column.

SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
FROM
  system.lakeflow.jobs QUALIFY rn=1

Daily job count by workspace

This query gets the daily job count by workspace for the last 7 days:

SELECT
  workspace_id,
  COUNT(DISTINCT run_id) as job_count,
  to_date(period_start_time) as date
FROM system.lakeflow.job_run_timeline
WHERE
  period_start_time > CURRENT_TIMESTAMP() - INTERVAL 7 DAYS
GROUP BY ALL

Daily job status distribution by workspace

This query returns the daily job count by workspace for the last 7 days, distributed by the outcome of the job run. The query removes any records where the jobs are in a pending or running state.

SELECT
  workspace_id,
  COUNT(DISTINCT run_id) as job_count,
  result_state,
  to_date(period_start_time) as date
FROM system.lakeflow.job_run_timeline
WHERE
  period_start_time > CURRENT_TIMESTAMP() - INTERVAL 7 DAYS
  AND result_state IS NOT NULL
GROUP BY ALL

Longest running jobs overview

This query returns the average time of job runs, measured in seconds. The records are organized by job. A top 90 and a 95 percentile column show the average lengths of the job’s longest runs.

with job_run_duration as (
    SELECT
        workspace_id,
        job_id,
        run_id,
        CAST(SUM(period_end_time - period_start_time) AS LONG) as duration
    FROM
        system.lakeflow.job_run_timeline
    WHERE
      period_start_time > CURRENT_TIMESTAMP() - INTERVAL 7 DAYS
    GROUP BY ALL
),
most_recent_jobs as (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
  FROM
    system.lakeflow.jobs QUALIFY rn=1
)
SELECT
    t1.workspace_id,
    t1.job_id,
    first(t2.name, TRUE) as name,
    COUNT(DISTINCT t1.run_id) as runs,
    MEAN(t1.duration) as mean_seconds,
    AVG(t1.duration) as avg_seconds,
    PERCENTILE(t1.duration, 0.9) as p90_seconds,
    PERCENTILE(t1.duration, 0.95) as p95_seconds
FROM
    job_run_duration t1
    LEFT OUTER JOIN most_recent_jobs t2 USING (workspace_id, job_id)
GROUP BY ALL
ORDER BY mean_seconds DESC
LIMIT 100

Job run time for jobs executed via runSubmit (ie. Airflow)

This query provides a historical runtime for a specific job based on the run_name parameter. For the query to work, you must set the run_name.

You can also edit the length of time for analysis by updating the number of days in the INTERVAL 60 DAYS section.

SELECT
  workspace_id,
  run_id,
  SUM(period_end_time - period_start_time) as run_time
FROM system.lakeflow.job_run_timeline
WHERE
  run_type="SUBMIT_RUN"
  AND run_name={run_name}
  AND period_start_time > CURRENT_TIMESTAMP() - INTERVAL 60 DAYS
GROUP BY ALL

Job run analysis

This query provides a historical runtime for a specific job. For the query to work, you must set a workspace_id and job_id.

You can also edit the length of time for analysis by updating the number of days in the INTERVAL 60 DAYS section.

with job_run_duration as (
    SELECT
        workspace_id,
        job_id,
        run_id,
        min(period_start_time) as run_start,
        max(period_start_time) as run_end,
        CAST(SUM(period_end_time - period_start_time) AS LONG) as duration,
        FIRST(result_state, TRUE) as result_state
    FROM
        system.lakeflow.job_run_timeline
    WHERE
      period_start_time > CURRENT_TIMESTAMP() - INTERVAL 60 DAYS
      AND workspace_id={workspace_id}
      AND job_id={job_id}
    GROUP BY ALL
    ORDER BY run_start DESC
),
most_recent_jobs as (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
  FROM
    system.lakeflow.jobs QUALIFY rn=1
)
SELECT
    t1.workspace_id,
    t1.job_id,
    t2.name,
    t1.run_id,
    t1.run_start,
    t1.run_end,
    t1.duration,
    t1.result_state
FROM job_run_duration t1
    LEFT OUTER JOIN most_recent_jobs t2 USING (workspace_id, job_id)

Jobs running on all-purpose compute

This query joins with the compute.clusters system table to return recent jobs that are running on all-purpose compute instead of jobs compute.

with clusters AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY workspace_id, cluster_id ORDER BY change_time DESC) as rn
  FROM system.compute.clusters
  WHERE cluster_source="UI" OR cluster_source="API"
  QUALIFY rn=1
),
job_tasks_exploded AS (
  SELECT
    workspace_id,
    job_id,
    EXPLODE(compute_ids) as cluster_id
  FROM system.lakeflow.job_task_run_timeline
  WHERE period_start_time >= CURRENT_DATE() - INTERVAL 30 DAY
),
all_purpose_cluster_jobs AS (
  SELECT
    t1.*,
    t2.cluster_name,
    t2.owned_by,
    t2.dbr_version
  FROM job_tasks_exploded t1
    INNER JOIN clusters t2 USING (workspace_id, cluster_id)
)
SELECT * FROM all_purpose_cluster_jobs LIMIT 10;

Retried job runs

This query collects a list of retried job runs with the number of retries for each run.

with repaired_runs as (
  SELECT
    workspace_id, job_id, run_id, COUNT(*) - 1 as retries_count
  FROM system.lakeflow.job_run_timeline
  WHERE result_state IS NOT NULL
  GROUP BY ALL
  HAVING retries_count > 0
)
SELECT
  *
FROM repaired_runs
ORDER BY retries_count DESC
LIMIT 10;

Share via

Jobs system table reference

Available job tables

Job table schema

Job task table schema

Job run timeline table schema

Trigger type values

Run type values

Result state values

Termination code values

Job task run timeline table schema

Example queries

Get the most recent version of jobs

Daily job count by workspace

Daily job status distribution by workspace

Longest running jobs overview

Job run time for jobs executed via runSubmit (ie. Airflow)

Job run analysis

Jobs running on all-purpose compute

Retried job runs

Feedback

Additional resources