Lineage, audit logs and system tables

Governance without proof is a policy document

A grant model says who should have access. Auditors, security teams and your own incident reviews ask a different question: who actually used it, what changed, and where did this number come from? Unity Catalog answers with two kinds of evidence: lineage, which records how data flows, and audit logs, which record who did what. Both are queryable as system tables.

In this lesson we use lineage to assess impact, audit logs to answer access questions, and system tables to build a small set of governance checks that run on a schedule. We do not cover shipping logs to an external SIEM.

Lineage

The documentation says Unity Catalog captures lineage automatically for queries run on Databricks, and aggregates it across all workspaces attached to the metastore. It tracks tables, views, columns, ML model versions and file paths. Notebooks, jobs and dashboards appear as consumers of a table. In Catalog Explorer, a table's Lineage tab lists downstream jobs and dashboards, and the lineage graph shows column-level links.

You do not instrument anything. If your pipeline reads bronze.orders_raw and writes silver.orders, the link exists.

Three uses pay for the effort:

1. Impact analysis before a change. "If I rename this column, which tables, jobs and dashboards break?" Look at downstream lineage before you merge.
2. Root cause during an incident. "This gold number is wrong." Walk upstream to the first table where the value diverges.
3. Evidence for privacy questions. "Where does this personal data field end up?" Column lineage answers it, and it is the base of a record of processing.

Query it

Lineage is also available in the system.access.table_lineage and system.access.column_lineage system tables, so you can analyse it in SQL. The documentation says they keep a rolling window of 395 days by default, while the lineage shown in Catalog Explorer is kept indefinitely, starting from 1 September 2024.

-- Schematic: which targets are built from a given source table in the last 90 days?
-- Confirm the exact column names in your workspace with DESCRIBE system.access.table_lineage.
SELECT DISTINCT target_table_full_name
FROM   system.access.table_lineage
WHERE  source_table_full_name = 'prd_finance.silver.invoices'
  AND  event_date >= current_date() - INTERVAL 90 DAYS
  AND  target_table_full_name IS NOT NULL;

Know the limits

The documentation lists limitations that change what you can claim:

• Lineage captured before 1 September 2024 is not available.
• Jobs submitted through the Jobs API runs submit request or the spark submit task type show no link to the job run.
• Lineage is not preserved when catalogs, schemas, tables, views or columns are renamed.
• RDDs, global temporary views and information_schema tables are not captured.
• Lineage through temporary tables ignores UPDATE, DELETE and MERGE INTO changes.
• Column lineage is not shown for path-based references, although the system table records them.
• User-defined functions are captured only at table level.

Use lineage as strong evidence, not as a guarantee. A missing edge means "not captured", and it does not prove that no flow exists. When you build a record of processing for a regulator, say how it was produced.

Audit logs

Audit events land in system.access.audit. The documentation describes the columns you use most:

Two facts matter when you rely on it. Most audit logs are only available in the workspace's region, and account-level events record workspace_id as 0. And when a workspace is deleted, its audit events older than 14 days are removed from the table, so export what your retention policy requires before you decommission one.

A starting query, built from the documented columns:

-- Schematic: who read a table in the last 7 days, and did the request succeed?
-- Action names and request_params keys vary by event type: inspect a few rows first.
SELECT event_time,
       user_identity.email,
       action_name,
       response.statusCode
FROM   system.access.audit
WHERE  event_date >= current_date() - INTERVAL 7 DAYS
  AND  service_name = 'unityCatalog'
  AND  action_name  = 'getTable'
ORDER  BY event_time DESC;

Read a few raw rows before you write a filter. The values of action_name and the keys inside request_params depend on the event, and your query should be based on what your workspace actually emits.

Questions the evidence answers

The last row feeds the quarterly access review from lesson 3: remove what is never used.

Governance checks as code

Turn the questions into scheduled queries, owned like any pipeline, with alerts for hits. A short list to start with:

• Direct grants to users. List grants whose principal is a person and not a group. The target is zero.
• Owners that are people. Same check on ownership.
• Access to sensitive tables by non-members. Compare audit reads on sensitive tables with the allowed groups.
• Changes to the security schema. Alert on any change to filter and mask functions.
• Unusual volume. A principal that reads far more than usual.

Run these in the platform repository, deploy them through the bundle, and route alerts to the platform owner group. This also demonstrates governance to a client: you can show the checks, the last run and the alerts. Our case on self-service data onboarding at RIVM describes how governed onboarding scales when the controls are part of the platform.

Retention and privacy of the evidence

Audit logs and lineage hold personal data, such as e-mail addresses of users. Apply our privacy rules to your own evidence: restrict who can read the tables, set a retention period, and export only what you need. The audit table is among the most sensitive data in the platform.

Common mistakes

• Reading lineage as complete. Check the limitations.
• Filtering on event_time instead of event_date. Slow queries.
• Writing a query before looking at the data. Event shapes vary.
• Running checks by hand. Schedule them and alert.
• Forgetting regional scope. Audit logs are mostly per region.

Summary

Use lineage for impact, root cause and privacy questions, and audit logs for access questions. Query the system tables, respect the documented limits, and turn your governance rules into scheduled checks with owners.

Next lesson: sharing data with other organisations without losing control.