Query-Based Lineage Not Extracting Correctly

Query-based lineage from data warehouse query logs is not being extracted correctly, showing incomplete lineage, missing tables, or incorrect relationships. This commonly occurs due to complex SQL patterns, insufficient query log retention, or parser limitations with specific SQL dialects.

You Might Be Asking:

• Why is my query-based lineage incomplete?
• How far back does DataHub look for query history?
• Can DataHub parse all SQL dialects?

1. Configure query lineage extraction properly:

Example for Snowflake. This can be adapted for other data sources/connectors.

source:
  type: snowflake
  config:
    include_table_lineage: true
    include_view_lineage: true

    # Query-based lineage settings
    include_usage_stats: true
    start_time: "-30d"  # Look back 30 days
    end_time: "-1d"     # Up to yesterday

    # SQL parsing options
    sql_parser: "sqlglot"  # or "sql-metadata"
    use_queries_v2: true   # Use improved query parsing

2. Verify query log access permissions:

Example for Snowflake. This can be adapted for other data sources/connectors.

-- For Snowflake
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE datahub_role;

-- For BigQuery, ensure service account has bigquery.jobs.listAll

3. For complex queries that don't parse correctly, use manual lineage:

Example for Snowflake. This can be adapted for other data sources/connectors.

from datahub.emitter.rest_emitter import DatahubRestEmitter
from datahub.metadata.schema_classes import UpstreamLineageClass, UpstreamClass

emitter = DatahubRestEmitter("http://localhost:8080")

lineage = UpstreamLineageClass(
    upstreams=[
        UpstreamClass(
            dataset="urn:li:dataset:(urn:li:dataPlatform:snowflake,db.schema.source,PROD)",
            type="TRANSFORMED"
        )
    ]
)

4. Test SQL parsing with specific queries:

from datahub.utilities.sql_parser import SQLParser

parser = SQLParser("SELECT * FROM db.schema.table1 JOIN db.schema.table2")
tables = parser.get_tables()
print(tables)  # Should list all referenced tables

5. Increase query log lookback for historical lineage:

source:
  config:
    # Increase window for initial ingestion
    start_time: "-90d"

    # Use stateful ingestion to track progress
    stateful_ingestion:
      enabled: true

Query-based lineage quality depends on the SQL parser's ability to understand your specific SQL dialect and query patterns. Consider supplementing with view lineage and dbt lineage for comprehensive coverage.

• Lineage: SQL Parsing
• Snowflake Query Lineage