Lineage & system tables
Databricks · Unity Catalog & Governance
Theory
Unity Catalog automatically captures data lineage: which tables, columns, notebooks, jobs, pipelines and dashboards read from and write to each table. You do not add any code; queries on Unity Catalog-enabled compute are tracked down to column level. The Lineage tab in Catalog Explorer shows upstream sources and downstream consumers. For example, the lineage of main.sales.city_revenue shows it was built from orders and customers by the job orders_nightly and feeds the Sales dashboard.
System tables are Databricks-managed tables in the system catalog that record operational data for your account: system.access.audit (audit events), system.access.table_lineage and system.access.column_lineage (lineage events), system.billing.usage (DBU usage), system.query.history (queries) and compute and job tables. By default only admins can read them; admins grant USE CATALOG, USE SCHEMA and SELECT to others.
Why we use it
When a number looks wrong or a column must change, you need to know where data came from and what will break. Lineage answers that instantly, and system tables let you audit access, track cost and monitor usage with plain SQL.
When to use it
Use the Lineage tab for impact analysis before changing or dropping a table. Use system tables for audits, cost dashboards and usage reports. Lineage is captured only for work on Unity Catalog; legacy hive_metastore tables are not covered.
How it works
- A job reads orders and customers and writes city_revenue
- Unity Catalog records the lineage event
- Catalog Explorer shows it in the Lineage tab
- Need it for many tables or audits? Yes: Query system.access.table_lineage. No: Use the lineage graph in the UI.
- Change tables knowing exactly what depends on them
Syntax
Catalog → table → Lineage tab → See lineage graph
system.access.table_lineage | system.access.column_lineage
system.access.audit | system.billing.usage | system.query.history
GRANT SELECT ON SCHEMA system.access TO `auditors`Example
-- What reads from main.sales.orders, and from where?
SELECT target_table_full_name,
entity_type,
COUNT(*) AS events,
MAX(event_time) AS last_seen
FROM system.access.table_lineage
WHERE source_table_full_name = 'main.sales.orders'
AND event_date >= current_date() - INTERVAL 30 DAYS
GROUP BY target_table_full_name, entity_type
ORDER BY events DESC;
-- Who queried the customers table recently?
SELECT event_time, user_identity.email, action_name
FROM system.access.audit
WHERE request_params.full_name_arg = 'main.sales.customers'
ORDER BY event_time DESC
LIMIT 20;Key points
- Lineage is automatic for Unity Catalog tables, including column-level lineage
- The Lineage tab shows upstream tables and downstream tables, jobs, notebooks and dashboards
- system.access.table_lineage stores lineage events you can query
- System tables live in the system catalog; by default only admins can read them
- Use lineage for impact analysis before renaming or dropping columns