Data Lineage: Tracing the Journey of Your Data

When something goes wrong with a report, how do you trace the problem back to its source? When a compliance auditor asks where a particular number came from, can you answer? When you need to change a calculation, which downstream reports will be affected?

These questions are answered by data lineage. Lineage tracks the flow of data from origin to destination, capturing every transformation, aggregation, and derivation along the way.

Lineage is one of the most valuable forms of metadata a data platform can have. It is also one of the most commonly neglected.

Why Lineage Matters

Lineage serves three critical functions:

Impact Analysis: Before changing a calculation or dropping a column, you need to know what will break. Lineage tells you exactly which reports, dashboards, and downstream tables depend on this data.

Root Cause Analysis: When a report shows unexpected numbers, lineage lets you trace backward through every transformation to find where things went wrong.

Compliance and Audit: Regulated industries must explain where numbers came from and how they were calculated. Lineage provides the evidence.

Without lineage, you are working blind. Changes are risky because you cannot fully understand the impact. Debugging is slow because you trace through code manually. Compliance is costly because you rebuild lineage evidence retroactively.

When to Use Lineage

Use Case	Best Lineage Level	Recommended Approach
Compliance and audit reporting	Table or Column	OpenLineage + data catalog
Impact analysis before schema changes	Column	Column-level graph database
Root cause investigation	Column	Column-level with transformation expressions
Debugging data quality issues	Row	Row-level with batch tracking
CI/CD validation of pipeline changes	Table	Automated ETL capture

Lineage is worth the overhead when any of these conditions hold:

• You have more than 10 pipelines feeding critical reports
• You operate in a regulated industry (finance, healthcare, government)
• Your schema changes more than once per month
• You have more than one team contributing to the data platform

When not to use lineage:

• Early-stage data platforms with fewer than 5 tables and one ETL job
• Read-only one-time data migrations with no ongoing maintenance
• Proof-of-concept systems that will be replaced within 3 months
• Teams without bandwidth to maintain lineage instrumentation

Levels of Lineage Granularity

Lineage can be captured at different levels of detail. The right level depends on your use case.

Table-Level Lineage

Table-level lineage tracks which tables flow to which other tables. This is the simplest form and often sufficient for impact analysis.

-- Table-level lineage registry
CREATE TABLE table_lineage (
    lineage_id INT PRIMARY KEY AUTO_INCREMENT,
    source_table VARCHAR(200) NOT NULL,
    target_table VARCHAR(200) NOT NULL,
    etl_job_name VARCHAR(200),
    load_frequency VARCHAR(50),
    last_loaded TIMESTAMP,
    FOREIGN KEY (source_table) REFERENCES tables(table_id),
    FOREIGN KEY (target_table) REFERENCES tables(table_id)
);

-- Example records
INSERT INTO table_lineage (source_table, target_table, etl_job_name)
VALUES
    ('staging.customers', 'warehouse.dim_customer', 'customer_etl'),
    ('staging.orders', 'warehouse.fact_orders', 'order_etl'),
    ('warehouse.fact_orders', 'reports.daily_sales', 'sales_report_etl');

Table-level lineage answers: “Does this table depend on that table?” It does not answer: “Does this specific column depend on that specific column?”

Column-Level Lineage

Column-level lineage tracks dependencies at the column level. This is more detailed and enables more precise impact analysis.

-- Column-level lineage
CREATE TABLE column_lineage (
    lineage_id INT PRIMARY KEY AUTO_INCREMENT,
    source_table VARCHAR(200),
    source_column VARCHAR(100),
    target_table VARCHAR(200),
    target_column VARCHAR(100),
    transformation_type VARCHAR(50),  -- DIRECT, COMPUTED, AGGREGATED
    transformation_expression TEXT,
    FOREIGN KEY (target_table, target_column)
        REFERENCES columns(table_id, column_id)
);

Column-level lineage tells you that warehouse.customer_lifetime_value is computed from staging.orders.total_amount summed over staging.orders.customer_id.

Row-Level Lineage

Row-level lineage tracks individual records. This is more expensive to store but necessary for certain compliance use cases.

-- Row-level lineage (simplified)
CREATE TABLE row_lineage (
    lineage_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    source_system VARCHAR(100),
    source_record_id VARCHAR(200),
    target_system VARCHAR(100),
    target_record_id VARCHAR(200),
    load_timestamp TIMESTAMP,
    batch_id VARCHAR(50)
);

Row-level lineage can answer: “Show me every record in the warehouse that was derived from this source record” or “What source records contributed to this specific output record?”

Capturing Lineage Automatically

Manually maintaining lineage is error-prone and quickly becomes stale. Automatic capture is the goal.

ETL-Based Lineage Capture

Modern ETL platforms like Apache Spark, Airflow, and dbt can capture lineage automatically.

# Example: lineage capture with Apache Spark
from pyspark import SparkConf
from pyspark.sql import SparkSession

class LineageCaptureSparkSession(SparkSession):
    """Spark session that captures lineage."""

    def __init__(self):
        super().__init__()
        self.lineage_tracker = LineageTracker()

    def _capture_read_operation(self, table_name):
        """Track when a table is read."""
        self.lineage_tracker.record_operation(
            operation_type='READ',
            source=(table_name, None),
            target=(self.current_job, self.current_output)
        )

    def _capture_write_operation(self, output_table):
        """Track when a table is written."""
        self.lineage_tracker.record_operation(
            operation_type='WRITE',
            source=(self.current_input, None),
            target=(output_table, None)
        )

    def _capture_transformation(self, expression):
        """Track column-level transformations."""
        self.lineage_tracker.record_operation(
            operation_type='TRANSFORM',
            source=(self.current_input, self.read_columns),
            target=(self.current_output, self.written_columns),
            expression=expression
        )

dbt Lineage

dbt captures lineage automatically from the DAG defined by ref() relationships.

# dbt lineage is captured from model dependencies
# models/staging/stg_orders.sql
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM raw.orders

# models/marts/fact_orders.sql
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM {{ ref('stg_orders') }}

The dbt DAG IS the lineage graph. Lineage platforms like dbt-powered lineage extract this DAG and make it queryable.

# Get lineage from dbt
dbt ls --output json | jq '.[] | select(.resource_type == "model")'

Change Data Capture Lineage

CDC events can carry lineage information through the pipeline.

{
  "event_type": "UPDATE",
  "source_system": "oracle_erp",
  "source_table": "orders",
  "source_record_id": "12345",
  "target_system": "snowflake",
  "target_table": "staging.orders",
  "timestamp": "2026-03-27T10:00:00Z",
  "changes": {
    "status": { "old": "pending", "new": "shipped" },
    "shipped_date": { "old": null, "new": "2026-03-27" }
  },
  "lineage_metadata": {
    "batch_id": "batch-2026-03-27-001",
    "etl_job": "orders_cdc_job"
  }
}

Lineage Standards

Several standards exist for representing lineage data.

OpenLineage

OpenLineage is an open standard for lineage collection. It defines a JSON schema for lineage events.

{
  "eventType": "COMPLETE",
  "eventTime": "2026-03-27T10:00:00Z",
  "run": {
    "runId": "run-123"
  },
  "job": {
    "namespace": "warehouse",
    "name": "fact_orders_etl"
  },
  "inputs": [
    {
      "namespace": "staging",
      "name": "staging.orders",
      "facets": {
        "schema": {
          "fields": [
            { "name": "order_id", "type": "BIGINT" },
            { "name": "customer_id", "type": "INT" }
          ]
        }
      }
    }
  ],
  "outputs": [
    {
      "namespace": "warehouse",
      "name": "warehouse.fact_orders",
      "facets": {
        "outputStatistics": {
          "rowCount": 1000000
        }
      }
    }
  ]
}

OpenLineage can be collected by Airflow, Spark, Flink, and many other tools.

Data Catalog Integration

Lineage integrates with data catalogs like Apache Atlas, DataHub, and Collibra.

# Example: pushing lineage to Apache Atlas
from atlas_client import AtlasClient

atlas = AtlasClient('http://atlas:21000', ('admin', 'admin'))

def publish_lineage(atlas, lineage_event):
    """Publish lineage event to Atlas."""

    # Create Atlas lineage record
    guid_map = atlas.create_entities([
        {
            'typeName': 'DataSet',
            'attributes': {
                'qualifiedName': lineage_event['target_table'],
                'name': lineage_event['target_table'].split('.')[-1],
                'description': f"Loaded from {lineage_event['source_table']}"
            }
        }
    ])

    # Create lineage relationship
    atlas.create_lineage({
        'guid': guid_map[0],
        'lineageDirection': 'OUTPUT',
        'lineageRelations': [{
            'guid': get_guid_for_source(lineage_event['source_table']),
            'lineageType': 'INPUT'
        }]
    })

Visualizing Lineage

flowchart TB
    subgraph "Source Systems"
        S1["Oracle ERP\nstaging.orders"]
        S2["Salesforce CRM\nstaging.customers"]
        S3["Shopify\nstaging.products"]
    end
    subgraph "ETL / Transformation Layer"
        E1["dbt: stg_orders"]
        E2["dbt: dim_customer"]
        E3["dbt: fact_orders"]
    end
    subgraph "Warehouse"
        W1["warehouse.dim_customer"]
        W2["warehouse.fact_orders"]
    end
    subgraph "Reporting"
        R1["reports.daily_sales"]
        R2["reports.customer360"]
    end
    S1 --> E1
    S2 --> E1
    S2 --> E2
    S3 --> E3
    E1 --> E3
    E1 --> E2
    E2 --> W1
    E3 --> W2
    W1 --> R2
    W2 --> R1
    W2 --> R2

A lineage graph is only useful if you can explore it visually.

Graph Databases for Lineage

Graph databases like Neo4j are natural fits for lineage storage.

-- Query lineage downstream of a table
MATCH (source:Table {name: 'staging.orders'})
MATCH (source)-[:DEPENDS_ON*]->(downstream)
RETURN downstream

-- Query lineage upstream of a table
MATCH (target:Table {name: 'reports.daily_sales'})
MATCH (upstream)-[:DEPENDS_ON*]->(target)
RETURN upstream

-- Find paths between two tables
MATCH path = (a:Table)-[:DEPENDS_ON*]->(b:Table)
WHERE a.name = 'staging.customers' AND b.name = 'reports.customer360'
RETURN path

Lineage UI

Build or buy a lineage UI that lets users explore the graph.

Key features:

• Search: Find any table or column
• Upstream view: See what feeds into a table
• Downstream view: See what a table feeds into
• Filter by system: Show only certain systems in the graph
• Depth control: Limit how many hops to display
• Column drill-down: Show column-level dependencies when needed

Lineage Use Cases

Impact Analysis

def impact_analysis(table_name, lineage_graph):
    """Find all downstream dependencies of a table."""

    downstream = set()

    def traverse(node):
        for child in lineage_graph.get_children(node):
            downstream.add(child)
            traverse(child)

    traverse(table_name)
    return downstream

# Before dropping a column:
impacted_reports = impact_analysis('warehouse.fact_orders', lineage_graph)
print(f"Dropping this column affects: {impacted_reports}")

Root Cause Analysis

def root_cause_analysis(report_name, expected_value, actual_value,
                        lineage_graph):
    """Trace unexpected values back to source."""

    # Get all upstream tables
    upstream = get_upstream_tables(report_name, lineage_graph)

    # Check each upstream table for anomalies
    for table in upstream:
        anomalies = check_for_anomalies(
            table,
            date_range=get_report_date_range(report_name)
        )

        if anomalies:
            print(f"Potential issue in {table}:")
            for anomaly in anomalies:
                print(f"  - {anomaly}")

Compliance Evidence

def generate_compliance_report(table_name, date_range, lineage_graph):
    """Generate lineage evidence for audit."""

    upstream = get_upstream_tables(table_name, lineage_graph)

    report = {
        'report_table': table_name,
        'date_range': date_range,
        'lineage': [],
        'data_quality_metrics': {}
    }

    for table in upstream:
        record = {
            'table': table,
            'source_system': get_source_system(table),
            'last_refreshed': get_last_refresh_time(table),
            'transformation_rules': get_transformations(table),
            'validation_results': get_validation_results(table, date_range)
        }
        report['lineage'].append(record)

    return report

Challenges with Lineage

Lineage is valuable but comes with challenges.

Completeness

Capturing lineage across all systems is difficult. Legacy systems, third-party tools, and manual processes may not support lineage capture. You end up with partial lineage that is misleading.

Solution: Start with automated lineage capture where possible. Accept that manual documentation is better than nothing for systems that cannot be instrumented.

Performance

Storing and querying lineage at column-level granularity produces massive graphs. A warehouse with 10,000 columns and complex transformations can produce millions of lineage edges.

Solution: Index aggressively. Use graph databases optimized for traversals. Consider materialized views for common queries.

Maintenance

Lineage becomes stale if not maintained. When tables are renamed, columns dropped, or jobs refactored, lineage must be updated.

Solution: Make lineage capture automatic from CI/CD pipelines. Validate that lineage is updated when code changes.

Building a Lineage Program

Start with what you can automate and expand.

1. Instrument ETL jobs. Use OpenLineage or similar standards to capture lineage automatically from your main ETL platforms.

2. Integrate with data catalog. Push lineage to a catalog that provides search and visualization.

3. Capture dbt lineage. If you use dbt, export the model DAG automatically.

4. Document critical paths manually. For systems that cannot be instrumented, document critical lineage paths manually.

5. Build use cases. The value of lineage is in the use cases. Build impact analysis and root cause analysis tools that make lineage useful.

Lineage Trade-Offs

Dimension	Table-Level	Column-Level	Row-Level
Storage overhead	Low	Medium	High
Query performance	Fast	Medium	Slow
Precision of impact analysis	Low	High	Very high
Implementation complexity	Low	Medium	High
Compliance suitability	Basic audits	Regulatory audits	Full audit trails
Maintenance cost	Low	Medium	High

Data Lineage Production Failure Scenarios

Incomplete lineage silently misleads impact analysis

A team instruments their main dbt pipelines but misses three Python scripts that also transform data into the warehouse.fact_orders table. When an analyst drops a column thinking it only affects one report, 14 downstream reports produce incorrect numbers. The lineage graph says it is safe because the Python jobs never appeared in it.

Mitigation: Run impact analysis both ways: from source columns forward AND from destination tables backward. If the paths do not meet, you have blind spots. Add lineage coverage metrics to your CI/CD pipeline and block deployments if new jobs are not instrumented.

Graph database performance collapses on large traversals

A column-level lineage graph with 50 million edges starts timing out on queries that traverse more than 5 hops. Dashboard users get errors. The graph database was never sized for the actual traversal depth of the warehouse transformations.

Mitigation: Pre-compute common traversal paths as materialized views. Set query timeout limits and paginate results. Profile your traversal patterns and create indexes for the most common path shapes.

Stale lineage causes wrong root cause analysis

The lineage graph shows staging.orders as the source of a data quality issue in reports.daily_sales. A team spends 3 days investigating the orders pipeline. The real issue is a late-arriving shipment record that bypassed the pipeline entirely and was loaded directly by a manual SQL job that is not in the lineage graph.

Mitigation: Treat lineage staleness as a data quality issue. Alert when jobs run without updating lineage. Include manual/SQL processes in your lineage inventory, even if they cannot be automatically instrumented. Verify lineage coverage regularly.

Column rename breaks lineage silently

A developer renames customer_region to customer_territory in dim_customer. The column-level lineage shows zero downstream impact because the old column name no longer exists in the graph. But a Tableau dashboard references dim_customer.customer_region directly and returns NULLs for all region values. No lineage edge existed to flag this.

Mitigation: Never delete columns—deprecate them instead. Keep the old column name as a view wrapper around the new column. Block schema changes from CI/CD unless lineage is updated atomically with the schema change.

Lineage Observability Hooks

Track these metrics to keep lineage trustworthy:

-- Lineage coverage: % of jobs with lineage captured
SELECT
    job_name,
    CASE
        WHEN lineage_last_updated IS NOT NULL
        AND lineage_last_updated > last_job_run - INTERVAL '1 day'
        THEN 'COVERED'
        ELSE 'MISSING'
    END AS lineage_status
FROM etl_job_registry;

-- Detect orphaned tables: tables with no upstream lineage
SELECT
    table_name,
    last_loaded,
    last_data_refresh
FROM table_registry
WHERE table_name NOT IN (
    SELECT DISTINCT target_table FROM table_lineage
);

-- Verify column lineage completeness
SELECT
    target_table,
    target_column,
    COUNT(DISTINCT source_table) AS upstream_sources
FROM column_lineage
GROUP BY target_table, target_column
HAVING COUNT(DISTINCT source_table) = 0;

Log every pipeline run: job name, run ID, start/end timestamps, row counts before and after, and whether lineage was successfully published. Alert when row counts deviate more than 20% from baseline or when lineage publish fails.

Lineage Anti-Patterns

Partial lineage treated as complete. Instrumenting 70% of your pipelines and assuming you have lineage coverage is worse than having no lineage—you trust the graph for decisions it cannot support.

Lineage without use cases. Maintaining lineage that nobody uses is a resource drain. Build the use cases first (impact analysis, root cause tools), then instrument to serve those use cases.

Graph schema mismatches production schema. The lineage graph shows a table that was dropped 6 months ago. The graph was populated once and never updated. Treat lineage as a living system, not a one-time project.

Column-level lineage on systems that only need table-level. Column-level lineage is expensive to store and query. If your compliance requirements only need table-level, save the overhead.

Data Lineage Quick Recap

• Lineage tracks data flow from source to destination at table, column, or row level.
• Table-level answers “which tables depend on which.” Column-level answers “which columns derive from which.” Row-level answers “which records came from which.”
• OpenLineage is the open standard for automated lineage capture from ETL platforms.
• Store lineage in a graph database (Neo4j, Amazon Neptune) for efficient traversal queries.
• Instrument dbt, Spark, Airflow automatically. Document critical manual pipelines manually.
• Validate lineage coverage in CI/CD. Alert when lineage publish fails or row counts deviate.
• Partial lineage is dangerous—always check both forward and backward traversal for blind spots.

For more on data governance, see Data Governance for the broader framework of data management. For tracking data quality alongside lineage, see Data Validation for ensuring data meets quality standards., see Data Governance for the broader framework of data management. For tracking data quality alongside lineage, see Data Validation for ensuring data meets quality standards.