Skip to content

a5c-ai/babysitter / library/specializations/data-engineering-analytics/skills/data-lineage-mapper

data-lineage-mapper

Verified

Extracts and maps data lineage from various sources including SQL, dbt, Airflow, and Spark, generating comprehensive lineage graphs for impact analysis.

FileKindSize
README.mdmarkdown8.5 KB
SKILL.mdskill md8.9 KB
# Data Lineage Mapper

A comprehensive skill for extracting and mapping data lineage from SQL queries, dbt projects, Airflow DAGs, and Spark jobs, enabling data governance and impact analysis.

## Overview

The Data Lineage Mapper parses various data transformation sources to build comprehensive lineage graphs. It tracks data flow from source to destination at both table and column levels, enabling impact analysis, compliance auditing, and data governance.

## Quick Start

```bash
# Map lineage from SQL
/skill data-lineage-mapper --sources.type sql --sources.content "INSERT INTO target SELECT * FROM source"

# Map lineage from dbt project
/skill data-lineage-mapper --sources.type dbt --sources.content ./target/manifest.json

# Export to DataHub
/skill data-lineage-mapper --sources.type dbt --targetCatalog datahub
```

## Features

### 1. Multi-Source Lineage Extraction

Extract lineage from various sources:

| Source Type | What's Extracted |
|-------------|-----------------|
| SQL | SELECT, INSERT, MERGE statements |
| dbt | Model refs, sources, tests |
| Airflow | Task data flows, XCom |
| Spark | DataFrame operations, SQL |
| Files | CSV/Parquet read/write |

### 2. Column-Level Lineage

Track individual column transformations:

```
Source: customers.first_name + customers.last_name
  |
  | CONCAT transformation
  v
Target: dim_customers.full_name
```

### 3. Impact Analysis

Understand data dependencies:

- **Upstream**: What feeds into this table?
- **Downstream**: What depends on this table?
- **Critical Path**: Most important lineage paths

### 4. Catalog Integration

Export to data catalogs:

- DataHub
- Amundsen
- Alation
- OpenLineage (universal format)

## Output Structure

### Lineage Graph

```json
{
  "lineageGraph": {
    "nodes": [
      {
        "id": "warehouse.raw.customers",
        "type": "table",
        "name": "customers",
        "database": "warehouse",
        "schema": "raw",
        "columns": ["id", "name", "email"]
      },
      {
        "id": "warehouse.analytics.dim_customers",
        "type": "table",
        "name": "dim_customers",
        "database": "warehouse",
        "schema": "analytics"
      }
    ],
    "edges": [
      {
        "source": "warehouse.raw.customers",
        "target": "warehouse.analytics.dim_customers",
        "transformationType": "dbt_model",
        "sql": "SELECT * FROM {{ source('raw', 'customers') }}"
      }
    ]
  }
}
```

### Column Lineage

```json
{
  "columnLineage": [
    {
      "targetColumn": {
        "table": "dim_customers",
        "column": "full_name"
      },
      "sourceColumns": [
        {"table": "raw_customers", "column": "first_name"},
        {"table": "raw_customers", "column": "last_name"}
      ],
      "transformationLogic": "CONCAT(first_name, ' ', last_name)"
    }
  ]
}
```

### Impact Analysis

```json
{
  "impactAnalysis": {
    "target": "raw.customers",
    "upstream": [
      "external_crm.accounts",
      "external_crm.contacts"
    ],
    "downstream": [
      "staging.stg_customers",
      "analytics.dim_customers",
      "analytics.fct_orders",
      "reports.customer_360"
    ],
    "criticalPath": [
      "raw.customers",
      "staging.stg_customers",
      "analytics.dim_customers"
    ]
  }
}
```

## Use Cases

### 1. Schema Change Impact Analysis

Before modifying a source table:

```json
{
  "sources": [{"type": "dbt", "content": "./target/manifest.json"}],
  "options": {
    "impactAnalysisTarget": "raw.customers"
  }
}
```

Output shows all downstream tables and reports affected.

### 2. Compliance Auditing

Track PII data flow:

```json
{
  "sources": [
    {"type": "dbt", "content": "./target/manifest.json"},
    {"type": "sql", "content": "./etl/customer_etl.sql"}
  ],
  "options": {
    "columnLevel": true,
    "trackColumns": ["email", "phone", "ssn"]
  }
}
```

### 3. Data Catalog Population

Export lineage to data catalog:

```json
{
  "sources": [{"type": "dbt", "content": "./target/manifest.json"}],
  "targetCatalog": "datahub",
  "options": {
    "columnLevel": true
  }
}
```

### 4. Cross-System Lineage

Connect lineage across tools:

```json
{
  "sources": [
    {
      "type": "sql",
      "content": "INSERT INTO landing.events SELECT * FROM kafka.events_topic",
      "metadata": {"system": "ingestion"}
    },
    {
      "type": "dbt",
      "content": "./analytics/target/manifest.json",
      "metadata": {"system": "transformation"}
    },
    {
      "type": "sql",
      "content": "./bi/tableau_extracts.sql",
      "metadata": {"system": "reporting"}
    }
  ]
}
```

## SQL Parsing Examples

### SELECT Statement

```sql
SELECT
  c.customer_id,
  c.customer_name,
  SUM(o.amount) as total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
```

Extracted lineage:
- `customers.customer_id` -> output.customer_id (direct)
- `customers.customer_name` -> output.customer_name (direct)
- `orders.amount` -> output.total_orders (aggregation: SUM)

### MERGE Statement

```sql
MERGE INTO dim_customers target
USING stg_customers source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name)
```

Extracted lineage:
- `stg_customers` -> `dim_customers` (merge transformation)
- Column mappings for UPDATE and INSERT

### INSERT...SELECT

```sql
INSERT INTO analytics.fct_orders (order_id, customer_id, amount)
SELECT o.id, c.customer_id, o.order_amount
FROM staging.orders o
JOIN staging.customers c ON o.cust_id = c.id
```

Extracted lineage:
- `staging.orders` -> `analytics.fct_orders`
- `staging.customers` -> `analytics.fct_orders`
- Column: `orders.id` -> `fct_orders.order_id`

## dbt Lineage Extraction

From manifest.json, extracts:

```json
{
  "nodes": {
    "model.analytics.dim_customers": {
      "depends_on": {
        "nodes": [
          "source.analytics.raw.customers",
          "model.analytics.stg_customers"
        ]
      }
    }
  }
}
```

With catalog.json, adds column lineage:

```json
{
  "columns": {
    "full_name": {
      "type": "VARCHAR",
      "description": "Full customer name"
    }
  }
}
```

## Catalog Export Formats

### OpenLineage (Universal)

```json
{
  "eventType": "COMPLETE",
  "eventTime": "2024-01-15T10:00:00Z",
  "run": {"runId": "abc123"},
  "job": {"namespace": "dbt", "name": "dim_customers"},
  "inputs": [
    {"namespace": "raw", "name": "customers"}
  ],
  "outputs": [
    {"namespace": "analytics", "name": "dim_customers"}
  ]
}
```

### DataHub

```json
{
  "entity": "dataset",
  "urn": "urn:li:dataset:(urn:li:dataPlatform:snowflake,analytics.dim_customers,PROD)",
  "aspects": {
    "upstreamLineage": {
      "upstreams": [
        {"dataset": "urn:li:dataset:...raw.customers"}
      ]
    }
  }
}
```

## Integration

### With dbt MCP Server

For real-time manifest access:

```json
{
  "mcpServers": {
    "dbt": {
      "command": "npx",
      "args": ["-y", "@dbt-labs/dbt-mcp"]
    }
  }
}
```

### With Babysitter Processes

Used in these orchestration processes:

- **data-lineage.js** - Comprehensive lineage mapping
- **data-catalog.js** - Catalog population
- **dbt-project-setup.js** - Project documentation

## Best Practices

### 1. Maintain Lineage Currency

Run lineage extraction after each deployment:

```bash
dbt build
/skill data-lineage-mapper --sources.type dbt --sources.content ./target/manifest.json
```

### 2. Include All Systems

Map lineage across all data tools:
- Ingestion (Airbyte, Fivetran, custom ETL)
- Transformation (dbt, Spark, SQL)
- Consumption (BI tools, exports)

### 3. Use Column-Level Lineage

Enable for compliance and debugging:

```json
{"options": {"columnLevel": true}}
```

### 4. Export to Catalog

Maintain a centralized lineage view:

```json
{"targetCatalog": "datahub"}
```

## Troubleshooting

### Common Issues

**"Unable to parse SQL"**
- Check SQL dialect compatibility
- Simplify complex expressions
- Verify complete statement

**"Missing column lineage"**
- Ensure catalog.json is present
- Run `dbt docs generate`
- Check column references in SQL

**"Cross-system gaps"**
- Add explicit source metadata
- Map external system identifiers
- Include connection metadata

## References

- [OpenLineage](https://openlineage.io/)
- [DataHub Lineage](https://datahubproject.io/docs/lineage/lineage-feature-guide)
- [dbt Column-Level Lineage](https://docs.getdbt.com/docs/collaborate/column-level-lineage)
- [Marquez](https://marquezproject.ai/)
- [Apache Atlas](https://atlas.apache.org/)

## Version

- **Current Version**: 1.0.0
- **Skill ID**: SK-DEA-010
- **Category**: Data Governance
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall