Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Column-level lineage

Data quality · Governance, Privacy & Lineage

Column-level lineage

Mediumdata-quality-33
column-level-lineagedata-lineagemetadataimpact-analysis

Question

What is column-level lineage, and why is it more useful than table-level lineage?

Solution

Column-level lineage traces the complete path of every individual column from its original source fields through all intermediate transformations and aggregations to final reporting outputs. While table-level lineage only shows that two tables are connected, column-level lineage identifies the exact columns involved, making schema migration planning and compliance tracking significantly more accurate.

Tracking field transformations down to sources

Table-level lineage is too coarse for modern engineering workflows. Column-level lineage provides precise visibility into data dependencies:

  • Targeted impact analysis: When an upstream engineering team alters or deprecates a source column like orders.tax_rate, table-level lineage flags dozens of downstream tables as impacted. Column-level lineage isolates the exact three downstream models and specific dashboard tiles that compute with tax_rate, preventing wasted engineering effort and unnecessary alerts.
  • Tracking PII propagation: When sensitive customer fields like email addresses or phone numbers enter the pipeline, column-level lineage maps every downstream derivative, including renamed columns, hashed keys, or concatenated strings. This guarantees accurate data privacy audits and prevents accidental exposure in public analytics marts.
  • Debugging metric anomalies: If an executive dashboard reports incorrect margin figures, engineers can trace the specific output calculation back through intermediate SQL CTEs to locate the exact join or arithmetic formula causing the discrepancy.
Upstream: orders.subtotal, orders.discount
    \               /
     v             v
Transformation: silver_orders.net_amount = subtotal - discount
     |
     v
Downstream Mart: fct_monthly_sales.total_net_revenue = SUM(net_amount)

Modern data tools extract and visualize column-level lineage automatically:

  • dbt compiles SQL code and parses abstract syntax trees to trace column dependencies across models.
  • Databricks Unity Catalog observes executed query plans to construct automated column-level graphs.
  • The OpenLineage standard defines open specifications for capturing and sharing column-level transformation events across disparate processing engines.

Operational precision in migrations

Relying solely on table-level lineage leads to over-cautious teams that fear refactoring tables because dependencies appear overwhelming. Column-level lineage removes this ambiguity, allowing engineers to modify or drop unused columns with complete confidence.

PreviousNext