Chapter 2

Types of Data Lineage

Data lineage comes in different forms, each serving specific use cases and providing different levels of detail. Understanding these types helps you choose the right granularity for your needs.

1. Column-Level Lineage

Column-level lineage tracks data at the most granular level: individual columns within tables. It shows exactly which source columns contribute to which target columns and how they're transformed.

What It Tracks

Source column(s) for each target column
Transformation logic applied to columns
Data type conversions
Aggregations and calculations
Join conditions affecting columns

Example: Column-Level Lineage

-- SQL Transformation
SELECT
    customer_id,
    UPPER(first_name || ' ' || last_name) AS full_name,
    ROUND(total_purchases / order_count, 2) AS avg_order_value
FROM raw.customers c
JOIN raw.orders o ON c.id = o.customer_id;

Column Lineage:

customer_id ← raw.customers.id
full_name ← raw.customers.first_name + raw.customers.last_name
avg_order_value ← raw.orders.total_purchases ÷ raw.orders.order_count

Use Cases

Data Privacy: Track PII (Personally Identifiable Information) columns through transformations
Data Quality: Trace bad values to specific source columns
Regulatory Compliance: Show exactly how sensitive data is used
Impact Analysis: Understand which reports break if a column changes

Challenges

Computationally expensive to extract and maintain
Requires parsing complex SQL and code
Can become overwhelming with thousands of columns
Not all transformations are easily traceable (UDFs, complex code)

2. Table-Level Lineage

Table-level lineage tracks relationships between entire tables, views, or datasets. It provides a higher-level view of data flows without diving into column details.

What It Tracks

Source tables/datasets
Target tables/datasets
Transformation jobs/processes
Data movement patterns

Example: Table-Level Lineage

ETL Pipeline Flow:

raw.api_events
    → staging.cleaned_events
        → warehouse.fact_user_activity
            → analytics.daily_active_users
                → dashboard.user_engagement_report

Use Cases

High-Level Impact Analysis: What systems depend on this table?
Data Architecture: Visualize overall data flow
Performance Optimization: Identify bottlenecks in data pipelines
Change Management: Plan table migrations and deprecations

Advantages

Easier to implement and maintain than column-level
Provides good overview without overwhelming detail
Less computational overhead
Sufficient for many use cases

3. Process-Level Lineage

Process-level (or job-level) lineage focuses on the transformations themselves rather than the data. It tracks which jobs, scripts, or processes create and modify data.

What It Tracks

ETL/ELT job definitions
Job dependencies and scheduling
Execution history and timing
Success/failure status
Resource consumption

Example: Process-Level Lineage

Data Pipeline Flow:

Job: extract_api_data
  Reads: external.api_endpoint
  Writes: raw.api_events
  Schedule: Every 15 minutes
  Last Run: 2025-12-26 10:00:00
  Status: Success
  Duration: 45 seconds

Job: clean_events
  Reads: raw.api_events
  Writes: staging.cleaned_events
  Schedule: Triggered after extract_api_data
  Last Run: 2025-12-26 10:01:00
  Status: Success
  Duration: 2 minutes

Use Cases

Pipeline Orchestration: Understand job dependencies
Troubleshooting: Identify which job failed and caused downstream issues
Performance Monitoring: Track job execution times
Resource Planning: Optimize job scheduling

4. Business Lineage

Business lineage connects technical data lineage to business concepts, metrics, and KPIs. It translates technical assets into business-friendly language.

What It Tracks

Business metrics and their technical implementations
Ownership and stewardship information
Business glossary terms
Data quality rules and SLAs

Example: Business Lineage

Business Metric: "Monthly Recurring Revenue (MRR)"

Business Definition:
  Sum of all active subscription values per month

Technical Implementation:
  SELECT
      DATE_TRUNC('month', subscription_date) AS month,
      SUM(monthly_value) AS mrr
  FROM warehouse.active_subscriptions
  GROUP BY month

Data Sources:
  • CRM System → raw.subscriptions
  • Payment Gateway → raw.payments
  • Product Catalog → raw.plans

Business Owner: VP of Finance
Data Steward: Finance Analytics Team
Update Frequency: Daily
SLA: Available by 9am EST

Use Cases

Business User Self-Service: Non-technical users understand data origins
Governance: Track data ownership and accountability
Metric Definitions: Ensure consistent metric calculations
Regulatory Reporting: Connect compliance requirements to data

Comparison of Lineage Types

Type	Granularity	Complexity	Best For
Column-Level	Very Fine	High	Data privacy, detailed impact analysis
Table-Level	Medium	Medium	General impact analysis, architecture
Process-Level	Coarse	Low	Pipeline orchestration, job monitoring
Business-Level	Variable	Medium	Business user self-service, governance

Choosing the Right Type

Most organizations need a combination of lineage types. The key is choosing the right level for each use case:

            Recommended Approach:
            Start with table-level lineage for broad coverage
Add column-level lineage for sensitive data and critical pipelines
Layer in business lineage for governance and user-facing tools
Use process-level lineage for operational monitoring

        

Hybrid Approaches

Modern lineage tools often combine multiple types:

Default to table-level for performance
Drill down to column-level on demand
Enrich with business context
Include job/process metadata

This hybrid approach balances comprehensiveness with maintainability and performance.