# Data lineage and audit logs in Unity Catalog, with system tables

Unity Catalog records lineage on its own for every query run on Databricks, down to the column: which tables, notebooks, jobs and dashboards read and write each table. You see it in Catalog Explorer, on a table's Lineage tab, or query it in system.access.table_lineage and system.access.column_lineage. Who did what is in the audit log, system.access.audit. Lineage is lost when a table is renamed, and a table read by its path rather than its name gets no column lineage.

- Page: https://tecyfy.com/data-engineering/course/unity-catalog/lineage-and-audit
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 5: Unity Catalog and governance, Lineage and audit logs
- Parts of this module: Catalogs, schemas and the 3-part name (https://tecyfy.com/data-engineering/course/unity-catalog); Privileges and grants (https://tecyfy.com/data-engineering/course/unity-catalog/privileges); Masking personal data (https://tecyfy.com/data-engineering/course/unity-catalog/personal-data); Lineage and audit logs (https://tecyfy.com/data-engineering/course/unity-catalog/lineage-and-audit); Build it: the shop's catalog (https://tecyfy.com/data-engineering/course/unity-catalog/build-it)

At the end of this part you can:

- Find what feeds a table and what reads it, in Catalog Explorer and with SQL on the lineage system tables
- Find who read a table, and when, in the audit log
- Say what lineage misses, and write pipelines that keep it

[Part 3](/data-engineering/course/unity-catalog/personal-data) kept the emails from the wrong readers. 2 questions remain once data is shared: where did this number come from, and who has read the customers table? Unity Catalog answers both from records it keeps on its own. Neither exists on your computer: Delta's history, from [module 4](/data-engineering/course/delta-lake-and-iceberg), lists the changes to 1 table, not who read it or where its data went, so this part is for Databricks.

## Lineage is recorded for you

Every query that runs on Databricks against Unity Catalog tables adds to the [Data Lineage](https://tecyfy.com/concepts/data-lineage) graph, down to the column, with no setup. When a notebook reads `silver.orders` and `silver.customers` and writes `gold.customer_spend`, Unity Catalog records the 2 sources, the target, the notebook that ran it and which columns fed which.

To see it, open a table in **Catalog** in the sidebar and choose the **Lineage** tab. **See lineage graph** draws the tables upstream and downstream; choose a column to follow it alone. The tab also lists the jobs, notebooks and dashboards that read or write the table. You need at least BROWSE on the table's catalog to see its lineage.

Lineage answers the 2 questions a data team gets every week. Upstream: a number on a dashboard looks wrong, so which tables and jobs produced it? Downstream: you want to drop a column, so which tables and dashboards will break?

## Lineage in SQL

The same records are in system tables, so a script can ask them. What has read the customers table lately:

```sql
SELECT target_table_full_name, entity_type, created_by, event_time
FROM system.access.table_lineage
WHERE source_table_full_name = 'shop.silver.customers'
ORDER BY event_time DESC
LIMIT 20;
```

Swap source for target to see what feeds a table, and use `system.access.column_lineage` for columns. The system tables keep a rolling year; the graph in Catalog Explorer keeps lineage for longer.

System tables live in the `system` catalog, and their schemas must be enabled for the account. Users with both the account admin and metastore admin roles can read them; anyone else needs USE CATALOG, USE SCHEMA and SELECT on them, granted like any other table.

## What lineage misses

Lineage follows names, so it breaks where a pipeline stops using them:

- **Paths.** Column lineage cannot be captured when the source or the target is referenced by its path, such as a folder of files read with `spark.read.parquet(...)`. Load files into a bronze table once, then build every later table from tables by name, and column lineage runs from bronze on.
- **Renames.** Lineage is not kept for a renamed catalog, schema, table, view or column. Choose names you can keep.
- **Old interfaces.** RDDs and global temporary views are not captured. DataFrames and SQL are.

## Who read what: the audit log

Lineage says where data went; the audit log says who did what. It is the system table `system.access.audit`, 1 row per event, with the time, the user, the service and the action. Unity Catalog's events have the service name `unityCatalog`. When a query is given access to a table, Databricks logs `generateTemporaryTableCredential` with the table's full name, so this lists who read the customers in the last 30 days:

```sql
SELECT event_time, user_identity.email, request_params['operation'] AS operation
FROM system.access.audit
WHERE event_date >= date_sub(current_date(), 30)
  AND service_name = 'unityCatalog'
  AND action_name = 'generateTemporaryTableCredential'
  AND request_params['table_full_name'] = 'shop.silver.customers'
ORDER BY event_time DESC;
```

Filter on `event_date` first: the table is large, and Databricks recommends the date column over the time for speed. The audit log keeps a year, like lineage.

Together with [part 2](/data-engineering/course/unity-catalog/privileges)'s grants and [part 3](/data-engineering/course/unity-catalog/personal-data)'s masks, that is governance in practice: decide who may read, protect what they should not see, and be able to show afterwards who did. [Part 5](/data-engineering/course/unity-catalog/build-it) puts the shop's catalog together in 1 script.

## Check yourself

1. A job reads files from a cloud folder by their path, cleans them, and writes the table silver.orders by name. A colleague asks which source column each column of silver.orders comes from. What does lineage show?

   A. Column lineage from the source folder's columns to silver.orders, since lineage is recorded for every query
   B. Nothing at all, because the job wrote through a path
   C. The job and the table, but no column lineage from the folder, because column lineage cannot be captured when the source is referenced by path
   D. Only the job's notebook, never the tables

### Answers

1. C. Unity Catalog records lineage for queries on Databricks, but column lineage needs both ends to be named objects; it cannot be captured when the source or the target is a path. Loading the files into a bronze table once, and building later tables from tables by name, gives column lineage from bronze on.

## Questions

### How do I see data lineage in Databricks?

Open the table in Catalog Explorer and choose the Lineage tab, then See lineage graph for the interactive view; choose a column to see the columns it comes from and feeds. In SQL, query system.access.table_lineage and system.access.column_lineage, which keep a rolling year. You need at least BROWSE on the table's catalog.

### How do I find out who read a table in Databricks?

Query the audit log, system.access.audit. Unity Catalog's events have the service name unityCatalog, and the action generateTemporaryTableCredential is logged when a query is given access to a table, with the table's full name in request_params. Filter on event_date for speed. The system schemas must be enabled, and other users need privileges on them from an admin.

## Sources

- [Data lineage in Unity Catalog](https://docs.databricks.com/aws/en/data-governance/unity-catalog/data-lineage)
- [Lineage system tables reference](https://docs.databricks.com/aws/en/admin/system-tables/lineage)
- [Audit log system table reference](https://docs.databricks.com/aws/en/admin/system-tables/audit-logs)
- [Audit log reference](https://docs.databricks.com/aws/en/admin/account-settings/audit-logs)
- [Monitor account activity with system tables](https://docs.databricks.com/aws/en/admin/system-tables/)