Data Lineage

Data Lineage Documentation

When to Use This Skill

Use this skill when:

• Data Lineage tasks - Working on document data flow, transformation lineage, and impact analysis for traceability and compliance
• Planning or design - Need guidance on Data Lineage approaches
• Best practices - Want to follow established patterns and standards

Overview

Data lineage tracks data from origin to destination, documenting every transformation along the way. Essential for impact analysis, debugging, compliance, and trust in data.

Lineage Types

Lineage Granularity

Lineage Diagram

SOURCE-TO-TARGET LINEAGE
┌──────────────────────────────────────────────────────────────────┐
│                                                                   │
│  ┌─────────┐    ┌─────────────┐    ┌─────────┐    ┌───────────┐ │
│  │  CRM    │───►│   STAGING   │───►│  DWH    │───►│  REPORTS  │ │
│  │ System  │    │   Tables    │    │  Facts  │    │   & BI    │ │
│  └─────────┘    └─────────────┘    └─────────┘    └───────────┘ │
│       │               │                 │                │       │
│       │               │                 │                │       │
│  ┌────┴────┐    ┌─────┴─────┐    ┌─────┴─────┐    ┌─────┴─────┐ │
│  │customers│    │stg_customer│    │dim_customer│   │sales_report│ │
│  │ orders  │    │stg_order   │    │fact_sales │   │cust_dashboard│
│  │products │    │stg_product │    │dim_product│   │product_analytics
│  └─────────┘    └───────────┘    └───────────┘   └───────────┘  │
│                                                                   │
└──────────────────────────────────────────────────────────────────┘

Column-Level Lineage

Mapping Documentation Template

# Source-to-Target Mapping: dim_customer

## Target Table
- Schema: dbo
- Table: dim_customer
- Type: Dimension (SCD Type 2)

## Column Mappings

| Target Column | Source System | Source Table | Source Column | Transformation |
|---------------|---------------|--------------|---------------|----------------|
| customer_key | Generated | N/A | N/A | IDENTITY |
| customer_id | CRM | customers | customer_id | TRIM, UPPER |
| full_name | CRM | customers | first_name + last_name | CONCAT, TRIM |
| email | CRM | customers | email | LOWER, TRIM |
| segment | CRM | customers | tier | CASE mapping |
| city | CRM | addresses | city | TRIM |
| state | CRM | addresses | state_code | Lookup to ref_state |
| country | CRM | addresses | country_code | Lookup to ref_country |
| effective_date | Generated | N/A | N/A | GETUTCDATE() |
| is_current | Generated | N/A | N/A | 1 (new records) |

## Transformation Rules

### Rule 1: Full Name Concatenation
```sql
CONCAT(TRIM(first_name), ' ', TRIM(last_name))

Rule 2: Segment Mapping

CASE tier
    WHEN 'G' THEN 'Gold'
    WHEN 'S' THEN 'Silver'
    WHEN 'B' THEN 'Bronze'
    ELSE 'Standard'
END

Join Conditions

customers c
LEFT JOIN addresses a ON c.customer_id = a.customer_id
    AND a.address_type = 'PRIMARY'
LEFT JOIN ref_state rs ON a.state_code = rs.state_code

Data Flow Diagrams

Mermaid Flow Diagram

flowchart LR
    subgraph Sources
        CRM[(CRM)]
        ERP[(ERP)]
        ECOM[(E-Commerce)]
    end

    subgraph Staging
        STG_CUST[stg_customer]
        STG_ORD[stg_order]
        STG_PROD[stg_product]
    end

    subgraph Transformations
        T1[Cleanse]
        T2[Match & Merge]
        T3[Enrich]
    end

    subgraph Data Warehouse
        DIM_CUST[dim_customer]
        DIM_PROD[dim_product]
        FACT_SALES[fact_sales]
    end

    subgraph Consumption
        RPT[Reports]
        DASH[Dashboards]
        API[Data API]
    end

    CRM --> STG_CUST
    ERP --> STG_ORD
    ECOM --> STG_PROD

    STG_CUST --> T1
    STG_ORD --> T1
    STG_PROD --> T1

    T1 --> T2
    T2 --> T3

    T3 --> DIM_CUST
    T3 --> DIM_PROD
    T3 --> FACT_SALES

    DIM_CUST --> RPT
    DIM_PROD --> DASH
    FACT_SALES --> API

Transformation Catalog

Transformation Types

Transformation Documentation

# Transformation: Calculate Order Total

## Description
Calculates the extended amount for each order line item.

## Input Columns
| Column | Source | Type |
|--------|--------|------|
| quantity | stg_order | INT |
| unit_price | stg_product | DECIMAL(10,2) |
| discount_pct | stg_order | DECIMAL(5,2) |

## Output Column
| Column | Target | Type |
|--------|--------|------|
| extended_amount | fact_sales | DECIMAL(18,2) |

## Logic
```sql
(quantity * unit_price) * (1 - COALESCE(discount_pct, 0) / 100)

Business Rules

• Discount cannot exceed 100%
• Negative quantities are returns
• NULL discount treated as 0%

Impact Analysis

Forward Impact (What does this affect?)

# Impact Analysis: customers.email

## Direct Dependents
| Object | Type | Relationship |
|--------|------|--------------|
| stg_customer.email | Staging Table | Source |
| dim_customer.email | Dimension | Target |
| marketing_list.email | Report | Display |

## Indirect Dependents
| Object | Type | Impact Path |
|--------|------|-------------|
| email_campaign_report | Report | dim_customer → email_metrics → report |
| customer_dashboard | Dashboard | dim_customer → customer_360 → dashboard |

## Change Impact Assessment
- **Schema Change**: Would break ETL pipeline
- **Data Quality Issue**: Affects all downstream reports
- **Removal**: Requires updates to 12 dependent objects

Backward Impact (Where does this come from?)

# Backward Lineage: fact_sales.total_revenue

## Immediate Sources
| Source | Transformation |
|--------|----------------|
| stg_order.quantity | Direct |
| stg_product.unit_price | Direct |
| stg_order.discount_pct | Direct |

## Origin Sources
| System | Table | Column |
|--------|-------|--------|
| ERP | orders | qty |
| ERP | order_lines | unit_price |
| CRM | promotions | discount |

## Full Lineage Path
ERP.orders.qty → stg_order.quantity → (calculation) → fact_sales.total_revenue
ERP.order_lines.unit_price → stg_product.unit_price → (calculation) → fact_sales.total_revenue
CRM.promotions.discount → stg_order.discount_pct → (calculation) → fact_sales.total_revenue

Lineage Metadata Schema

-- Lineage storage schema (PascalCase - SQL Server Convention)
CREATE TABLE LineageDataset (
    DatasetId INT IDENTITY PRIMARY KEY,
    DatasetName VARCHAR(200) NOT NULL,
    DatasetType VARCHAR(50), -- Table, View, File, API
    SystemName VARCHAR(100),
    SchemaName VARCHAR(100),
    Description VARCHAR(500),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE LineageColumn (
    ColumnId INT IDENTITY PRIMARY KEY,
    DatasetId INT FOREIGN KEY REFERENCES LineageDataset(DatasetId),
    ColumnName VARCHAR(200) NOT NULL,
    DataType VARCHAR(50),
    IsKey BIT,
    IsSensitive BIT,
    Description VARCHAR(500)
);

CREATE TABLE LineageMapping (
    MappingId INT IDENTITY PRIMARY KEY,
    SourceColumnId INT FOREIGN KEY REFERENCES LineageColumn(ColumnId),
    TargetColumnId INT FOREIGN KEY REFERENCES LineageColumn(ColumnId),
    TransformationType VARCHAR(50),
    TransformationLogic VARCHAR(2000),
    PipelineName VARCHAR(200),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE LineagePipeline (
    PipelineId INT IDENTITY PRIMARY KEY,
    PipelineName VARCHAR(200) NOT NULL,
    PipelineType VARCHAR(50), -- ETL, CDC, Streaming
    Schedule VARCHAR(100),
    Owner VARCHAR(100),
    DocumentationUrl VARCHAR(500)
);

C# Lineage Tracking

public class LineageTracker
{
    private readonly ILineageRepository _repository;

    public async Task TrackTransformation(
        string pipelineName,
        IEnumerable<ColumnMapping> mappings,
        CancellationToken ct)
    {
        foreach (var mapping in mappings)
        {
            var lineage = new LineageMapping
            {
                SourceDataset = mapping.Source.Dataset,
                SourceColumn = mapping.Source.Column,
                TargetDataset = mapping.Target.Dataset,
                TargetColumn = mapping.Target.Column,
                TransformationType = mapping.TransformationType,
                TransformationLogic = mapping.Logic,
                PipelineName = pipelineName,
                ExecutedAt = DateTime.UtcNow
            };

            await _repository.AddAsync(lineage, ct);
        }
    }

    public async Task<IEnumerable<LineageMapping>> GetForwardLineage(
        string dataset,
        string column,
        int depth = 3,
        CancellationToken ct = default)
    {
        var results = new List<LineageMapping>();
        var queue = new Queue<(string Dataset, string Column, int Level)>();
        queue.Enqueue((dataset, column, 0));

        while (queue.Count > 0)
        {
            var (ds, col, level) = queue.Dequeue();
            if (level >= depth) continue;

            var mappings = await _repository.GetMappingsBySource(ds, col, ct);
            results.AddRange(mappings);

            foreach (var m in mappings)
            {
                queue.Enqueue((m.TargetDataset, m.TargetColumn, level + 1));
            }
        }

        return results;
    }
}

Lineage Visualization

Graph Representation

graph LR
    subgraph "Source Layer"
        A[crm.customers] --> B[email]
        A --> C[name]
    end

    subgraph "Staging Layer"
        D[stg.customer] --> E[email]
        D --> F[full_name]
    end

    subgraph "DWH Layer"
        G[dim.customer] --> H[email]
        G --> I[customer_name]
    end

    B -->|LOWER, TRIM| E
    C -->|CONCAT| F
    E -->|Direct| H
    F -->|Direct| I

    style B fill:#f9f,stroke:#333
    style E fill:#bbf,stroke:#333
    style H fill:#bfb,stroke:#333

Validation Checklist

• All source-to-target mappings documented
• Transformation logic captured for each mapping
• Join conditions documented
• Business rules included with mappings
• Lineage covers all data layers
• Impact analysis documented for critical fields
• Lineage metadata stored in accessible format
• Visualization available for key data flows

Integration Points

Inputs from:

• er-modeling skill → Table relationships
• migration-planning skill → ETL specifications
• schema-design skill → Physical schemas

Outputs to:

• data-governance skill → Data catalog
• Impact analysis → Change management
• Compliance → Audit trails
• Debugging → Root cause analysis