Lakeflow pipeline event log cheat sheet: SQL for failures, expectations, backlog and lineage

#When to use this

A pipeline update failed, ran long or dropped more rows than expected, and the pipeline UI only shows the last error. Every pipeline writes an event log (a Delta table) that records each update, flow, expectation result, autoscale decision and user action. These queries answer the usual on-call questions directly.

Databricks now also offers a pipeline events system table (Beta) that covers every pipeline in a region. The columns are similar. Check your system catalog for availability before relying on it.

#Access: three ways in

By default the event log is a hidden table in the pipeline's default catalog and schema, and only the run-as user can query it. To give on-call engineers access, publish it under Settings → Advanced → Event log (catalog, schema, name). Then put a view over it and grant on the view:

CREATE VIEW prod.ops.orders_pipeline_events AS
SELECT * FROM prod.ops.orders_pipeline_event_log;

GRANT SELECT ON VIEW prod.ops.orders_pipeline_events TO `data-oncall`;

Caution

Don't drop the event log table, or the catalog or schema it lives in. The pipeline can fail on its next update. The published event log location is also the schema location for Auto Loader in that pipeline.

The rest of this page queries a view named events. Point it at whichever source you have:

CREATE OR REPLACE TEMP VIEW events AS
SELECT * FROM event_log('<pipeline-id>');

#Columns you'll use

Event types and fields can change between releases. If a query returns nothing, check your pipeline's event log schema.

#Latest update ID

Most queries filter to one update. Keep this CTE handy:

WITH latest_update AS (
  SELECT origin.update_id AS id
  FROM events
  WHERE event_type = 'create_update'
  ORDER BY timestamp DESC
  LIMIT 1
)
SELECT * FROM latest_update;

#Why did the update fail?

SELECT timestamp, origin.update_id, origin.flow_name, message, error
FROM events
WHERE level = 'ERROR'
ORDER BY timestamp DESC
LIMIT 50;

The first error by timestamp in an update is usually the root cause. Errors after it are often downstream flows that failed or were skipped because of it.

Update history, with final state per update:

SELECT origin.update_id,
       MIN(timestamp) AS first_event,
       MAX(timestamp) AS last_event,
       MAX_BY(details:update_progress.state, timestamp) AS final_state
FROM events
WHERE event_type IN ('create_update', 'update_progress')
GROUP BY origin.update_id
ORDER BY first_event DESC
LIMIT 20;

States include COMPLETED, FAILED and CANCELED.

#Per-flow status and row counts

WITH latest_update AS (
  SELECT origin.update_id AS id FROM events
  WHERE event_type = 'create_update' ORDER BY timestamp DESC LIMIT 1
)
SELECT origin.flow_name,
       MAX_BY(details:flow_progress.status, timestamp) AS last_status,
       SUM(TRY_CAST(details:flow_progress.metrics.num_output_rows AS BIGINT)) AS output_rows,
       SUM(TRY_CAST(details:flow_progress.metrics.num_upserted_rows AS BIGINT)) AS upserted_rows,
       SUM(TRY_CAST(details:flow_progress.metrics.num_deleted_rows AS BIGINT)) AS deleted_rows,
       MAX(TRY_CAST(details:flow_progress.data_quality.dropped_records AS BIGINT)) AS dropped_by_expectations
FROM events, latest_update
WHERE event_type = 'flow_progress'
  AND origin.update_id = latest_update.id
  AND origin.flow_name IS NOT NULL
GROUP BY origin.flow_name
ORDER BY origin.flow_name;

#Expectations: what passed, failed, dropped

WITH latest_update AS (
  SELECT origin.update_id AS id FROM events
  WHERE event_type = 'create_update' ORDER BY timestamp DESC LIMIT 1
)
SELECT e.dataset, e.name AS expectation,
       SUM(e.passed_records) AS passed,
       SUM(e.failed_records) AS failed
FROM (
  SELECT explode(from_json(
           details:flow_progress.data_quality.expectations,
           'array<struct<name: string, dataset: string, passed_records: bigint, failed_records: bigint>>'
         )) AS e
  FROM events, latest_update
  WHERE event_type = 'flow_progress'
    AND origin.update_id = latest_update.id
) GROUP BY e.dataset, e.name
ORDER BY failed DESC;

Trend over the last 14 days, to see whether failures are new:

SELECT date_trunc('DAY', timestamp) AS day, e.dataset, e.name,
       SUM(e.failed_records) AS failed
FROM (
  SELECT timestamp, explode(from_json(details:flow_progress.data_quality.expectations,
         'array<struct<name: string, dataset: string, passed_records: bigint, failed_records: bigint>>')) AS e
  FROM events
  WHERE event_type = 'flow_progress' AND timestamp > current_timestamp() - INTERVAL 14 DAYS
) GROUP BY ALL
ORDER BY day DESC, failed DESC;

#Backlog: is a streaming flow falling behind?

SELECT timestamp, origin.flow_name,
       DOUBLE(details:flow_progress.metrics.backlog_bytes) AS backlog_bytes
FROM events
WHERE event_type = 'flow_progress'
  AND details:flow_progress.metrics.backlog_bytes IS NOT NULL
  AND timestamp > current_timestamp() - INTERVAL 6 HOURS
ORDER BY origin.flow_name, timestamp;

If backlog keeps growing across updates, the flow can't keep up. Check cluster_resources below before you add workers. Backlog metrics aren't reported for every source type or runtime version.

For Kafka and Event Hubs sources, the raw streaming progress has offsets-behind metrics:

SELECT timestamp,
       parse_json(get_json_object(details, '$.stream_progress.progress_json')) AS progress
FROM events
WHERE event_type = 'stream_progress'
ORDER BY timestamp DESC
LIMIT 20;

#Auto Loader listing

SELECT timestamp,
       details:operation_progress.type,
       details:operation_progress.status,
       details:operation_progress.duration_ms,
       details:operation_progress.auto_loader_details.source_path,
       details:operation_progress.auto_loader_details.num_files_listed
FROM events
WHERE event_type = 'operation_progress'
  AND details:operation_progress.type IN ('AUTO_LOADER_LISTING', 'AUTO_LOADER_BACKFILL')
ORDER BY timestamp DESC;

Long duration_ms with large num_files_listed means directory listing is the bottleneck.

#Compute: under-sized or stuck scaling (classic compute)

SELECT timestamp,
       DOUBLE(details:cluster_resources.num_executors) AS executors,
       DOUBLE(details:cluster_resources.avg_task_slot_utilization) AS slot_utilization,
       DOUBLE(details:cluster_resources.avg_num_queued_tasks) AS queued_tasks,
       details:cluster_resources.state AS autoscale_state
FROM events
WHERE event_type = 'cluster_resources'
ORDER BY timestamp DESC
LIMIT 100;

High utilization with queued tasks and the state stuck on scale-up usually means a max-workers cap, vCPU quota or VM capacity problem. Pair this with event_type = 'autoscale' (details:autoscale.status, requested_num_executors). These events only appear on classic compute with enhanced autoscaling.

#Lineage: what feeds what

SELECT details:flow_definition.output_dataset AS output,
       details:flow_definition.input_datasets AS inputs,
       details:flow_definition.flow_type AS flow_type
FROM events
WHERE details:flow_definition IS NOT NULL
  AND origin.update_id = (SELECT origin.update_id FROM events
                          WHERE event_type = 'create_update' ORDER BY timestamp DESC LIMIT 1);

#Who changed or started it?

SELECT timestamp, details:user_action.action, details:user_action.user_name
FROM events
WHERE event_type = 'user_action'
ORDER BY timestamp DESC;

Runtime version per update, useful after a channel change:

SELECT origin.update_id, details:runtime_details.runtime_version.dbr_version
FROM events
WHERE event_type = 'runtime_details';

#Cost of the pipeline

SELECT usage_date, sku_name, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_metadata.dlt_pipeline_id = '<pipeline-id>'
  AND usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY usage_date DESC;

#Prevent it next time

• Publish the event log for every production pipeline and grant on-call a view, so nobody needs the run-as identity to debug.
• Schedule the expectations query as a Databricks SQL alert. Alert when failed or dropped_by_expectations goes over a baseline.
• Alert on level = 'ERROR' events and on backlog growing for more than N updates.
• Keep expectations named clearly (valid_order_id, not check1), because the name is all you see in these results.