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

  1. A job reads orders and customers and writes city_revenue
  2. Unity Catalog records the lineage event
  3. Catalog Explorer shows it in the Lineage tab
  4. Need it for many tables or audits? Yes: Query system.access.table_lineage. No: Use the lineage graph in the UI.
  5. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic