Before you drop, rename or change a table: impact analysis with Unity Catalog lineage system tables

#Goal

Someone wants to drop prod.silver.orders, rename a column, or change its type. You need a list of everything that will break: jobs, Lakeflow pipelines, dashboards, Genie spaces, notebooks and the people or BI tools running ad hoc queries. Unity Catalog records this in two system tables:

• system.access.table_lineage: one row per read or write event on a table or path.
• system.access.column_lineage: the same, per column.

Both keep a rolling one year of data. Catalog Explorer and the lineage API keep lineage captured after 1 September 2024 for longer.

#Prerequisites

The access system schema must be enabled, and you need read access on it. An account admin or metastore admin grants it:

GRANT USE CATALOG ON CATALOG system TO `platform-ops`;
GRANT USE SCHEMA  ON SCHEMA  system.access TO `platform-ops`;
GRANT SELECT ON TABLE system.access.table_lineage  TO `platform-ops`;
GRANT SELECT ON TABLE system.access.column_lineage TO `platform-ops`;

Run the queries below on a SQL warehouse or Unity Catalog-enabled compute.

#Step 1: find direct consumers of the table

Filter by source_table_full_name to see who reads the table. Group by entity so you get one row per job, pipeline or dashboard instead of one row per run.

SELECT
  entity_type,
  entity_metadata.job_info.job_id                   AS job_id,
  entity_metadata.dlt_pipeline_info.dlt_pipeline_id AS pipeline_id,
  entity_metadata.notebook_id                       AS notebook_id,
  entity_metadata.dashboard_id                      AS dashboard_id,
  entity_metadata.genie_space_id                    AS genie_space_id,
  target_table_full_name,
  created_by,
  MAX(event_time) AS last_seen,
  COUNT(*)        AS events
FROM system.access.table_lineage
WHERE source_table_full_name = 'prod.silver.orders'
  AND event_date >= current_date() - INTERVAL 90 DAYS
GROUP BY ALL
ORDER BY last_seen DESC;

How to read the result:

Tip

Use 90 days, not 7. Month-end and quarter-end jobs only show up if your window covers them.

#Step 2: turn IDs into names and owners

Job IDs mean nothing in a change ticket. Join to system.lakeflow.jobs, which keeps a row per job change, and take the latest row for each job:

WITH consumers AS (
  SELECT DISTINCT workspace_id, entity_metadata.job_info.job_id AS job_id
  FROM system.access.table_lineage
  WHERE source_table_full_name = 'prod.silver.orders'
    AND event_date >= current_date() - INTERVAL 90 DAYS
    AND entity_metadata.job_info.job_id IS NOT NULL
),
jobs AS (
  SELECT workspace_id, job_id, name, creator_id, run_as, delete_time
  FROM system.lakeflow.jobs
  QUALIFY ROW_NUMBER() OVER (PARTITION BY workspace_id, job_id ORDER BY change_time DESC) = 1
)
SELECT c.workspace_id, c.job_id, j.name, j.run_as, j.creator_id, j.delete_time
FROM consumers c
LEFT JOIN jobs j USING (workspace_id, job_id)
ORDER BY j.name;

Jobs with a non-null delete_time are gone and can be ignored. A NULL name usually means the lakeflow schema isn't enabled or the job is in a workspace you can't see.

For readers with no Databricks entity, statement_id joins to query history when the query ran on a SQL warehouse or serverless compute:

SELECT q.executed_by, q.client_application, COUNT(*) AS queries, MAX(q.start_time) AS last_query
FROM system.access.table_lineage l
JOIN system.query.history q ON q.statement_id = l.statement_id
WHERE l.source_table_full_name = 'prod.silver.orders'
  AND l.event_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY queries DESC;

client_application tells you whether the readers are BI tools, notebooks or drivers. Values vary by client version.

#Step 3: follow the chain downstream

A change to silver.orders also breaks every table built from gold.daily_sales, which is built from silver.orders. Walk the graph with a short Python loop in a notebook:

from pyspark.sql import functions as F

def downstream(table, days=90, max_depth=6):
    lineage = (spark.read.table("system.access.table_lineage")
               .where(F.col("event_date") >= F.date_sub(F.current_date(), days))
               .where(F.col("target_table_full_name").isNotNull())
               .select("source_table_full_name", "target_table_full_name")
               .distinct()
               .cache())
    seen, frontier, depth = set(), {table}, 0
    while frontier and depth < max_depth:
        nxt = {r.target_table_full_name for r in
               lineage.where(F.col("source_table_full_name").isin(list(frontier))).collect()}
        nxt -= seen | {table}
        seen |= nxt
        frontier, depth = nxt, depth + 1
    return sorted(seen)

downstream("prod.silver.orders")

Run Step 1 against each table it returns to collect the dashboards and jobs at the end of the chain.

#Step 4: check column-level impact

For a column rename, drop or type change, column_lineage is more precise. It records reads even when nothing is written, so dashboard and query usage is included.

SELECT
  source_column_name,
  target_table_full_name,
  target_column_name,
  entity_type,
  entity_id,
  created_by,
  MAX(event_time) AS last_seen
FROM system.access.column_lineage
WHERE source_table_full_name = 'prod.silver.orders'
  AND source_column_name IN ('customer_email', 'order_ts')
  AND event_date >= current_date() - INTERVAL 90 DAYS
GROUP BY ALL
ORDER BY source_column_name, last_seen DESC;

A column that appears in no rows over 90 days is a good candidate for removal. Check what lineage does not capture first, though.

#What lineage misses

For a path-based or external table:

DESCRIBE DETAIL prod.silver.orders;  -- copy the location value

SELECT entity_type, entity_id, created_by, MAX(event_time) AS last_seen
FROM system.access.table_lineage
WHERE source_path = 'abfss://silver@stlakeprod.dfs.core.windows.net/orders'
GROUP BY ALL;

For older history, the lineage REST API can be called through the Databricks CLI:

databricks api get /api/2.0/lineage-tracking/table-lineage \
  --json '{"table_name": "prod.silver.orders", "include_entity_lineage": true}'

#Make the change safely

1. Mark the table as deprecated so people see it in Catalog Explorer:

   ALTER TABLE prod.silver.orders SET TAGS ('lifecycle' = 'deprecated', 'remove_after' = '2026-12-31');
   COMMENT ON TABLE prod.silver.orders IS 'Deprecated: use prod.silver.orders_v2. Removal 2026-12-31.';
   

2. Send the owner list from Step 2 to each team, with the date.

3. For a rename, leave a view under the old name for one release cycle:

   CREATE VIEW prod.silver.orders AS SELECT * FROM prod.silver.orders_v2;  -- after renaming the table
   

4. Re-run Step 1 a week before the date. New consumers show up here first.

Caution

DROP TABLE on a managed table deletes the data after the retention window, and UNDROP only works within that window. Rename the table or revoke access first, and drop it only after a quiet period with no lineage events.

#Prevent it next time

• Schedule the Step 1 query as a SQL alert on critical tables and send new consumers to the table owner.
• Require a lineage export (Steps 1–4) in change tickets for schema changes on silver and gold tables.
• Put reporting tools on service principals with clear names, so the created_by in lineage tells you which system is reading.
• Finish the move off hive_metastore and path-based access. Lineage is only as complete as your Unity Catalog coverage.