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

Part 3 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, 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 LineageData LineageThe record of where a piece of data came from and everything it passed through, from source tables to the dashboards, models and AI answers built on it. 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:

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:

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's grants and part 3'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 puts the shop's catalog together in 1 script.