Build a data lineage graph: extraction, impact analysis, and visualizations.
- User
Build a data lineage graph: extraction, impact analysis, and visualizations.
- Assistant
graph TD subgraph "Sources" S1[CSV: sales_data.csv] S2[API: customer_api] S3[DB: raw_orders] end subgraph "Extraction & Ingestion" E1[Extract Job: Airflow DAG - extract_sales] E2[Extract Job: Fivetran - customer_sync] E3[Extract Job: Spark - orders_etl] end subgraph "Transformation Layer" T1[dbt Model: stg_sales] T2[dbt Model: stg_customers] T3[dbt Model: fact_orders] T4[SQL View: dim_products] end subgraph "Consumption Layer" C1[Table: mart.sales_summary] C2[Table: mart.customer_segments] C3[Dashboard: Looker - Revenue Dashboard] C4[ML Model: churn_predictor] C5[Report: BI Tool - Monthly KPIs] end %% Edges for lineage S1 -->|read| E1 S2 -->|pull| E2 S3 -->|query| E3 E1 -->|load| T1 E2 -->|load| T2 E3 -->|load| T3 T1 -->|select, join| T3 T2 -->|select, join| T3 T3 -->|ref| T4 T1 -->|materialize| C1 T3 -->|materialize| C1 T2 -->|materialize| C2 C1 -->|query| C3 C2 -->|query| C4 C1 -->|query| C5 C3 -.->|depends_on| C5 classDef source fill:#e1f5fe classDef extract fill:#f3e5f5 classDef transform fill:#e8f5e8 classDef consume fill:#fff3e0 class S1,S2,S3 source class E1,E2,E3 extract class T1,T2,T3,T4 transform class C1,C2,C3,C4,C5 consumeData Lineage Graph: Complete Guide
Greetings! As Sherlock, I've deduced your request requires a fully functional blueprint for a data lineage graph. Data lineage tracks data's journey from sources to consumption, enabling impact analysis (e.g., "What breaks if I change
stg_sales?").I'll break it down into extraction, graph construction, impact analysis, and visualizations. This uses a realistic ETL example (sales pipeline). All code is executable in Python (requires
networkx,pygraphviz,mermaidfor viz—install viapip install networkx pygraphviz pydot).1. Extraction: Capturing Lineage Metadata
Extract lineage from:
- Tools: Airflow/Dagster (DAGs), dbt (models.yml), Spark (logs), query logs (e.g., PostgreSQL
pg_stat_statements). - Automated Methods:
- OpenLineage/Marquez: Instrument jobs for std. events.
- Custom Parser: Scan SQL for table refs (e.g.,
sqlglotlib). - Metadata Stores: Atlas, Amundsen, DataHub.
Sample Extraction Code (Python): Parses a mock dbt
catalog.json+ Airflow logs.import json from typing import Dict, List, Tuple # Mock extracted data (in reality: query logs, dbt manifest, Airflow metadata DB) lineage_data = [ ("sales_data.csv", "extract_sales", "read"), ("customer_api", "customer_sync", "pull"), ("raw_orders", "orders_etl", "query"), ("extract_sales", "stg_sales", "load"), ("customer_sync", "stg_customers", "load"), ("orders_etl", "fact_orders", "load"), ("stg_sales", "fact_orders", "select_join"), ("stg_customers", "fact_orders", "select_join"), ("fact_orders", "dim_products", "ref"), ("stg_sales", "mart.sales_summary", "materialize"), ("fact_orders", "mart.sales_summary", "materialize"), ("stg_customers", "mart.customer_segments", "materialize"), ("mart.sales_summary", "revenue_dashboard", "query"), ("mart.customer_segments", "churn_predictor", "query"), ("mart.sales_summary", "monthly_kpis", "query"), ] # Build edges: (upstream, downstream, transformation_type) edges: List[Tuple[str, str, str]] = lineage_data print("Extracted", len(edges), "lineage edges.")2. Graph Construction
Use NetworkX for a directed graph (DAG). Nodes have metadata (e.g., type: source/transform).
import networkx as nx G = nx.DiGraph() # Add nodes with attributes node_attrs = { "sales_data.csv": {"type": "source", "layer": "Sources"}, "customer_api": {"type": "source", "layer": "Sources"}, "raw_orders": {"type": "source", "layer": "Sources"}, "extract_sales": {"type": "extract", "layer": "Extraction"}, "customer_sync": {"type": "extract", "layer": "Extraction"}, "orders_etl": {"type": "extract", "layer": "Extraction"}, "stg_sales": {"type": "transform", "layer": "Transformation"}, "stg_customers": {"type": "transform", "layer": "Transformation"}, "fact_orders": {"type": "transform", "layer": "Transformation"}, "dim_products": {"type": "transform", "layer": "Transformation"}, "mart.sales_summary": {"type": "consume", "layer": "Consumption"}, "mart.customer_segments": {"type": "consume", "layer": "Consumption"}, "revenue_dashboard": {"type": "consume", "layer": "Consumption"}, "churn_predictor": {"type": "consume", "layer": "Consumption"}, "monthly_kpis": {"type": "consume", "layer": "Consumption"}, } for node, attrs in node_attrs.items(): G.add_node(node, **attrs) # Add edges from extraction for upstream, downstream, op in edges: G.add_edge(upstream, downstream, operation=op) print(f"Graph built: {G.number_of_nodes()} nodes, {G.number_of_edges()} edges.")3. Impact Analysis
- Downstream Impact: Find all dependents (e.g., change in
stg_sales?). - Upstream Sources: Trace origins.
- Critical Paths: Shortest paths or degree centrality.
def impact_analysis(G: nx.DiGraph, node: str) -> Dict: downstream = list(nx.descendants(G, node)) upstream = list(nx.ancestors(G, node)) critical_down = nx.shortest_path(G, node, "revenue_dashboard") if "revenue_dashboard" in nx.descendants(G, node) else [] return { "node": node, "upstream_sources": upstream, "downstream_impacted": downstream, "critical_path_to_dashboard": critical_down, "total_impact_size": len(downstream) } # Example: Impact of changing stg_sales print(json.dumps(impact_analysis(G, "stg_sales"), indent=2))Sample Output:
{ "node": "stg_sales", "upstream_sources": ["sales_data.csv", "extract_sales"], "downstream_impacted": ["fact_orders", "mart.sales_summary", "revenue_dashboard", "monthly_kpis"], "critical_path_to_dashboard": ["stg_sales", "mart.sales_summary", "revenue_dashboard"], "total_impact_size": 4 }4. Visualizations
- Interactive: Mermaid (above) or Plotly.
- Static: Graphviz PNG/SVG.
# Export to Graphviz DOT (render with `dot -Tpng graph.dot -o lineage.png`) nx.nx_agraph.write_dot(G, "lineage.dot") # Or Mermaid exporter (for GitHub/Markdown) def mermaid_graph(G: nx.DiGraph) -> str: lines = ["graph TD"] for u, v, data in G.edges(data=True): lines.append(f' {u} -->|{data.get("operation", "flow")}| {v}') # Add subgraphs/classes as in top diagram return "\n".join(lines) + "\n" + """ classDef source fill:#e1f5fe classDef extract fill:#f3e5f5 classDef transform fill:#e8f5e8 classDef consume fill:#fff3e0 %% Apply classes based on node attrs """ print(mermaid_graph(G))Scaling & Production Tips
- Store in Neo4j/GraphDB for queries:
MATCH (n:Node {name: 'stg_sales'})-[:FLOWS_TO*]->(m) RETURN m. - Real Extraction: Integrate OpenLineage with Airflow/dbt.
- Alerts: Use graph to notify on schema changes (e.g., via DataHub).
- Run Full Script: Copy-paste into Jupyter; outputs graph + analysis.
Case closed? If you provide your pipeline metadata (e.g., dbt manifest JSON), I'll customize further! 🔍
- Tools: Airflow/Dagster (DAGs), dbt (models.yml), Spark (logs), query logs (e.g., PostgreSQL
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.