1.4 Governed Asset Discovery & System Tables

Governed Asset Discovery & System Tables

As enterprise lakehouses grow to store thousands of catalogs, tables, and dashboards, finding the right dataset and auditing system activity becomes challenging. Data analysts need efficient tools to discover trusted datasets, evaluate data quality, track column definitions, and understand system operational costs. Databricks provides a comprehensive observability and discovery framework powered by Catalog Explorer, AI-Generated Documentation, and System Tables.

---

Introduction to Asset Discovery & Observability

Governed asset discovery ensures that analysts find existing, verified data products rather than re-creating duplicate data pipelines. Simultaneously, enterprise security and platform teams require complete auditability over who queried which dataset, when access was granted, and how many Databricks Units (DBUs) were consumed during execution.

+-----------------------------------------------------------------------+
|                          SYSTEM CATALOG (system)                      |
|                                                                       |
|   +-------------------+  +-------------------+  +-----------------+   |
|   |   system.access   |  |  system.billing   |  |  system.query   |   |
|   |                   |  |                   |  |                 |   |
|   |  - audit          |  |  - usage          |  |  - history      |   |
|   |  - table_lineage  |  |  - list_prices    |  |                 |   |
|   |  - column_lineage |  |                   |  |                 |   |
|   +-------------------+  +-------------------+  +-----------------+   |
+-----------------------------------------------------------------------+

---

Catalog Explorer & AI-Assisted Discovery

Catalog Explorer is the primary visual interface in Databricks for discovering, inspecting, and managing data and AI assets.

Key Discovery Capabilities

• Global Semantic Search: Analysts can search across table names, column names, schema comments, tags, and dashboard titles using the workspace search bar.
• Data Profile & Sample Data: Preview top rows, column data types, null counts, and summary distributions directly inside the Catalog Explorer UI without spinning up compute clusters.
• Lineage Inspection: Graphically trace data flow from raw ingestion source files down through transformation tables, materialized views, and downstream AI/BI dashboards.
• AI-Generated Documentation: Databricks uses built-in Large Language Models (LLMs) to automatically generate descriptive table summaries and column comments based on table schemas and sample data distributions. Analysts can review, edit, and accept these suggestions to maintain up-to-date data dictionaries.

---

Metadata Tagging & Governance Framework

Tags are key-value attributes attached to Unity Catalog objects (catalogs, schemas, tables, columns, volumes) to categorize assets for discovery and security control.

Common Tagging Use Cases

• PII / Sensitivity Tagging: Flagging columns containing sensitive data (e.g., PII = True, Confidentiality = High).
• Cost Center Attribution: Tagging compute clusters or table assets with department identifiers (e.g., CostCenter = Finance_104).
• Data Lifecycle Status: Categorizing table maturity (e.g., Tier = Gold, Status = Production).

-- Applying metadata tags to a governed Delta table and specific columns
ALTER TABLE main.sales_schema.customer_orders 
SET TAGS ('DataOwner' = 'SalesAnalyticsTeam', 'Tier' = 'Gold');

ALTER TABLE main.sales_schema.customer_orders 
ALTER COLUMN credit_card_number 
SET TAGS ('PII' = 'Sensitive', 'MaskingRequired' = 'True');

---

System Tables Architecture & Accessibility

System Tables are a Databricks-hosted analytical log repository located inside the operational system catalog. They provide read-only access to historical operational telemetry, access records, billing metrics, and query execution logs.

Core Properties of System Tables

• Centralized Location: Reside in the system catalog (e.g., system.access.audit).
• Free Querying: Querying System Tables does not incur add-on licensing fees (analysts pay standard DBU SQL Warehouse compute rates to run queries).
• Historical Retention: System activity logs are retained historically (typically 365 days), allowing long-term trend analysis.
• Unity Catalog Governed: Access to system schemas must be explicitly granted by administrators using standard SQL (GRANT USE SCHEMA ON CATALOG system TO team;).

---

Deep Dive: Key System Table Schemas

Databricks organizes operational telemetry into specialized schemas within the system catalog:

1. system.access (Audit Logs & Data Lineage)

Tracks all user actions, security modifications, and automated lineage traces.

• system.access.audit: Records authentication attempts, workspace events, file downloads, permission changes (GRANT/REVOKE), and cluster lifecycle modifications.
• system.access.table_lineage: Records source-to-target table dependencies across SQL queries and ETL jobs.
• system.access.column_lineage: Detailed column-level data flow mapping.

2. system.billing (Cost & DBU Telemetry)

Provides complete transparency into cloud compute expenditures.

• system.billing.usage: Records hourly Databricks Unit (DBU) consumption per workspace, cluster, SQL warehouse, user, and SKU.
• system.billing.list_prices: Lists active contract SKU prices for cost conversion.

3. system.compute (Cluster & Warehouse Telemetry)

Monitors compute state transitions and cluster health.

• system.compute.clusters: Historical records of cluster configurations, node sizes, auto-scaling events, and creator IDs.
• system.compute.warehouses: Operational events for SQL Warehouses.

4. system.query (Query Execution History)

Tracks SQL execution performance metrics across all workspace compute engines.

• system.query.history: Detailed log of executed SQL queries, compilation duration, execution time, shuffle read/write bytes, read row counts, and error stack traces.

---

Production SQL Query Examples for System Tables

Data Analysts frequently query System Tables to build internal operational dashboards. Below are three real-world production SQL query patterns:

Example 1: Identifying Top DBU Cost Drivers by SQL Warehouse

-- Calculate total DBU consumption and estimated cost by warehouse over the last 30 days
SELECT 
  usage_metadata.warehouse_id AS warehouse_id,
  sku_name,
  SUM(usage_quantity) AS total_dbus,
  ROUND(SUM(usage_quantity * 0.22), 2) AS estimated_cost_usd
FROM system.billing.usage
WHERE usage_date >= CURRENT_DATE() - INTERVAL 30 DAYS
  AND usage_metadata.warehouse_id IS NOT NULL
GROUP BY warehouse_id, sku_name
ORDER BY total_dbus DESC;

Example 2: Auditing Table Access on Sensitive PII Datasets

-- Audit all users who queried PII-tagged tables during the past week
SELECT 
  event_time,
  user_identity.email AS user_email,
  action_name,
  request_params.full_table_name AS queried_table,
  source_ip_address
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name IN ('getTable', 'readTable')
  AND request_params.full_table_name LIKE '%customer%'
  AND event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
ORDER BY event_time DESC;

Example 3: Finding Long-Running Slow Queries for Tuning

-- Identify slow queries executing for over 60 seconds on SQL Warehouses
SELECT 
  statement_id,
  executed_by,
  statement_text,
  total_duration_ms / 1000.0 AS duration_seconds,
  read_rows,
  read_bytes / (1024 * 1024) AS read_mb
FROM system.query.history
WHERE execution_status = 'FINISHED'
  AND total_duration_ms > 60000
  AND start_time >= CURRENT_DATE() - INTERVAL 7 DAYS
ORDER BY duration_seconds DESC
LIMIT 20;