lineage table and procedures

`lineage` table

The core lineage data table that consolidates and maintains data lineage relationships.

Description

An alpha version of the core lineage data table for Masthead’s customers. This table consolidates and maintains data lineage relationships by joining edge and node information from the Marcia lineage system, providing a clean interface for tracking data flow connections, target types, and last update timestamps.

Schema

Column name	Data type	Description
`source`	`STRING`	Name of the source data object (e.g. `project.dataset.table`, routine, or URI)
`source_type`	`STRING`	Type of source data object. For details, see the Object types section below.
`source_subtype`	`STRING`	Subtype of source data object. For details, see the Object types section.
`process_type`	`STRING`	Type of process connecting source and target. See Process types below.
`target`	`STRING`	Name of the target data object (e.g. `project.dataset.table`, routine, or URI)
`target_type`	`STRING`	Type of target data object. For details, see the Object types section.
`target_subtype`	`STRING`	Subtype of target data object. For details, see the Object types section.

Object types and subtypes

source_type and target_type describe the category of the data asset:

TABLE - Table-like objects in BigQuery
ROUTINE - Stored procedures or user-defined functions
URI - External import source or export destination URI
SERVICE_ACCOUNT - Identity executing jobs
LOOKER / LOOKER_STUDIO_REPORT / POWERBI_REPORT - BI dashboards and reporting assets
GOOGLE_SPREADSHEET - Connected Google Sheets
LINKED_DATASET_RESOURCE / LINKED_DATASET_CONSUMER - Analytics Hub linked datasets

source_subtype and target_subtype provide specific granularity:

DB_TABLE - Standard native BigQuery table
VIEW - Logical view
MATERIALIZED_VIEW - Materialized view
EXTERNAL - External table (e.g. Cloud Storage, Bigtable, Google Sheets)
SHARDED - Date-sharded table
SNAPSHOT - Table snapshot
CLONE - Table clone
LOOKER_DASHBOARD / LOOKER_LOOK / LOOKER_EXPLORE - Looker asset subtypes

Process types

process_type indicates the mechanism connecting the source and target:

PIPELINE - Transformation pipeline or scheduled job (e.g., dbt, Dataform, Airflow)
STREAM - Streaming ingestion process
LINKED_DATASET - Analytics Hub data sharing subscription
NULL - Direct dependency (such as a view referencing an underlying table)
PARTICIPANT - Service account or compute identity with an active relationship to the lineage node.

Table usage examples

View lineage relationships

-- View all lineage relationships for an account
SELECT *
FROM `masthead-prod`.DATASET_NAME.lineage
LIMIT 100;

Find downstream dependencies

-- Find all tables that depend on a specific source
SELECT target, target_type, target_subtype, process_type
FROM `masthead-prod`.DATASET_NAME.lineage
WHERE source = 'project_id.dataset_name.table_name'
  AND target_type = 'TABLE';

Find upstream sources

-- Find all upstream dependencies feeding into a target table
SELECT source, source_type, source_subtype, process_type
FROM `masthead-prod`.DATASET_NAME.lineage
WHERE target = 'project_id.dataset_name.table_name';

Find recently updated lineage relationships

-- Get recent lineage changes in the last 7 days
SELECT source, target, target_type, updated_at
FROM masthead-prod.DATASET_NAME.lineage
WHERE DATE(updated_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY updated_at DESC;

`list_lineage` procedure

A stored procedure for recursive lineage exploration both upstream and downstream from any data object.

Procedure description

An alpha version of programmatic lineage exploration for Masthead’s customers. This stored procedure enables recursive traversal of data lineage relationships both upstream and downstream from a given origin reference, providing a comprehensive view of data dependencies and their hierarchical relationships within the account’s data ecosystem.

Procedure signature

CALL `masthead-prod`.DATASET_NAME.list_lineage(origin_ref STRING)

Parameters

origin_ref: A STRING representing the reference of the data object to start lineage exploration from

Output schema

Column name	Data type	Description
`origin`	`STRING`	The reference of the starting point, for example, `project_id.dataset_name.table_name`
`direction`	`STRING`	`UPSTREAM` or `DOWNSTREAM`
`depth`	`INTEGER`	Distance from origin, starting at 1
`source`	`STRING`	Source object reference
`source_type`	`STRING`	Type of source object
`source_subtype`	`STRING`	Subtype of source object
`process_type`	`STRING`	Type of process connecting source and target
`target`	`STRING`	Target object reference
`target_type`	`STRING`	Type of target object
`target_subtype`	`STRING`	Subtype of target object

Procedure usage examples

Basic usage

-- Explore lineage for a specific table
CALL `masthead-prod`.DATASET_NAME.list_lineage('project_id.dataset_name.table_name');

Filtering results

-- Get only upstream dependencies
CALL `masthead-prod`.DATASET_NAME.list_lineage('project_id.dataset_name.table_name');
-- Then filter in your application or with a view:
-- WHERE direction = 'UPSTREAM'

-- Get only direct dependencies (depth 1)
CALL `masthead-prod`.DATASET_NAME.list_lineage('project_id.dataset_name.table_name');
-- Then filter: WHERE depth = 1

-- Get only downstream tables
CALL `masthead-prod`.DATASET_NAME.list_lineage('project_id.dataset_name.table_name');
-- Then filter: WHERE direction = 'DOWNSTREAM' AND target_type = 'TABLE'

Further analysis

-- Find the deepest dependency chain
CALL `masthead-prod`.DATASET_NAME.list_lineage('project_id.dataset_name.table_name');
-- Then analyze: SELECT MAX(depth) as max_depth FROM results

-- Identify critical upstream dependencies
CALL `masthead-prod`.DATASET_NAME.list_lineage('project_id.dataset_name.table_name');
-- Focus on: WHERE direction = 'UPSTREAM' AND depth <= 2

AI agent skills

To automate the traversal and analysis of data lineage programmatically using AI agents, such as Google Antigravity or Claude Code, you can configure pre-built agent skills. These skills help agents query the lineage table and call the list_lineage stored procedure to determine downstream dependencies and evaluate the blast radius of anomalies.

For details, see the AI agent skills guide.

Limitations

Performance: Large lineage graphs may have slower query performance.
Cycle Detection: The recursive queries include basic cycle prevention but complex cycles may still cause issues.
Data Freshness: Masthead updates lineage daily.
Cross-Project Dependencies: External project references may have limited detail.
Data Retention: Lineage includes relationships only if they changed within the account’s configured lookback window, which defaults to 30 days. This ensures lineage data remains current and relevant.