Study guide

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

Microsoft Certified: Azure Databricks Data Engineer Associate DP-750

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Azure Databricks Architecture & Compute Selection

1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute

2 Chapter 2: Compute Configuration, Performance & Cost Management

2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions

3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup

3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown

4 Chapter 4: Unity Catalog Privilege Model & Access Control

4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation

5 Chapter 5: Advanced Data Security, Masking & ABAC

5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks

6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing

6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions

7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming

7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory

8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance

8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg

9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture

9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment

10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables

10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs

11 Chapter 11: Workflow Orchestration with Lakeflow Jobs

11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling

12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles

12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines

13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning

13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics

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

Key Takeaways Explain This Topic AI

The Unity Catalog 'system' catalog is a centralized, account-level analytical store hosting operational telemetry across audit logs, DBU billing, compute utilization, lakeflow jobs, and data lineage.

The 'system.access.audit' table provides a unified audit trail capturing user identities, service principals, IP addresses, workspace IDs, and specific actions such as table queries, credential generations, and ACL grants.

The 'system.billing.usage' and 'system.billing.list_prices' tables provide granular, per-second DBU consumption metrics, allowing direct cost attribution by joining on custom cluster and job tags (e.g., CostCenter, Environment).

Historical lineage relationships are queryable programmatically via 'system.lineage.table_lineage' and 'system.lineage.column_lineage', capturing source/target relations, timestamps, and executing entities.

Access to system tables is managed via standard Unity Catalog ANSI SQL grants administered by Metastore Admins (e.g., GRANT USE SCHEMA, GRANT SELECT ON SCHEMA system.billing TO billing_team).

Last updated: August 2026

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

system.access : Contains security audit logs ( audit ), role assignments, and permission grants across all workspaces.

system.billing : Delivers real-time and historical DBU consumption records ( usage ) and unit pricing schedules ( list_prices ).

system.lineage : Stores historical graph records for table-level ( table_lineage ) and column-level ( column_lineage ) derivations.

system.compute : Details cluster hardware configurations, lifecycle state transitions, node allocations, and Spark runtime versions.

system.lakeflow : Telemetry on workflow tasks, pipeline schedules, execution durations, retries, and SLA triggers.

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

Column Name Data Type Description

event_time TIMESTAMP Exact UTC timestamp when the event occurred.

event_date DATE Event partitioning date ( yyyy-MM-dd ).

account_id STRING Databricks account identifier.

workspace_id BIGINT Specific Azure Databricks workspace ID where the action executed.

service_name STRING Databricks service (e.g., unityCatalog , notebook , jobs , clusters , accounts , sql ).

action_name STRING Specific API action (e.g., createTable , getTable , generateTemporaryTableCredential , login , grantPermissions , runCommand ).

user_identity STRUCT Principal identity details: email , subject (OID), token_id , principal_id .

request_params MAP<STRING,STRING> Parameter key-values passed to the API (e.g., table name, SQL statement, cluster ID).

response STRUCT Response status, HTTP status code ( 200 , 403 ), and error messages if denied.

client_ip_address STRING Source IP address initiating the request.

user_agent STRING Client application or SDK (e.g., Mozilla/5.0 , DatabricksTerraform/1.20 , PowerBI ).

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.

Loading diagram... System Catalog Telemetry Flow & Observability Stack