Materialized Views and View Lineage

Lineage for database views, especially materialized views, is not being extracted correctly or showing proper dependencies on base tables. Understanding how different view types are handled is important for complete lineage.

You Might Be Asking:

• How does DataHub handle view lineage?
• Why isn't my materialized view lineage showing?
• Can DataHub track view refresh schedules?

1. Enable view lineage extraction:

Example for Snowflake. This can be adapted for other data warehouses like BigQuery, Redshift, or PostgreSQL.

source:
  type: snowflake
  config:
    # Include views in ingestion
    include_views: true
    include_tables: true

    # Parse view DDL for lineage
    include_view_lineage: true
    parse_view_ddl: true

    # For materialized views
    include_materialized_views: true

2. Handle view lineage for different databases:

Examples for multiple database platforms. Each example can be used as a guide for similar database systems.

# Snowflake views
source:
  type: snowflake
  config:
    include_view_lineage: true
    # Lineage extracted from SHOW VIEWS output

# BigQuery views
source:
  type: bigquery
  config:
    include_views: true
    # Lineage from view SQL in INFORMATION_SCHEMA

# Postgres views
source:
  type: postgres
  config:
    include_views: true
    parse_view_ddl: true
    # Lineage parsed from pg_views.definition

# Redshift
source:
  type: redshift
  config:
    include_views: true
    include_late_binding_views: true
    # Special handling for late-binding views

3. Manually emit view lineage:

Example for Snowflake. This can be adapted for other data warehouses.

from datahub.emitter.mce_builder import make_dataset_urn
from datahub.metadata.schema_classes import (
    UpstreamLineageClass,
    UpstreamClass,
    DatasetPropertiesClass
)

# Define view with lineage to base tables
view_urn = make_dataset_urn("snowflake", "analytics_db.public.customer_summary_view")

# Create lineage to base tables
upstream_lineage = UpstreamLineageClass(
    upstreams=[
        UpstreamClass(
            dataset=make_dataset_urn("snowflake", "raw_db.public.customers"),
            type="VIEW"  # VIEW type for view dependencies
        ),
        UpstreamClass(
            dataset=make_dataset_urn("snowflake", "raw_db.public.orders"),
            type="VIEW"
        )
    ]
)

emitter.emit_mcp(
    entity_urn=view_urn,
    aspect_name="upstreamLineage",
    aspect=upstream_lineage
)

# Mark as view in properties
view_props = DatasetPropertiesClass(
    customProperties={
        "view_type": "materialized",
        "refresh_schedule": "daily",
        "last_refresh": "2025-01-15T10:00:00Z"
    }
)

emitter.emit_mcp(
    entity_urn=view_urn,
    aspect_name="datasetProperties",
    aspect=view_props
)

4. Track materialized view refresh:

# Emit operation metadata for view refreshes
from datahub.metadata.schema_classes import OperationClass, OperationTypeClass

operation = OperationClass(
    timestampMillis=int(time.time() * 1000),
    operationType=OperationTypeClass.UPDATE,
    lastUpdatedTimestamp=int(time.time() * 1000),
    actor="urn:li:corpuser:airflow",
    customOperationType="MATERIALIZED_VIEW_REFRESH"
)

emitter.emit_mcp(
    entity_urn=view_urn,
    aspect_name="operation",
    aspect=operation
)

5. Handle complex view dependencies:

Example SQL that DataHub's parser handles. This applies to most SQL-compliant databases.

-- Example: View with CTEs
CREATE VIEW complex_view AS
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) as total
  FROM orders
  GROUP BY customer_id
),
customer_info AS (
  SELECT * FROM customers WHERE status = 'active'
)
SELECT ci.*, ct.total
FROM customer_info ci
JOIN customer_totals ct ON ci.id = ct.customer_id;

-- DataHub will parse this and create lineage:
-- orders → complex_view
-- customers → complex_view

6. Query view lineage:

query getViewLineage {
  dataset(urn: "urn:li:dataset:(urn:li:dataPlatform:snowflake,db.schema.view,PROD)") {
    # Check if it's a view
    properties {
      customProperties {
        key
        value
      }
    }

    # View dependencies (base tables)
    upstreamLineage {
      entities {
        urn
        name
        type
      }
    }

    # What uses this view (downstream)
    downstreamLineage {
      entities {
        urn
        name
      }
    }
  }
}

View lineage depends on SQL parsing capabilities. Complex views with window functions, CTEs, or subqueries may have incomplete lineage. Materialized view refresh schedules should be documented in custom properties. Consider emitting operation events for view refreshes.

• View Lineage
• SQL Parsing