6.1 System Tables & Observability

6.1 System Tables & Observability

Observability in a modern data lakehouse is critical for tracking costs, ensuring security, maintaining operational health, and meeting compliance requirements. Databricks provides System Tables—a Databricks-managed analytical schema that logs operational data across your entire account. Unlike traditional log files that require complex parsing, extraction, and loading into a separate log analytics tool, System Tables are standard Delta tables. This means you can query them directly using standard SQL, join them with your business data, and visualize the results using Databricks SQL Dashboards. This section dives deep into the schema of system tables, how to query them for cost and usage analytics, auditing access, and monitoring job histories to ensure your Databricks environment is running efficiently and securely.

Databricks System Tables Schema Overview

System tables are housed in the system catalog in Unity Catalog. They are organized into several schemas (databases) based on their functional domain. To access them, an account admin must explicitly enable system tables at the account level using the System Tables API or the Databricks UI. Once enabled, these tables are automatically populated and maintained by Databricks, providing a unified observability layer across all workspaces in your account. The data in system tables is typically retained for a default period, though you can copy this data to your own catalogs if you require long-term historical retention. This managed structure means that observability data behaves exactly like your production data—queryable, reliable, and secured by Unity Catalog.

Key System Schemas

These tables enable administrators and data engineers to build robust monitoring solutions. You can create dashboards to track DBU burn rate, set up Databricks SQL Alerts on suspicious login activities, and perform root-cause analysis on cost spikes—all without exporting data to third-party monitoring platforms.

Cost and Usage Analytics: Querying DBU Consumption

One of the most critical use cases for system tables is FinOps and cost attribution. The system.billing.usage table provides granular, row-level records of every compute resource billed by Databricks. Each record includes the workspace ID, the sku name (e.g., jobs compute, all-purpose compute, serverless SQL), the usage quantity (in DBUs), and the timestamp.

By combining this table with custom tags, you can attribute costs to specific departments, projects, or individual users, enabling accurate chargebacks. This replaces the old model of estimating costs and brings accountability directly to the teams deploying Databricks compute resources. Whether your organization requires detailed departmental chargebacks or high-level cost tracking, the usage table offers the necessary granularity.

Analyzing Cost by Tag and SKU

When you configure clusters, SQL warehouses, or jobs with custom tags (for example, Department: Marketing or CostCenter: 12345), those tags propagate into the billing data. They appear in the usage_metadata column of the usage table as a stringified JSON object. You can extract these tags using standard Spark SQL functions like the : operator or get_json_object. Proper tagging hygiene ensures that all compute resources can be tracked accurately.

-- Querying DBU consumption by department tag for the last 30 days
SELECT
  usage_metadata.tags['Department'] AS department,
  sku_name,
  SUM(usage_quantity) AS total_dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
  AND usage_metadata.tags['Department'] IS NOT NULL
GROUP BY department, sku_name
ORDER BY total_dbus DESC;

This query aggregates the total DBUs consumed by each department, broken down by the type of compute (SKU). You can easily visualize this as a stacked bar chart in Databricks SQL, enabling stakeholders to instantly identify which business units are consuming the most resources and how those resources are allocated across different workloads like ad-hoc analysis, scheduled jobs, or model training.

Practical Scenario: Investigating a Sudden Cost Spike

Imagine you receive an automated alert that your monthly DBU consumption jumped by 40% over the weekend. To investigate, you can join the system.billing.usage table with the system.compute.clusters table to identify exactly which clusters and users are responsible for the spike. Using these tables allows you to immediately drill down into the specifics without needing to contact Databricks support or navigate complex billing portals.

-- Join billing and compute schemas to find the most expensive clusters recently
SELECT
  c.cluster_name,
  c.owned_by,
  SUM(u.usage_quantity) AS total_dbus,
  u.sku_name
FROM system.billing.usage u
JOIN system.compute.clusters c
  ON u.usage_metadata.cluster_id = c.cluster_id
WHERE u.usage_date >= current_date() - INTERVAL 3 DAYS
GROUP BY c.cluster_name, c.owned_by, u.sku_name
ORDER BY total_dbus DESC
LIMIT 10;

This query instantly reveals if a specific user spun up a massive 50-node cluster for ad-hoc analysis and forgot to configure auto-termination, resulting in high weekend costs. This level of observability allows for immediate remediation and policy enforcement, helping to establish better guardrails in the future, such as implementing cluster policies that restrict the maximum number of worker nodes a non-admin user can deploy.

Audit Logs Analysis: Tracking Security and Access

Security and compliance are paramount in enterprise data environments. The system.access.audit table acts as the single source of truth for security monitoring. It records a vast array of events, including workspace logins, table reads, permission changes, cluster creations, and secret access. The exhaustive nature of the audit log makes it invaluable not only for incident response but also for routine compliance reviews required by regulations such as GDPR, HIPAA, or SOC 2.

Identifying Unauthorized Data Access Attempts

If a security incident occurs, or during a routine compliance audit, you may need to know who accessed a sensitive table (e.g., main.hr.employee_salaries) and precisely when it happened. The request_params and response columns contain JSON payloads detailing the exact nature of the action. By filtering on specific actions like queryTable or generateTemporaryTableCredential, you can build a complete picture of data interaction over time.

-- Identify all users who queried a specific table in the last week
SELECT
  event_time,
  user_identity.email AS user_email,
  action_name,
  request_params.table_full_name
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name IN ('generateTemporaryTableCredential', 'queryTable')
  AND request_params.table_full_name = 'main.hr.employee_salaries'
  AND event_time >= current_date() - INTERVAL 7 DAYS
ORDER BY event_time DESC;

This ensures full transparency over how sensitive data assets are consumed. It also allows security administrators to confirm that only authorized personnel from the HR department have been accessing the data, quickly highlighting any anomalies or potential insider threats.

Monitoring Permission Escalation

To ensure that no unauthorized users are granting permissions (such as granting SELECT or MODIFY on sensitive catalogs), you can filter the audit log for grant events. This helps enforce the principle of least privilege. Regular monitoring of authorization changes guarantees that your access control models remain strictly enforced as the team grows and changes.

-- Monitor all GRANT statements executed in the workspace
SELECT
  event_time,
  user_identity.email AS admin_user,
  request_params.privilege,
  request_params.principal,
  request_params.securable_type,
  request_params.securable_name
FROM system.access.audit
WHERE action_name = 'grantPrivilege'
ORDER BY event_time DESC;

Security teams can use this query to build Databricks SQL Alerts that trigger a Slack notification whenever a new permission is granted to a non-standard group. This shifts security from a reactive post-mortem analysis to a proactive, real-time defense posture, catching potential policy violations immediately.

Monitoring Job Run History and Failure Rates

While the Databricks Workflows UI provides an excellent interface for orchestration, analyzing historical trends across hundreds of jobs—such as which jobs fail most frequently or take the longest to run—is best accomplished using system tables. Depending on your workspace's preview status, job execution data is available either in system.operational schemas or by extracting events directly from the audit logs. Systematic monitoring of job behavior enables data engineering teams to prioritize reliability improvements effectively.

Calculating Job Failure Rates for SLA Monitoring

To calculate the failure rate of critical production data pipelines, you can query the audit logs for job completion events and aggregate the successful versus failed outcomes over a specific time window. A high failure rate indicates unstable data sources, flaky infrastructure, or poorly tested code, and quantifying this rate allows teams to establish meaningful Service Level Indicators (SLIs).

-- Calculate failure rate per job over the last 30 days
WITH JobRuns AS (
  SELECT
    request_params.job_id,
    response.status_code,
    CASE WHEN response.status_code != '200' THEN 1 ELSE 0 END AS is_failure
  FROM system.access.audit
  WHERE service_name = 'jobs'
    AND action_name IN ('runSucceeded', 'runFailed')
    AND event_time >= current_date() - INTERVAL 30 DAYS
)
SELECT
  job_id,
  COUNT(*) AS total_runs,
  SUM(is_failure) AS failed_runs,
  ROUND(SUM(is_failure) * 100.0 / COUNT(*), 2) AS failure_rate_pct
FROM JobRuns
GROUP BY job_id
HAVING total_runs > 10
ORDER BY failure_rate_pct DESC;

Practical Scenario: SLA Breaches and Duration Monitoring

Consider a nightly ETL job that has a strict Service Level Agreement (SLA) requiring it to complete within 2 hours. You can use system tables to identify historical runs that breached this SLA. By extracting the start and end times from the event payloads, you can calculate the run duration and flag any run where duration_minutes > 120. Consistent monitoring of run duration can reveal jobs that are experiencing data volume growth outstripping their allocated compute resources, allowing engineers to scale clusters before SLAs are breached.

This data can be piped into a Databricks SQL dashboard for the data engineering team to review every morning. By leveraging System Tables in this manner, data engineers transition from reactive troubleshooting (waiting for someone to complain that data is late) to proactive observability. You apply the exact same data warehousing techniques you use for business data directly to your operational metadata, resulting in a more reliable, secure, and cost-effective lakehouse platform. The unification of business data and operational data inside the same Data Intelligence Platform provides unparalleled leverage for organizations striving for operational excellence.