Auditing and Job Monitoring with system.access.audit and system.lakeflow

1.Auditing account activity with system.access.audit

Billing tells you what was spent. system.access.audit tells you who did what. The table is in Public Preview and records audit events from the workspaces in your region. Each row is one event, made up of the service that handled it, the action, the person or principal who called it, the parameters they sent and the response they got.

Four details affect how you query it:

- Partition by date. Filter on event_date, not event_time, to improve query performance. - Scope. Workspace-level events are regional. Account-level events are global and record workspace_id as 0, so an account-level action won't show up under a workspace ID filter. - Masking. Some events store SQL definitions in request_params under keys such as view_definition and function_info. Those keys are omitted unless you are an account admin or a member of the databricks_pii_access group. Other keys and columns are unchanged. - Workspace deletion. When a workspace is deleted, its audit events older than 14 days are removed from the table, so export anything you must keep before decommissioning a workspace.

Sources1

2.The system.lakeflow schema: jobs, tasks, pipelines and their timelines

For workload monitoring, the main schema is system.lakeflow, which was previously called workflow. Both schemas have identical content, so older queries against system.workflow still match. The tables are regional: to see another region's jobs, you query from a workspace deployed in that region. Job tables typically get records within one hour, which is much fresher than billing data.

The jobs table also stores the definition fields you'll want to audit, including run_as_user_name, creator_user_name, paused, timeout_seconds, health_rules and trigger_type (MULTIPLE when a job has several triggers). Many of these columns aren't populated for rows emitted before early December 2025, so null values in older history are expected and don't mean the data is bad.

Sources2

3.Querying current state from SCD2 tables

jobs, job_tasks and pipelines never update a row. Each change adds a new row with a new change_time, and a deletion adds a row with delete_time populated. A plain SELECT * FROM system.lakeflow.jobs therefore returns every historical version of every job. To get current state, take the latest row per entity key, and only after that drop rows that record a deletion.

-- Get the current state of every job, excluding deleted jobs
with latest 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 * FROM latest WHERE delete_time IS NULL

-- Get the most recent version of each job task
SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id,  ?  ORDER BY change_time DESC) as rn
FROM
  system.lakeflow.job_tasks QUALIFY rn=1

Sources2

4.Monitoring runs with the timeline tables

The timeline tables record what actually ran. job_run_timeline, job_task_run_timeline and pipeline_update_timeline are immutable and complete when produced, so they need no deduplication. Long runs are split into hourly slices across several rows, each bounded by period_start_time and period_end_time, and availability is measured from period_end_time.

In job_task_run_timeline, each row carries job_id, job_run_id, run_id (the task run) and task_key. The compute_ids array lists the job clusters, interactive clusters and SQL warehouses the task used, which lets you join a task's runtime to system.compute and, through cluster_id, to its billing rows. Because of hourly slicing, result_state is filled only on the row that represents the end of the run. When you count failures, filter or aggregate on that row; don't count every slice.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Adding WHERE delete_time IS NULL directly to the query that reads system.lakeflow.jobs removes deleted jobs.Why is that wrong?

   Filtering delete_time in the same pass discards the deletion row, so the job's last pre-deletion version is returned as if it were current. Select the latest row first, then filter.

   Covered in Querying current state from SCD2 tables

2. 2.system.access.audit is global like system.billing.usage, so one query from any workspace sees every region's events.Why is that wrong?

   Audit data is regional for workspace-level events. Only account-level events are global.

   Covered in Auditing account activity with system.access.audit

Practise it for real

Build a current-state inventory of jobs and tasks from system.lakeflow and confirm that deleted jobs are excluded

1. 1.Run SELECT COUNT(*) FROM system.lakeflow.jobs and compare it with the number of jobs you see in the Jobs UI.

   Why: jobs is an SCD2 table, so every edit and deletion adds a row.

   You should see: The count is higher than your number of jobs because the table holds every historical version.

2. 2.Run the 'current state of every job, excluding deleted jobs' query from this page.

   Why: ROW_NUMBER over workspace_id, job_id ordered by change_time DESC keeps the latest version, and the outer filter removes deleted jobs.

   You should see: One row per live job, matching the jobs you can see.

3. 3.Move the delete_time IS NULL filter inside the CTE and run the query again.

   Why: This shows the ordering trap: the deletion row is filtered out before the latest row is chosen.

   You should see: Deleted jobs reappear, showing their last state before deletion.

4. 4.Adapt the query to system.lakeflow.job_tasks, partitioning by workspace_id, job_id, task_key.

   Why: Each SCD2 table has its own entity key.

   You should see: One row per live task per job.