Lineage Harvesting

Lineage harvesting automates the collection of lineage metadata from disparate systems without manual intervention. This is achieved by integrating with:

• SQL Parsers to analyze query logic and infer column-level dependencies.
• Workflow Orchestrators (e.g., Apache Airflow, Dagster) to capture job execution graphs.
• Processing Engines (e.g., Spark, dbt) to extract transformation logic from runtime plans. Automation is critical for scale, ensuring lineage remains current as pipelines evolve.

Effective lineage harvesting connects to a heterogeneous technology stack to build a unified lineage graph. It integrates metadata from:

• Databases & Warehouses (e.g., Snowflake, BigQuery) for table and view dependencies.
• SaaS Applications (e.g., Salesforce, HubSpot) via API connectors.
• BI & Visualization Tools (e.g., Tableau, Looker) to map report-to-data dependencies.
• Code Repositories (e.g., Git) to analyze pipeline definition files for static lineage. This creates cross-system lineage, breaking down data silos.

Harvesting employs two complementary methods to maximize lineage fidelity:

• Static Lineage: Derived by analyzing source code, SQL scripts, and configuration files without execution. It reveals intended dependencies but may miss runtime logic.
• Dynamic Lineage: Captured by instrumenting jobs during execution. It provides an accurate record of actual data flow, including runtime parameters, data volumes, and conditional branches. The most robust systems combine both for a complete picture of design and operational truth.

The lineage granularity defines the level of detail captured, which dictates the utility for debugging and impact analysis. Harvesting systems target:

• Job/Table-Level: Tracks dependencies between entire datasets or pipeline tasks. Useful for high-level impact analysis.
• Column-Level: Tracks the flow and transformation of individual data columns. Essential for debugging specific data issues and understanding business logic.
• Row/Cell-Level (Emerging): Aims to trace the provenance of individual data records, offering the highest fidelity for audit and compliance but requiring significant instrumentation.

To avoid vendor lock-in and enable ecosystem interoperability, modern lineage harvesting aligns with open standards. The predominant standard is OpenLineage, which defines:

• A common facet-based schema for lineage events (run, job, dataset).
• Protocols for emitting events as JSON over HTTP or via message queues.
• Integration points for collectors in various data platforms. Adopting OpenLineage allows harvested metadata to be consumed by any compatible observability platform or data catalog.

Lineage harvesting operates as a continuous, event-driven process, not a periodic batch job. This ensures real-time observability. Key patterns include:

• Triggering on Pipeline Events: Harvesting initiates on events like 'job start', 'task complete', or 'query execution'.
• Streaming Metadata: Lineage events are streamed to a central metadata store as soon as they are captured.
• Automated Graph Updates: The central dependency graph is updated in near real-time, enabling immediate impact analysis and root cause analysis (RCA) when issues arise.

SQL parsers are the most common source for harvesting column-level and table-level lineage. They statically analyze SQL scripts (e.g., from dbt, stored procedures, BI tools) to extract SELECT, JOIN, and INSERT statements.

• Examples: Parsing dbt .sql model files, Airflow SQL operators, or LookML/Tableau queries.
• Key Output: Maps source tables/columns to target tables/columns based on the query logic.
• Limitation: Cannot capture dynamic SQL generated at runtime without execution tracing.

Orchestrators like Apache Airflow, Dagster, and Prefect provide DAG (Directed Acyclic Graph) definitions that specify task dependencies. Harvesting from these systems yields job-level lineage.

• Examples: Extracting task_id dependencies and sensor triggers from an Airflow DAG's Python code.
• Key Output: Understands the order of execution and which datasets are produced by which tasks.
• Integration: Often combined with SQL parsing from tasks to connect job flow with data flow.

Harvesting lineage at runtime from engines like Apache Spark, Flink, or cloud data warehouses (Snowflake, BigQuery) provides dynamic, high-fidelity lineage.

• Mechanism: Uses listener APIs or query execution plans. For example, Spark's QueryExecution object or Snowflake's ACCESS_HISTORY.
• Key Advantage: Captures what actually executed, including runtime parameters, data volumes, and conditional logic missed by static analysis.
• Use Case: Essential for debugging failed jobs and understanding actual resource consumption.

Centralized catalogs (e.g., Amundsen, DataHub, OpenMetadata) and cloud-native metastores (AWS Glue, Unity Catalog) act as aggregation points for harvested lineage.

• Role: They consume lineage from other sources via APIs (like OpenLineage) and serve as the system of record.
• Key Function: Provide a unified graph by linking harvested technical lineage with business metadata (owners, descriptions, tags).
• Harvesting Target: The catalog itself can be a source when integrating multiple lineage tools.

Business Intelligence platforms (e.g., Tableau, Power BI, Looker) are critical sources for understanding the last mile of data consumption.

• What's Harvested: Dashboard and report dependencies on underlying datasets, tables, and columns.
• Method: Parsing workbook files (.twb, .pbix) or querying tool metadata APIs.
• Business Impact: Enables impact analysis for schema changes—showing which reports will break if a source column is modified.

Version control systems (Git) and CI/CD pipelines are sources for harvesting lineage defined as code, ensuring lineage reflects the deployed state.

• Process: Scanning repository branches for SQL, YAML pipeline definitions, and dbt projects during merge or build processes.
• Key Benefit: Links lineage to specific code commits, enabling historical tracking and rollback analysis.
• Practice: Part of DataOps, where lineage harvesting is integrated into the deployment lifecycle.

The comprehensive record of a data asset's origin, movement, transformation, and dependencies across its entire lifecycle. It provides an audit trail used for:

• Governance and Compliance: Demonstrating data provenance for regulations like GDPR.
• Impact Analysis: Understanding what will break if a source schema changes.
• Root Cause Analysis: Quickly tracing an error in a dashboard back to the faulty job or source.
• Optimization: Identifying redundant or inefficient data flows.

A specific subset of lineage that focuses exclusively on the origin and creation history of a data asset. It answers the questions: "Where did this data come from?" and "Under what conditions was it created?"

Key differentiators from general lineage:

• Focuses on the initial source and immutable creation event.
• Critical for establishing authenticity and trustworthiness.
• Often includes metadata like source system, timestamp, user ID, and extraction parameters.
• Used heavily in scientific computing and regulated industries to verify data integrity.

Two primary methods for harvesting lineage metadata, differing in when and how the information is captured.

Static Lineage:

• Harvested by analyzing source code, SQL scripts, and configuration files (e.g., dbt .sql files, Airflow DAGs).
• Shows potential data flow based on declared logic.
• Pros: Lightweight, can be generated without running pipelines.
• Cons: May miss runtime decisions, parameterized paths, or logic in external applications.

Dynamic Lineage:

• Captured by instrumenting jobs at runtime (e.g., using OpenLineage listeners).
• Shows actual data flow, including runtime parameters, data volumes, and job status.
• Pros: Higher fidelity, reflects what truly happened.
• Cons: Requires execution and instrumentation overhead.

A high-fidelity form of lineage that tracks dependencies at the granularity of individual columns within tables. It maps how a specific output column is derived from specific input columns through transformations.

Contrast with Table/Job-Level Lineage:

• Job-Level: "Table B depends on Table A."
• Column-Level: "The customer_lifetime_value column in Table B is calculated from the total_purchases column in Table A and the signup_date column in Table C."

Critical for:

• Precise impact analysis for schema changes.
• Debugging complex business logic errors.
• Data privacy compliance (e.g., tracking PII flow).
• Often harvested using specialized SQL parsers.

A directed graph (often a Directed Acyclic Graph or DAG) that visually models the relationships and dependencies between data assets and processing jobs. It is the primary output structure for organizing harvested lineage data.

Key Properties:

• Nodes: Represent data assets (tables, files, streams) or jobs (Spark jobs, SQL queries).
• Edges: Represent dependencies ("consumes" or "produces").
• Directed: Flow has a direction (from source to destination).
• Acyclic: In a pipeline DAG, dependencies should not form loops.

This graph structure enables computational traversal for impact analysis (walking downstream edges) and root cause analysis (walking upstream edges).