6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs

6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs

DP-750 Exam Focus: Understand the architectural placement, schema definitions, and analytical use cases of Unity Catalog system tables within the system catalog. Be prepared to write and interpret Databricks SQL queries against system.access.audit (security and compliance auditing), system.billing.usage & system.billing.list_prices (DBU cost allocation and tag-based chargebacks), and system.lineage.table_lineage & system.lineage.column_lineage (automated lineage querying).

---

1. System Tables Architecture & Enablement

Prior to Unity Catalog system tables, monitoring Azure Databricks platform activity required configuring Azure Diagnostic Settings to stream JSON logs into Azure Log Analytics, Azure Event Hubs, or ADLS Gen2 storage accounts. Analyzing these logs required writing custom JSON parsing pipelines and maintaining separate reporting databases.

Unity Catalog System Tables eliminate this complexity by delivering operational telemetry directly into a built-in, serverless, SQL-queryable Delta Lake catalog named system.

+-----------------------------------------------------------------------------------+
|                         UNITY CATALOG `system` CATALOG                            |
+-----------------------------------------------------------------------------------+
|                                                                                   |
|  +------------------------+  +------------------------+  +---------------------+  |
|  |     system.access      |  |     system.billing     |  |   system.lineage    |  |
|  |  - audit               |  |  - usage               |  |  - table_lineage    |  |
|  |  - table_privileges    |  |  - list_prices         |  |  - column_lineage   |  |
|  |  - column_privileges   |  |                        |  |                     |  |
|  +------------------------+  +------------------------+  +---------------------+  |
|                                                                                   |
|  +------------------------+  +------------------------+  +---------------------+  |
|  |     system.compute     |  |    system.lakeflow     |  |   system.market     |  |
|  |  - clusters            |  |  - jobs                |  |   - shares          |  |
|  |  - node_types          |  |  - job_run_timeline    |  |   - recipients      |  |
|  +------------------------+  +------------------------+  +---------------------+  |
+-----------------------------------------------------------------------------------+

Core System Table Schemas

1. system.access: Contains security audit logs (audit), role assignments, and permission grants across all workspaces.
2. system.billing: Delivers real-time and historical DBU consumption records (usage) and unit pricing schedules (list_prices).
3. system.lineage: Stores historical graph records for table-level (table_lineage) and column-level (column_lineage) derivations.
4. system.compute: Details cluster hardware configurations, lifecycle state transitions, node allocations, and Spark runtime versions.
5. system.lakeflow: Telemetry on workflow tasks, pipeline schedules, execution durations, retries, and SLA triggers.
6. system.information_schema: ANSI-standard schema views detailing metadata for all catalogs, schemas, tables, columns, volumes, and constraints.

Enabling System Table Schemas

System tables are deployed at the Unity Catalog metastore level and are accessible across all workspaces attached to that metastore. A Metastore Admin enables individual system schemas via the Databricks System Schemas API or Account Console:

# Enable the billing and audit system schemas via Databricks CLI
databricks system-schemas enable billing
databricks system-schemas enable access
databricks system-schemas enable lineage

---

2. Audit Logging with system.access.audit

The system.access.audit table records every control plane and data plane event generated within the account, providing an immutable audit trail for security audits and forensic analysis.

Key Schema Fields in system.access.audit

Security Scenario: Detecting Unauthorized Access Attempts (403 Denied)

-- Query: Find all unauthorized table access attempts within the last 7 days
SELECT 
    event_time,
    user_identity.email AS user_email,
    client_ip_address,
    workspace_id,
    service_name,
    action_name,
    request_params['table_name'] AS attempted_table,
    response.status_code AS http_code,
    response.error_message AS denial_reason
FROM system.access.audit
WHERE event_date >= current_date() - INTERVAL 7 DAYS
  AND service_name = 'unityCatalog'
  AND response.status_code = 403
ORDER BY event_time DESC;

Governance Scenario: Auditing Privilege Changes (GRANT / REVOKE)

-- Query: Audit all privilege modifications made to production catalogs
SELECT 
    event_time,
    user_identity.email AS modified_by,
    request_params['securable_type'] AS object_type,
    request_params['securable_full_name'] AS object_name,
    request_params['principal'] AS grantee,
    request_params['changes'] AS privilege_changes
FROM system.access.audit
WHERE event_date >= current_date() - INTERVAL 30 DAYS
  AND action_name IN ('updatePermissions', 'grantPermissions', 'revokePermissions')
ORDER BY event_time DESC;

---

3. Cost Governance: system.billing.usage & list_prices

Financial cost management and chargeback attribution are core responsibilities for enterprise data engineers. Unity Catalog captures granular DBU consumption in system.billing.usage and associates it with contract pricing in system.billing.list_prices.

                               CHARGEBACK CALCULATION PIPELINE

  +-------------------------------------+       +-------------------------------------+
  |     system.billing.usage            |       |     system.billing.list_prices      |
  |  - usage_quantity (DBUs)            |       |  - pricing.default (Price per DBU)  |
  |  - sku_name (Compute Tier)          |       |  - sku_name                         |
  |  - custom_tags['CostCenter']        |       |  - currency_code ('USD')            |
  +-------------------------------------+       +-------------------------------------+
                     |                                             |
                     +----------------------+----------------------+
                                            |
                                            v
                             [ Databricks SQL JOIN Query ]
                                            |
                                            v
                     +---------------------------------------------+
                     |   Departmental Chargeback Report            |
                     |   CostCenter | Month | Total DBUs | USD ($) |
                     +---------------------------------------------+

Key Fields in system.billing.usage

• usage_quantity: Total Databricks Units (DBUs) consumed during the usage window.
• sku_name: The compute workload SKU (e.g., ENTERPRISE_ALL_PURPOSE_COMPUTE, ENTERPRISE_JOBS_COMPUTE, ENTERPRISE_SERVERLESS_SQL_WAREHOUSE_PRO).
• usage_start_time / usage_end_time: Timestamp range for the recorded meter tick.
• custom_tags: Key-value map (MAP<STRING, STRING>) containing all tags attached to clusters, pools, jobs, and SQL warehouses.
• usage_metadata: Contains cluster IDs, job IDs, warehouse IDs, and DLT pipeline IDs.

Financial Chargeback Query Pattern

To calculate actual monetary cost per department, join usage with list_prices on sku_name:

-- Query: Monthly chargeback breakdown by CostCenter and Compute SKU
SELECT 
    date_trunc('month', u.usage_start_time) AS billing_month,
    coalesce(u.custom_tags['CostCenter'], 'Unallocated') AS cost_center,
    coalesce(u.custom_tags['Environment'], 'Production') AS environment,
    u.sku_name,
    ROUND(SUM(u.usage_quantity), 2) AS total_dbus_consumed,
    ROUND(SUM(u.usage_quantity * p.pricing.default), 2) AS total_cost_usd
FROM system.billing.usage u
INNER JOIN system.billing.list_prices p
    ON u.sku_name = p.sku_name
   AND u.usage_start_time >= p.price_start_time
   AND (p.price_end_time IS NULL OR u.usage_start_time < p.price_end_time)
WHERE u.usage_date >= '2026-01-01'
GROUP BY 1, 2, 3, 4
ORDER BY billing_month DESC, total_cost_usd DESC;

Exam Tip: If compute clusters are not configured with custom tags (e.g., CostCenter), custom_tags['CostCenter'] evaluates to NULL. Best practice mandates enforcing Cluster Policies that require specific tag keys on all provisioned compute.

---

4. Programmatic Lineage Querying: system.lineage

While Catalog Explorer offers an interactive visual graph, enterprise compliance automation, metadata catalogs, and CI/CD pipelines query lineage programmatically using system.lineage.table_lineage and system.lineage.column_lineage.

system.lineage.table_lineage

Captures all table-to-table dependencies created during query runs:

• source_table_full_name: 3-level name of the source table (e.g., bronze.crm.raw_customers).
• target_table_full_name: 3-level name of the generated/modified table (e.g., silver.crm.dim_customers).
• source_type / target_type: Object type (TABLE, VIEW, PATH).
• entity_type: The orchestrating runtime (NOTEBOOK, JOB, DLT_PIPELINE, DASHBOARD).
• entity_id: The unique identifier of the executing job, notebook, or pipeline.
• created_by: The user or service principal identity that executed the query.
• event_time: Timestamp when the write occurred.

system.lineage.column_lineage

Captures exact column-level derivation:

• source_table_full_name & source_column_name
• target_table_full_name & target_column_name

Compliance Scenario: Tracking PII Column Propagation

Suppose compliance officers need to verify where the sensitive column social_security_number in bronze.hr.employees has been propagated across the entire lakehouse:

-- Query: Trace all downstream tables and columns inheriting from a sensitive PII column
SELECT 
    event_time,
    source_table_full_name,
    source_column_name,
    target_table_full_name,
    target_column_name,
    entity_type,
    created_by
FROM system.lineage.column_lineage
WHERE source_table_full_name = 'bronze.hr.employees'
  AND source_column_name = 'social_security_number'
  AND event_date >= current_date() - INTERVAL 90 DAYS
ORDER BY event_time DESC;

---

5. Security & Delegating Access to System Tables

Because the system catalog contains sensitive account-wide financial and security telemetry, access is restricted by default.

Administrative Delegation Model

• Metastore Admins automatically possess full access to all schemas under system.
• To allow specific teams (such as FinOps, SecOps, or Compliance) to query these tables, Metastore Admins grant granular, least-privilege permissions:

-- Step 1: Grant catalog-level visibility
GRANT USE CATALOG ON CATALOG system TO `finops_analysts`;

-- Step 2: Grant schema-level access exclusively to billing data
GRANT USE SCHEMA ON SCHEMA system.billing TO `finops_analysts`;
GRANT SELECT ON SCHEMA system.billing TO `finops_analysts`;

-- Step 3: Grant security analysts access exclusively to audit logs
GRANT USE CATALOG ON CATALOG system TO `security_operations`;
GRANT USE SCHEMA ON SCHEMA system.access TO `security_operations`;
GRANT SELECT ON TABLE system.access.audit TO `security_operations`;

Important: Never grant ALL PRIVILEGES on CATALOG system to broad groups. Use schema-level and table-level GRANT statements to maintain least-privilege segregation of duties between security and financial personnel.