Skip to main content

LINEAGE_ADJACENCY_LIST table

The LINEAGE_ADJACENCY_LIST table is a flat list of directed lineage edges between assets and processes. Each row represents a single hop, where from_guid is the upstream asset or process and to_guid is the downstream asset or process.

Use this table to traverse lineage starting from any asset. Pair it with the ASSETS table to resolve GUIDs to human-readable names and types.

Columns​

The LINEAGE_ADJACENCY_LIST table includes the following columns:

ColumnDescription
from_guidThe GUID of the upstream asset or process in the edge
to_guidThe GUID of the downstream asset or process in the edge

Example queries​

The examples below run on Snowflake. Recursive CTE syntax is similar across data warehouse engines, but the cycle-detection helpers (ARRAY_CONSTRUCT, ARRAY_APPEND, ARRAY_CONTAINS, ::VARIANT) are Snowflake-specific—adapt them to your engine's array functions if needed.

For multi-hop queries that return direction, hop level, and asset names in a single result set, see Set up lineage tables for the optional LINEAGE helper view.

Upstream lineage​

Find every asset directly upstream of a target asset, joined to ASSETS for names and types.

-- Replace '--' with the GUID of the asset you're tracing from.
SELECT guid, asset_name, asset_type
FROM (
  WITH RECURSIVE lineage AS (
    SELECT from_guid, 1 AS level
    FROM gold.lineage_adjacency_list
    WHERE to_guid = '--'
  UNION ALL
    SELECT a.from_guid, l.level + 1
    FROM lineage l
    JOIN gold.lineage_adjacency_list a
      ON l.from_guid = a.to_guid
    WHERE l.level < 50
  )
  SELECT DISTINCT from_guid FROM lineage
) t
LEFT JOIN gold.assets ON from_guid = guid;

Downstream lineage​

Find every asset directly downstream of a source asset.

-- Replace '--' with the GUID of the asset you're tracing from.
SELECT guid, asset_name, asset_type
FROM (
  WITH RECURSIVE lineage AS (
    SELECT to_guid, 1 AS level
    FROM gold.lineage_adjacency_list
    WHERE from_guid = '--'
  UNION ALL
    SELECT a.to_guid, l.level + 1
    FROM lineage l
    JOIN gold.lineage_adjacency_list a
      ON l.to_guid = a.from_guid
    WHERE l.level < 50
  )
  SELECT DISTINCT to_guid FROM lineage
) t
LEFT JOIN gold.assets ON to_guid = guid;

Optional: Full-lineage stored procedure (Snowflake)​

If you frequently explore lineage from a single starting asset, this Snowflake stored procedure walks both directions in one call. It returns edges (not just nodes) annotated with direction (upstream or downstream) and level (hop distance), and joins both ends to ASSETS for names and types.

The procedure walks up to 100 hops in each direction. Cycle suppression isn't built in—use the cycle-detection variants in the sections earlier if your graph contains cycles.

-- Replace:
--   <CONTEXT_STORE_DB>  with your context store database
--   <CUSTOM_SCHEMA>     with the schema where you want the procedure to live

CREATE OR REPLACE PROCEDURE <CONTEXT_STORE_DB>.<CUSTOM_SCHEMA>.FULL_LINEAGE(BASE_GUID VARCHAR)
RETURNS TABLE (
    direction       VARCHAR,
    from_guid       VARCHAR,
    from_asset_name VARCHAR,
    from_asset_type VARCHAR,
    to_guid         VARCHAR,
    to_asset_name   VARCHAR,
    to_asset_type   VARCHAR,
    level           INT
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    res RESULTSET DEFAULT (
        WITH RECURSIVE upstream AS (
            SELECT
                from_guid,
                to_guid,
                1          AS level,
                'upstream' AS direction
            FROM <CONTEXT_STORE_DB>.GOLD.LINEAGE_ADJACENCY_LIST
            WHERE to_guid = :BASE_GUID

          UNION ALL

            SELECT
                a.from_guid,
                a.to_guid,
                l.level + 1,
                'upstream'
            FROM upstream l
            JOIN <CONTEXT_STORE_DB>.GOLD.LINEAGE_ADJACENCY_LIST a
                ON l.from_guid = a.to_guid
            WHERE l.level < 100
        ),

        downstream AS (
            SELECT
                from_guid,
                to_guid,
                1            AS level,
                'downstream' AS direction
            FROM <CONTEXT_STORE_DB>.GOLD.LINEAGE_ADJACENCY_LIST
            WHERE from_guid = :BASE_GUID

          UNION ALL

            SELECT
                a.from_guid,
                a.to_guid,
                l.level + 1,
                'downstream'
            FROM downstream l
            JOIN <CONTEXT_STORE_DB>.GOLD.LINEAGE_ADJACENCY_LIST a
                ON l.to_guid = a.from_guid
            WHERE l.level < 100
        ),

        combined AS (
            SELECT
                from_guid,
                to_guid,
                direction,
                MIN(level) AS level
            FROM (
                SELECT * FROM upstream
                UNION ALL
                SELECT * FROM downstream
            )
            GROUP BY from_guid, to_guid, direction
        )

        SELECT
            d.direction,
            d.from_guid,
            src.asset_name AS from_asset_name,
            src.asset_type AS from_asset_type,
            d.to_guid,
            tgt.asset_name AS to_asset_name,
            tgt.asset_type AS to_asset_type,
            d.level
        FROM combined d
        LEFT JOIN <CONTEXT_STORE_DB>.GOLD.ASSETS src ON d.from_guid = src.guid
        LEFT JOIN <CONTEXT_STORE_DB>.GOLD.ASSETS tgt ON d.to_guid = tgt.guid
        ORDER BY d.direction, d.level
    );
BEGIN
    RETURN TABLE(res);
END;
$$;

Call it with the GUID of the asset you want to explore:

CALL <CONTEXT_STORE_DB>.<CUSTOM_SCHEMA>.FULL_LINEAGE('<base_guid>');

See also​