Lineage impact analysis

Use Lakehouse to understand the downstream impact of changes to tables, columns, or models before you deploy them to production.

This page provides SQL templates and guidance for impact analysis using the GOLD.ASSET_LOOKUP_TABLE and GOLD.FULL_LINEAGE tables in your Lakehouse.

Before you begin

Before you run these queries, make sure:

Your Lakehouse setup is complete and Lakehouse is enabled for your organization. If setup isn't complete, see Get started with Lakehouse.
The Gold layer is set up for your SQL engine. See Set up gold layer in Snowflake. For Databricks (Databricks setup guide) or BigQuery (BigQuery setup guide) setups, contact your Atlan Customer Success team.

You can run these queries directly on your lineage tables to understand and communicate the impact of upstream changes before you modify schemas, deprecate assets, or migrate data.

Impact analysis

List all assets that depend on a given source table, view, or model so teams can plan migrations, coordinate with owners, and avoid breaking downstream workloads.

Before making changes to a data asset, use impact analysis to list downstream consumers and plan updates with owners. Common scenarios include renaming columns used by dashboards, deprecating legacy tables, changing data types, migrating tables to new schemas, implementing row-level security, or upgrading dbt models with downstream dependencies.

Snowflake
Databricks
BigQuery

-- ============================================
-- IMPACT ANALYSIS: Find All Downstream Dependencies Before Making Changes
-- ============================================

SELECT 
    A.ASSET_NAME AS SOURCE_ASSET,              -- Asset you're planning to change
    A.ASSET_TYPE AS SOURCE_TYPE,               -- Type of source asset (Table, Column, etc.)
    A.ASSET_QUALIFIED_NAME AS SOURCE_PATH,     -- Full path to source asset
    A.CERTIFICATE_STATUS AS SOURCE_CERT,       -- Source certification status
    TO_TIMESTAMP_LTZ(A.UPDATED_AT / 1000) AS SOURCE_LAST_UPDATED,  -- When source was last updated
    
    L.RELATED_NAME AS IMPACTED_ASSET,          -- Name of downstream asset that will be affected
    L.RELATED_TYPE AS IMPACTED_TYPE,           -- Type of impacted asset (Dashboard, View, etc.)
    L.LEVEL AS DEPENDENCY_DEPTH,               -- How many hops downstream (1=direct, 2=2-hops, etc.)
    
    -- Get impacted asset details for stakeholder notification
    RA.ASSET_QUALIFIED_NAME AS IMPACTED_PATH,  -- Full path to impacted asset
    RA.OWNER_USERS AS IMPACTED_OWNERS,         -- Who to notify about the change
    RA.CONNECTOR_NAME AS IMPACTED_SYSTEM,      -- Which system the impacted asset is in
    RA.CERTIFICATE_STATUS AS IMPACTED_CERT_STATUS,  -- Business criticality indicator
    TO_TIMESTAMP_LTZ(RA.UPDATED_AT / 1000) AS IMPACTED_LAST_UPDATED,  -- When impacted asset was last updated
    
    -- Create notification priority based on dependency depth
    CASE 
        WHEN L.LEVEL = 1 THEN 'DIRECT DEPENDENCY - HIGH PRIORITY'
        WHEN L.LEVEL = 2 THEN 'SECONDARY DEPENDENCY - MEDIUM PRIORITY'
        ELSE 'INDIRECT DEPENDENCY - LOW PRIORITY'
    END AS NOTIFICATION_PRIORITY
    
FROM ASSETS A
INNER JOIN LINEAGE L 
    ON A.GUID = L.START_GUID                   -- Join on asset identifier
LEFT JOIN ASSETS RA 
    ON L.RELATED_GUID = RA.GUID                -- Get metadata for impacted assets
    
WHERE 
    -- START HERE: Asset you plan to change (use GUID or QUALIFIED_NAME)
    A.ASSET_QUALIFIED_NAME = 'default/snowflake/123/ANALYTICS/DIM_CUSTOMERS'
    AND A.ASSET_TYPE = 'Table'
    
    -- Look DOWNSTREAM to find what depends on it
    AND L.DIRECTION = 'DOWNSTREAM'
    
ORDER BY 
    L.LEVEL ASC,                               -- Show direct dependencies first (highest risk)
    L.RELATED_TYPE,                            -- Group by type (Dashboards, Tables, etc.)
    L.RELATED_NAME;                            -- Alphabetically within each type

USE atlan_gold;

-- ============================================
-- IMPACT ANALYSIS: Find All Downstream Dependencies Before Making Changes
-- ============================================
SELECT 
    AL.NAME AS SOURCE_ASSET,
    AL.TYPE_NAME AS SOURCE_TYPE,
    AL.QUALIFIED_NAME AS SOURCE_PATH,
    AL.CERTIFICATE_STATUS AS SOURCE_CERT,
    FL.RELATED_NAME AS IMPACTED_ASSET,
    FL.RELATED_TYPE AS IMPACTED_TYPE,
    FL.LEVEL AS DEPENDENCY_DEPTH,
    -- Get impacted asset details
    RAL.QUALIFIED_NAME AS IMPACTED_PATH,
    RAL.OWNER_USERS AS IMPACTED_OWNERS,
    RAL.CONNECTOR_NAME AS IMPACTED_SYSTEM,
    RAL.CERTIFICATE_STATUS AS IMPACTED_CERT_STATUS,
    -- Create notification list
    CASE 
        WHEN FL.LEVEL = 1 THEN 'DIRECT DEPENDENCY - HIGH PRIORITY'
        WHEN FL.LEVEL = 2 THEN 'SECONDARY DEPENDENCY - MEDIUM PRIORITY'
        ELSE 'INDIRECT DEPENDENCY - LOW PRIORITY'
    END AS NOTIFICATION_PRIORITY
FROM ASSET_LOOKUP_TABLE AL
INNER JOIN FULL_LINEAGE FL 
    ON AL.GUID = FL.START_GUID
LEFT JOIN ASSET_LOOKUP_TABLE RAL 
    ON FL.RELATED_GUID = RAL.GUID
WHERE 
    -- Start here: asset you plan to change
    AL.QUALIFIED_NAME = 'default/snowflake/123/ANALYTICS/DIM_CUSTOMERS'
    AND AL.TYPE_NAME = 'Table'
    -- Look downstream to find what depends on it
    AND FL.DIRECTION = 'DOWNSTREAM'
ORDER BY 
    FL.LEVEL ASC,
    FL.RELATED_TYPE,
    FL.RELATED_NAME;

SET @@dataset_id = 'ATLAN_GOLD';

-- ============================================
-- IMPACT ANALYSIS: Find All Downstream Dependencies Before Making Changes
-- ============================================
SELECT 
    A.ASSET_NAME AS SOURCE_ASSET,              -- Asset you're planning to change
    A.ASSET_TYPE AS SOURCE_TYPE,               -- Type of source asset (Table, Column, etc.)
    A.ASSET_QUALIFIED_NAME AS SOURCE_PATH,     -- Full path to source asset
    A.CERTIFICATE_STATUS AS SOURCE_CERT,       -- Source certification status
    TIMESTAMP_MILLIS(A.UPDATED_AT) AS SOURCE_LAST_UPDATED,  -- When source was last updated
    
    L.RELATED_NAME AS IMPACTED_ASSET,          -- Name of downstream asset that will be affected
    L.RELATED_TYPE AS IMPACTED_TYPE,           -- Type of impacted asset (Dashboard, View, etc.)
    L.LEVEL AS DEPENDENCY_DEPTH,               -- How many hops downstream (1=direct, 2=2-hops, etc.)
    
    -- Get impacted asset details for stakeholder notification
    RA.ASSET_QUALIFIED_NAME AS IMPACTED_PATH,  -- Full path to impacted asset
    RA.OWNER_USERS AS IMPACTED_OWNERS,         -- Who to notify about the change
    RA.CONNECTOR_NAME AS IMPACTED_SYSTEM,      -- Which system the impacted asset is in
    RA.CERTIFICATE_STATUS AS IMPACTED_CERT_STATUS,  -- Business criticality indicator
    TIMESTAMP_MILLIS(RA.UPDATED_AT) AS IMPACTED_LAST_UPDATED,  -- When impacted asset was last updated
    
    -- Create notification priority based on dependency depth
    CASE 
        WHEN L.LEVEL = 1 THEN 'DIRECT DEPENDENCY - HIGH PRIORITY'
        WHEN L.LEVEL = 2 THEN 'SECONDARY DEPENDENCY - MEDIUM PRIORITY'
        ELSE 'INDIRECT DEPENDENCY - LOW PRIORITY'
    END AS NOTIFICATION_PRIORITY
    
FROM ASSETS A
INNER JOIN LINEAGE L 
    ON A.GUID = L.START_GUID                   -- Join on asset identifier
LEFT JOIN ASSETS RA 
    ON L.RELATED_GUID = RA.GUID                -- Get metadata for impacted assets
    
WHERE 
    -- START HERE: Asset you plan to change (use GUID or QUALIFIED_NAME)
    -- A.ASSET_QUALIFIED_NAME = 'default/snowflake/123/ANALYTICS/DIM_CUSTOMERS'
    -- AND A.ASSET_TYPE = 'Table'
    A.GUID = 'ff0f00d5-dde5-4cf9-b199-2ab09a961bd2'
    -- Look DOWNSTREAM to find what depends on it
    AND L.DIRECTION = 'DOWNSTREAM'
    
ORDER BY 
    L.LEVEL ASC,                               -- Show direct dependencies first (highest risk)
    L.RELATED_TYPE,                            -- Group by type (Dashboards, Tables, etc.)
    L.RELATED_NAME;                            -- Alphabetically within each type

Sample output

SOURCE_ASSET	SOURCE_TYPE	IMPACTED_ASSET	IMPACTED_TYPE	DEPENDENCY_DEPTH	IMPACTED_SYSTEM	NOTIFICATION_PRIORITY
DIM_CUSTOMER	Table	DIM_CUSTOMER	PowerBITable	1	powerbi	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	DIM_CUSTOMER	PowerBITable	1	powerbi	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	DIM_CUSTOMER	PowerBITable	1	powerbi	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	DIM_CUSTOMER	PowerBITable	1	powerbi	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	DIM_CUSTOMER	PowerBITable	1	powerbi	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	dim_Customer	PowerBITable	1	powerbi	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	DBx.qvd	QlikDataset	1	qlik-sense	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	dim_customer	QlikDataset	1	qlik-sense	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	ORDERS_DENORMALIZED	Table	1	snowflake	DIRECT DEPENDENCY - HIGH PRIORITY
DIM_CUSTOMER	Table	Demo_DataSource_WWI	TableauDatasource	1	tableau	DIRECT DEPENDENCY - HIGH PRIORITY

Before you begin​

Impact analysis​

Before you begin

Impact analysis