6.1 Automated Data Lineage Tracking in Catalog Explorer

6.1 Automated Data Lineage Tracking in Catalog Explorer

DP-750 Exam Focus: Master Unity Catalog's automated lineage capture architecture, distinguishing between table-level and column-level lineage. Understand runtime prerequisites (DBR 11.3 LTS+, Shared or Single User access modes), permissions required to view and traverse lineage graphs, metadata redaction for unauthorized upstream/downstream assets, the 365-day retention lifecycle, and how to perform root-cause and impact analysis.

---

1. Architectural Foundations of Unity Catalog Lineage

In enterprise data lakehouses, establishing data trust, validating regulatory compliance (such as BCBS 239, GDPR, and HIPAA), and diagnosing data quality anomalies require complete visibility into how data originates, transforms, and flows across workloads.

Legacy Apache Spark environments relied on external, third-party governance agents or intrusive code instrumentation (such as custom listeners or manual Spline/Atlas agents) to capture execution telemetry. These approaches suffered from high operational overhead, inconsistent coverage across languages, and significant security vulnerabilities when collecting cluster metadata.

Unity Catalog automated data lineage operates natively at the query planning and execution layer inside Azure Databricks. As Spark parses, optimizes, and executes queries via Catalyst and the Photon engine, Unity Catalog intercepts the logical query plan (LogicalPlan), extracts the source and target relations, and maps exact column-level projection and transformation semantics.

+-----------------------------------------------------------------------------------+
|                    UNITY CATALOG RUNTIME LINEAGE CAPTURE                           |
+-----------------------------------------------------------------------------------+
|                                                                                   |
|  [ SQL Query / PySpark / Scala Notebook / Lakeflow Job / DLT Pipeline / Dashboard]|
|                                         |                                         |
|                                         v                                         |
|                     +---------------------------------------+                     |
|                     |   Catalyst Optimizer & Photon Engine  |                     |
|                     |   - Analyzed Logical Plan Extraction  |                     |
|                     |   - Column Projection & Expression Map|                     |
|                     +---------------------------------------+                     |
|                                         |                                         |
|                                         v                                         |
|                     +---------------------------------------+                     |
|                     |   Unity Catalog Metastore Service     |                     |
|                     |   - Aggregates Upstream / Downstream  |                     |
|                     |   - Applies RBAC / Permission Masks   |                     |
|                     |   - Persists Lineage Graph (365 Days) |                     |
|                     +---------------------------------------+                     |
|                                         |                                         |
|                  +----------------------+----------------------+                  |
|                  |                                             |                  |
|                  v                                             v                  |
|     +---------------------------+                +---------------------------+    |
|     |  Catalog Explorer UI      |                |  system.lineage Tables    |    |
|     |  Interactive Visual Graph |                |  Databricks SQL Analytics |    |
|     +---------------------------+                +---------------------------+    |
+-----------------------------------------------------------------------------------+

Key Characteristics of Native Lineage Capture

• Zero Code Instrumentation: Engineers do not modify existing SQL queries, PySpark scripts, or ETL pipelines. Lineage is captured automatically during standard job and notebook execution.
• Polyglot Support: Captures operations written in ANSI SQL, Python (PySpark and Pandas on Spark), Scala, and R.
• Real-Time Aggregation: Lineage events are streamed to the Unity Catalog governance plane in near-real-time as tasks complete, instantly reflecting changes in Catalog Explorer.
• Centralized Account Scope: Because Unity Catalog spans multiple regional workspaces attached to the same metastore, lineage tracks operations across workspaces (e.g., an ingestion job in a Dev workspace writing to a table queried by a dashboard in a Prod workspace).

---

2. Table-Level vs. Column-Level Lineage

Unity Catalog captures dependencies at two distinct granularities: Table-Level Lineage and Column-Level Lineage.

Table-Level Lineage

Table-level lineage tracks dependencies between datasets as whole entities. It identifies:

• Source Tables & Views: The upstream inputs queried by a SELECT, MERGE, INSERT, COPY INTO, or DataFrame read operation.
• Target Tables & Views: The downstream table or view populated or modified by CREATE TABLE AS SELECT (CTAS), INSERT INTO, MERGE INTO, CREATE VIEW, or DataFrame write operations.
• External Data Paths: Ingestion from cloud storage locations (ADLS Gen2 URIs via Unity Catalog external locations or volumes).

-- Example 1: Table-to-Table CTAS Operation
CREATE OR REPLACE TABLE gold_sales.analytics.monthly_revenue AS
SELECT 
    date_trunc('month', o.order_date) AS order_month,
    c.country,
    SUM(o.net_amount) AS total_revenue
FROM silver_sales.orders.cleaned_orders o
INNER JOIN silver_crm.customers.dim_customer c
    ON o.customer_id = c.customer_id
GROUP BY 1, 2;

In this query, Unity Catalog records silver_sales.orders.cleaned_orders and silver_crm.customers.dim_customer as upstream parents of gold_sales.analytics.monthly_revenue.

Column-Level Lineage

Column-level lineage provides deep semantic tracking by recording how individual target columns are derived from specific upstream source columns, including transformations and expressions:

• Direct Mapping (1-to-1): Direct projections where target column country originates directly from source column c.country.
• Composite Expressions (Many-to-1): Transformations combining multiple source columns (e.g., concat(first_name, ' ', last_name) AS full_name) link full_name to both first_name and last_name.
• Aggregations & Functions: Expressions such as SUM(o.net_amount) link total_revenue directly to the net_amount source column in cleaned_orders.
• Conditional Logic: CASE WHEN, COALESCE, and scalar UDFs link target columns to all referenced input columns evaluated within the conditional branches.

Exam Tip: Indirect column dependencies—such as columns referenced purely in a WHERE filter clause (e.g., WHERE status = 'COMPLETED') without appearing in the SELECT projection—do not create column-level lineage for target output columns, but the underlying table dependency is captured at the table level.

---

3. End-to-End Asset Provenance and Impact Analysis

Unity Catalog lineage does not stop at tables and columns. It connects the entire data engineering ecosystem, providing end-to-end traceability across compute jobs, orchestration pipelines, user notebooks, and consumption endpoints.

                                END-TO-END PROVENANCE GRAPH

  [ ADLS Gen2 Ingest ] 
          | (Auto Loader / cloudFiles)
          v
  [ Table: bronze.iot.telemetry_raw ]
          |
          +---> [ Lakeflow Pipeline Task / Job: 902184 ]
          v
  [ Table: silver.iot.telemetry_curated ]
          |
          +---> [ Notebook: /Shared/Analytics/DailyMetrics ]
          v
  [ Table: gold.iot.device_performance ]
          |
          +-----------------------+-----------------------+
          |                                               |
          v                                               v
  [ Databricks SQL Dashboard ]                  [ MLflow Model: PredictiveMaintenance ]
  "IoT Fleet Health Executive"                  Registered in Unity Catalog

Upstream Root-Cause Analysis (Backward Lineage)

When an anomaly or data quality defect is detected on a Gold reporting table or Databricks SQL Dashboard, data engineers use backward lineage to trace the issue upstream:

1. Open the target table in Catalog Explorer and select the Lineage tab.
2. Expand upstream nodes to trace the exact lineage path back through intermediate Silver tables, Bronze ingestion tables, and the specific Lakeflow Job or notebook that performed each write.
3. Identify the exact commit or query that introduced corrupted or missing values.

Downstream Impact Analysis (Forward Lineage)

Before modifying a schema (e.g., renaming a column, altering data types, or deprecating a legacy table), data engineers perform forward lineage analysis:

1. Inspect downstream dependencies connected to the table or column in Catalog Explorer.
2. Identify all downstream consumers: dependent views, machine learning models, downstream ETL pipelines, Lakeflow Jobs, and Databricks SQL Dashboards.
3. Proactively notify affected downstream stakeholders or update dependent transformations before releasing schema changes, preventing production job failures.

---

4. Runtime Prerequisites and Supported Workloads

To ensure automated lineage capture occurs, compute clusters and queries must meet specific environment constraints:

Technical Prerequisites Matrix

Workload Lineage Capture Scenarios

• Lakeflow Spark Declarative Pipelines (Delta Live Tables): Lineage is captured automatically between Streaming Tables and Materialized Views defined across pipeline stages.
• Delta Sharing: When data is shared via Delta Sharing, Unity Catalog captures the share as an egress endpoint, providing auditability for external data distributions.
• Machine Learning Models: Models trained using PySpark/Feature Store tables and registered in Unity Catalog automatically capture input feature lineage.

---

5. Permissions and Security Governance in Lineage Visualization

Unity Catalog enforces strict security boundaries and access controls when displaying lineage in Catalog Explorer or returning lineage records via APIs.

Permissions Required to View Lineage

To view the lineage tab of any object in Catalog Explorer, a user must possess:

1. USE CATALOG on the parent catalog.
2. USE SCHEMA on the parent schema.
3. SELECT privilege on the specific table or view.

Metadata Masking and Privacy Protection

In enterprise environments, a data engineer might have permission to view a Gold reporting table (gold.finance.revenue), but might not possess read permissions on the raw upstream Bronze ingestion table (bronze.hr.payroll_raw) that contributed to an aggregated metric.

Unity Catalog handles cross-privilege lineage traversing through Metadata Redaction:

                                   USER PERMISSION VIEW

  [ bronze.hr.payroll_raw ]  ----->  [ silver.finance.comp ]  ----->  [ gold.finance.revenue ]
         (NO SELECT)                       (WITH SELECT)                     (WITH SELECT)
              |                                  |                                 |
              v                                  v                                 v
      [ Redacted Node ]              [ Visible Lineage Node ]          [ Target Lineage Node ]
      "You do not have               "silver.finance.comp"             "gold.finance.revenue"
       permission to view             Schema & Columns Visible          Full Details Visible
       this object's metadata"

• Redacted Nodes: If a user lacks SELECT or BROWSE permissions on an upstream or downstream object in the graph, Unity Catalog renders the node as Masked / Redacted.
• The BROWSE Privilege: The BROWSE privilege allows users to see metadata (such as catalog, schema, and table names) and discover the existence of objects in the lineage graph without granting access to read the underlying row data.
• Data Exfiltration Prevention: Column-level lineage graphs hide column names and transformation logic for redacted objects to prevent unauthorized inference of proprietary business logic or sensitive data definitions.

---

6. Lineage Retention and Programmatic Querying

Lineage metadata is retained in the Unity Catalog metastore for a rolling window of 365 days (1 year). This retention policy ensures organizations have sufficient historical depth to meet annual governance and auditing mandates.

Accessing Lineage Data

Data engineers can consume lineage through three primary interfaces:

1. Catalog Explorer UI: High-performance, interactive graphical visualization with node filtering, column expansion, and upstream/downstream depth toggles.
2. System Tables (system.lineage): SQL-queryable Delta tables containing structured lineage records across the entire account (detailed extensively in Section 6.2).
3. Databricks REST API: The Lineage REST API allows external data catalog tools (such as Microsoft Purview, Collibra, or Alation) to synchronize and export lineage graphs programmatically.

# Example REST API Request to fetch table lineage
curl -X GET -H "Authorization: Bearer <token>" \
  "https://<databricks-instance>/api/2.0/lineage-tracking/table-lineage?table_name=gold_sales.analytics.monthly_revenue"