DE Data Lineage

When an analyst discovers an error in a revenue report, the first question is: where did this data come from? Without data lineage, answering that question requires interviewing engineers, reading pipeline code, and tracing data manually through multiple systems — a process that takes days. With data lineage, the answer appears in a visual graph within minutes.

What Is Data Lineage

Data lineage tracks the origin of every piece of data, the transformations it passed through, and the destinations where it landed. It creates an auditable record of the data's entire journey — from the moment it entered the system to the moment an analyst queries it. Lineage answers three questions: Where did this data come from? What happened to it along the way? Where does it go from here?

The Ingredient Traceability Analogy

When a food safety agency discovers contaminated produce in a grocery store, it traces the product back to its origin — which farm, which supplier, which delivery truck, which storage facility. This traceability exists because every step in the supply chain is documented. Data lineage does exactly this for data: every source, every transformation, and every destination leaves a documented record that enables rapid root-cause analysis when something goes wrong.

Column-Level vs Table-Level Lineage

Table-Level Lineage

Table-level lineage tracks which tables feed into which other tables. It shows that the "fact_sales" table in the warehouse was built from the "raw_orders" table in the data lake and the "dim_customer" table from the CRM source.

Table-Level Lineage Diagram:

[MySQL: orders_db.orders] --------\
[PostgreSQL: crm.customers] --------> [S3: raw/orders_enriched.parquet]
[S3: products/catalog.json] ------/         |
                                            v
                               [Snowflake: staging.orders_clean]
                                            |
                                            v
                               [Snowflake: analytics.fact_sales]
                                            |
                          +----------------+----------------+
                          v                v                v
                   [Tableau Dashboard] [Looker Report] [ML Training Data]

Column-Level Lineage

Column-level lineage goes deeper, tracking each individual column through every transformation. It shows that the "revenue_usd" column in "fact_sales" came from the "amount" column in "raw_orders," was converted from INR using an exchange rate from the "fx_rates" table, and was then rounded to two decimal places in the transformation step.

Column-Level Lineage for "revenue_usd" in fact_sales:

raw_orders.amount (INR)
    + fx_rates.usd_inr_rate
    --> (raw_orders.amount / fx_rates.usd_inr_rate)
    --> ROUND(..., 2)
    --> staging.orders_clean.revenue_usd
    --> analytics.fact_sales.revenue_usd

Why Data Lineage Matters

Root Cause Analysis

When an error appears in a dashboard, lineage traces it back to the source in minutes. An engineer navigates the lineage graph upstream from the broken metric, identifying at which transformation stage the value became incorrect. Without lineage, this investigation takes days of manual code reading.

Impact Analysis

When an upstream table changes — a column is renamed, a data type changes, a source system updates its schema — lineage shows every downstream table, view, and report that depends on that change. Engineers assess the full impact before making the change, preventing silent breakages in downstream systems.

Impact Analysis Example:
Question: "If we rename column 'cust_id' to 'customer_id' in raw_customers,
           what breaks?"

Lineage answer (automated):
  Affected tables: 12
  Affected reports: 8
  Affected ML models: 3
  
  Tables requiring changes:
  - staging.customers_clean (references raw_customers.cust_id)
  - analytics.dim_customer (joins on staging.customers_clean.cust_id)
  - analytics.fact_sales (joins on analytics.dim_customer.cust_id)
  - ... 9 more

Regulatory Compliance

Regulations like GDPR require organizations to know where personal data exists and how it is used. Data lineage provides an automated audit trail showing the exact path of customer personal data — from collection through storage, transformation, and serving. When a customer exercises their right to deletion, lineage identifies every system that holds their data.

Trust and Transparency

Analysts who can see where a metric comes from and what transformations it passed through trust that metric more than one that appears from an unknown source. Lineage documentation builds confidence in data assets and reduces the time analysts spend questioning whether numbers are reliable.

How Lineage Gets Captured

Passive Capture

Lineage tools parse SQL code, pipeline definitions, and transformation scripts to automatically extract lineage relationships. dbt automatically generates column-level lineage from its SQL model definitions. Apache Atlas and OpenLineage passively capture lineage from Spark jobs, Airflow DAGs, and dbt runs without requiring engineers to write explicit lineage metadata.

Active Capture

Engineers explicitly define lineage metadata as part of building pipelines. They annotate each step with the source and destination tables and columns. Active capture is more accurate but requires discipline to maintain as pipelines evolve.

Data Lineage Tools

Tool              | Approach           | Best For
------------------|--------------------|------------------------------------
dbt               | SQL model lineage  | ELT transformation pipelines
Apache Atlas      | Enterprise lineage | Hadoop ecosystem
OpenLineage       | Open standard API  | Multi-tool lineage aggregation
Marquez           | OpenLineage-based  | Airflow + Spark lineage
DataHub           | Full data catalog  | Large enterprise data platforms
Alation           | Catalog + lineage  | Analyst-facing documentation

Data Lineage in dbt

dbt automatically builds a lineage graph from the SQL models engineers write. Every model references upstream models with the ref() function. dbt compiles these references into a complete DAG showing exactly how every table in the warehouse was built from every other table.

dbt model: analytics/fact_sales.sql

SELECT
    o.order_id,
    c.customer_id,
    p.product_key,
    o.amount * fx.usd_rate AS revenue_usd
FROM {{ ref('staging_orders') }}       o   -- upstream dependency
JOIN {{ ref('dim_customer') }}         c ON o.customer_id = c.customer_id
JOIN {{ ref('dim_product') }}          p ON o.product_id  = p.product_id
JOIN {{ ref('fx_rates') }}             fx ON o.order_date = fx.rate_date

dbt auto-generates lineage:
fx_rates + staging_orders + dim_customer + dim_product --> fact_sales

Summary

Data lineage tracks the origin, transformation history, and downstream usage of every data asset. Table-level lineage shows which tables depend on which sources. Column-level lineage traces individual fields through every transformation step. Lineage enables fast root-cause analysis, impact assessment before schema changes, regulatory compliance, and analyst trust. Tools like dbt, Apache Atlas, and DataHub automate lineage capture as a natural byproduct of well-structured pipeline code.