What Are System Tables? Databricks Monitoring & Audit Tables Complete Guide

System Tables let you query operational metadata from your Databricks workspace directly with SQL, including audit logs, billing data, compute resources, and table lineage. They live as Delta Tables under the Unity Catalog system catalog, and serve as the foundation for BI, security auditing, and cost optimization. The Data Engineer Professional exam asks about the available System Tables, their use cases, and how to enable them.

System Tables Overview

System Tables are metadata tables that the Databricks platform generates and updates automatically. You do not write to them explicitly — workspace activity (job runs, table access, cluster creation, and so on) is recorded in real time. Every table lives in a schema under the system catalog.

Key Tables at a Glance

Enabling System Tables

By default, only a subset of System Tables schemas is enabled. To enable all of them, an Account Admin runs the following commands.

-- 全スキーマを有効化（アカウント管理者権限が必要）
ALTER CATALOG system ENABLE ALL SCHEMAS

-- 特定スキーマのみ有効化
ALTER CATALOG system ENABLE SCHEMA billing
ALTER CATALOG system ENABLE SCHEMA access

-- アクセス権の付与（データエンジニアグループに監査ログの読み取り権限）
GRANT SELECT ON SCHEMA system.access TO `data-engineers`
GRANT SELECT ON TABLE system.billing.usage TO `finance-team`

Audit Log Query Examples

The system.access.audit table records every API request and action taken in the workspace. Use it for security auditing and compliance reporting.

-- 過去7日間のテーブルアクセスログ（誰がどのテーブルを参照したか）
SELECT
  event_date,
  user_identity.email AS user_email,
  action_name,
  request_params.full_name_arg AS table_name,
  source_ip_address,
  response.status_code
FROM system.access.audit
WHERE action_name IN ('getTable', 'commandSubmit')
  AND event_date >= CURRENT_DATE - INTERVAL 7 DAYS
  AND request_params.full_name_arg IS NOT NULL
ORDER BY event_date DESC

-- 特定ユーザーの操作履歴（インシデント調査用）
SELECT
  event_date,
  event_time,
  action_name,
  request_params,
  response.status_code,
  source_ip_address
FROM system.access.audit
WHERE user_identity.email = '[email protected]'
  AND event_date >= '2026-03-01'
ORDER BY event_time DESC

Billing Analysis Query Examples

Join system.billing.usage with system.billing.list_prices to analyze actual costs in USD.

-- 過去30日のSKU別DBU消費量と推定コスト
SELECT
  u.sku_name,
  u.usage_unit,
  SUM(u.usage_quantity) AS total_dbu,
  ROUND(SUM(u.usage_quantity * p.pricing.default), 2) AS estimated_cost_usd
FROM system.billing.usage u
LEFT JOIN system.billing.list_prices p
  ON u.sku_name = p.sku_name
  AND u.usage_date BETWEEN p.price_start_time AND COALESCE(p.price_end_time, '2099-12-31')
WHERE u.usage_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY u.sku_name, u.usage_unit
ORDER BY estimated_cost_usd DESC

-- ワークスペース別の日次コスト推移
SELECT
  u.usage_date,
  u.workspace_id,
  ROUND(SUM(u.usage_quantity * p.pricing.default), 2) AS daily_cost_usd
FROM system.billing.usage u
LEFT JOIN system.billing.list_prices p
  ON u.sku_name = p.sku_name
  AND u.usage_date BETWEEN p.price_start_time AND COALESCE(p.price_end_time, '2099-12-31')
WHERE u.usage_date >= CURRENT_DATE - INTERVAL 90 DAYS
GROUP BY u.usage_date, u.workspace_id
ORDER BY u.usage_date DESC

Cluster Monitoring Query Examples

Use the system.compute.clusters table to analyze cluster configuration, uptime, and cost efficiency.

-- アクティブなクラスタとそのスペック一覧
SELECT
  cluster_id,
  cluster_name,
  cluster_source,
  driver_node_type,
  node_type_id AS worker_node_type,
  autoscale.min_workers,
  autoscale.max_workers,
  spark_version,
  creator
FROM system.compute.clusters
WHERE delete_time IS NULL
ORDER BY cluster_name

-- 長時間稼働クラスタの検出（コスト最適化）
SELECT
  cluster_id,
  cluster_name,
  creator,
  state,
  TIMESTAMPDIFF(HOUR, last_restarted_time, CURRENT_TIMESTAMP) AS hours_since_restart
FROM system.compute.clusters
WHERE state = 'RUNNING'
  AND delete_time IS NULL
  AND TIMESTAMPDIFF(HOUR, last_restarted_time, CURRENT_TIMESTAMP) > 24
ORDER BY hours_since_restart DESC

Using Table Lineage

Lineage automatically tracked by Unity Catalog is recorded in the system.access.table_lineage and system.access.column_lineage tables. Use them to visualize data flow for impact analysis and data-quality tracking.

-- 特定テーブルの上流テーブル（データソース）を特定
SELECT
  source_table_full_name,
  target_table_full_name,
  event_date,
  entity_type
FROM system.access.table_lineage
WHERE target_table_full_name = 'gold.analytics.daily_revenue'
  AND event_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY source_table_full_name, target_table_full_name,
         event_date, entity_type
ORDER BY event_date DESC

Operational Best Practices

• Always specify filters: System Tables hold huge volumes of records, so omitting an event_date or usage_date filter in the WHERE clause can drive query costs through the roof.
• Aggregate into gold tables: Pre-aggregate frequently used metrics into a materialized view or Delta Table, and visualize them in a Databricks SQL dashboard.
• Configure alerts: Use Databricks SQL alerts to detect cost thresholds and unusual access patterns.
• Long-term storage: If you need data beyond the 365-day retention window, export it to external storage on a scheduled job.

Sample Question

Frequently Asked Questions