VW_DATASET_JOBS & VW_DATASET_JOB_EXECUTIONS

Overview

These two views provide visibility into job scheduling and execution history. VW_DATASET_JOBS contains job definitions (name, project, schedule count), while VW_DATASET_JOB_EXECUTIONS provides the execution history for each job with timing, status, and aggregate test results.

Use these views for job monitoring dashboards, SLA tracking, and understanding which jobs are inactive or failing.

VW_DATASET_JOBS Schema

VW_DATASET_JOB_EXECUTIONS Schema

Execution Identity

Timing

Job Context

Test Result Counts

Relationships

VW_DATASET_JOBS
    │
    └── JOB_ID → VW_DATASET_JOB_EXECUTIONS.JOB_ID (one-to-many)

VW_DATASET_JOB_EXECUTIONS
    │
    ├── BATCH_ID → VW_DATASET_TEST_EXECUTIONS.BATCH_ID
    └── PROJECT_ID → VW_DATASET_TESTS.PROJECT_ID

Example Queries

Job Run History with Pass/Fail Summary

SELECT
    JOB_NAME,
    PROJECT,
    RUN_DATE,
    DURATION,
    RESULT,
    TRIGGERED_BY
FROM REPOSITORY.VW_DATASET_JOB_EXECUTIONS
WHERE RUN_DATE >= DATEADD(day, -7, CURRENT_DATE)
ORDER BY RUN_DATE DESC;

Jobs Not Executed in 7+ Days

SELECT
    JOB_NAME,
    PROJECT_NAME,
    JOB_FOLDER_PATH,
    LAST_EXECUTED,
    DATEDIFF(day, LAST_EXECUTED, CURRENT_DATE) AS DAYS_SINCE_LAST_RUN,
    SCHEDULES_COUNT
FROM REPOSITORY.VW_DATASET_JOBS
WHERE LAST_EXECUTED < DATEADD(day, -7, CURRENT_DATE)
    OR LAST_EXECUTED IS NULL
ORDER BY LAST_EXECUTED ASC NULLS FIRST;

Average Job Duration by Project

SELECT
    PROJECT,
    JOB_NAME,
    COUNT(*) AS RUN_COUNT,
    AVG(DURATION) AS AVG_DURATION_SEC,
    MAX(DURATION) AS MAX_DURATION_SEC,
    MIN(DURATION) AS MIN_DURATION_SEC
FROM REPOSITORY.VW_DATASET_JOB_EXECUTIONS
WHERE RUN_DATE >= DATEADD(day, -30, CURRENT_DATE)
    AND END_DATE IS NOT NULL
GROUP BY PROJECT, JOB_NAME
ORDER BY AVG_DURATION_SEC DESC;

Job Failure Rate

SELECT
    JOB_NAME,
    COUNT(*) AS TOTAL_RUNS,
    SUM(CASE WHEN JOB_STATUS = 'Passed' THEN 1 ELSE 0 END) AS PASSES,
    SUM(CASE WHEN JOB_STATUS = 'Failed' THEN 1 ELSE 0 END) AS FAILURES,
    SUM(CASE WHEN JOB_STATUS = 'Error' THEN 1 ELSE 0 END) AS ERRORS,
    ROUND(SUM(CASE WHEN JOB_STATUS = 'Failed' THEN 1 ELSE 0 END)::DECIMAL / COUNT(*) * 100, 1) AS FAILURE_RATE_PCT
FROM REPOSITORY.VW_DATASET_JOB_EXECUTIONS
WHERE RUN_DATE >= DATEADD(day, -30, CURRENT_DATE)
    AND JOB_STATUS != 'N/A'
GROUP BY JOB_NAME
HAVING COUNT(*) >= 3
ORDER BY FAILURE_RATE_PCT DESC;

VW_DATASET_JOB_PROPERTIES — Extended Property Details

The companion view VW_DATASET_JOB_PROPERTIES provides a vertical (one-row-per-property) representation of job details that don't fit in the flat VW_DATASET_JOBS structure. Each row represents one property for a specific job.

Schema

Property Categories

Joining Jobs to Properties

Use JOB_ID as the join key between VW_DATASET_JOBS and VW_DATASET_JOB_PROPERTIES.

Example: Jobs with a Custom Field Indicating Environment

SELECT
    j.JOB_NAME,
    j.PROJECT_NAME,
    j.LAST_EXECUTED,
    p.PROPERTY_VALUE AS ENVIRONMENT
FROM REPOSITORY.VW_DATASET_JOBS j
INNER JOIN REPOSITORY.VW_DATASET_JOB_PROPERTIES p
    ON p.JOB_ID = j.JOB_ID
WHERE p.PROPERTY_CATEGORY = 'Custom Metadata'
    AND p.PROPERTY_NAME = 'Environment'
    AND p.PROPERTY_VALUE = 'Production'
ORDER BY j.LAST_EXECUTED DESC;

Example: List All Steps for a Specific Job

SELECT
    PROPERTY_NAME AS STEP_NAME,
    PROPERTY_VALUE AS STEP_TYPE,
    PROPERTY_NAME_INDEX AS STEP_ORDER
FROM REPOSITORY.VW_DATASET_JOB_PROPERTIES
WHERE JOB_NAME = 'Daily Validation Suite'
    AND PROPERTY_CATEGORY = 'Steps'
ORDER BY PROPERTY_NAME_INDEX;

Example: Jobs That Have Not Been Scheduled

SELECT
    j.JOB_NAME,
    j.PROJECT_NAME,
    j.LAST_EXECUTED
FROM REPOSITORY.VW_DATASET_JOBS j
WHERE NOT EXISTS (
    SELECT 1
    FROM REPOSITORY.VW_DATASET_JOB_PROPERTIES p
    WHERE p.JOB_ID = j.JOB_ID
        AND p.PROPERTY_CATEGORY = 'Schedules'
)
ORDER BY j.PROJECT_NAME, j.JOB_NAME;

Example: Jobs Where a Comment Mentions "SLA"

SELECT DISTINCT
    j.JOB_NAME,
    j.PROJECT_NAME,
    p.PROPERTY_VALUE AS COMMENT_TEXT,
    p.MODIFIED_BY,
    p.DATE_MODIFIED
FROM REPOSITORY.VW_DATASET_JOBS j
INNER JOIN REPOSITORY.VW_DATASET_JOB_PROPERTIES p
    ON p.JOB_ID = j.JOB_ID
WHERE p.PROPERTY_CATEGORY = 'Discussion Comment'
    AND LOWER(p.PROPERTY_VALUE) LIKE '%sla%'
ORDER BY p.DATE_MODIFIED DESC;

Secure View

VW_SECURE_DATASET_JOB_PROPERTIES adds USER_ID and ACCESS_TYPE columns, filtered by project membership (same security tier as VW_SECURE_DATASET_JOBS).

Databricks Notes

• Replace DATEADD(day, -7, CURRENT_DATE) with DATE_ADD(CURRENT_DATE(), -7)
• Replace DATEDIFF(day, col, CURRENT_DATE) with DATEDIFF(CURRENT_DATE(), col)
• Replace col::DECIMAL with CAST(col AS DECIMAL)
• The RUNTIME_PARENT column shows the full job nesting path when a job is triggered as a step inside another job