Object Dependencies¶

Supported object dependencies¶

Snowflake supports referencing objects and referenced objects as follows:

Note that Snowflake supports only the following objects in the context of data sharing:

For more details, see the Usage Notes section of the OBJECT_DEPENDENCIES view.

Benefits¶

Identifying object dependencies can provide insight into data tracking use cases as follows:

Impact analysis:

Knowing the object dependency allows data stewards to identify the relationships between referencing objects and referenced objects to ensure that updates to referenced objects do not adversely impact users of the referencing object.

For example, a table owner plans to add a column to a table. Querying the OBJECT_DEPENDENCIES view based on the table name returns all of the objects (e.g. views) that will be affected.

The data steward can then coordinate a plan of action to ensure that the timing of table and view updates do not result in any broken queries that would adversely affect users querying the views created from the table.

Compliance:

The object dependency relationship helps the compliance officer identify the relationship between sensitive data sources (i.e. referenced object) and data targets (i.e. referencing object). The compliance officer can then decide how best to update the referenced object and referencing object based on the compliance requirements (e.g. GDPR).

Data integrity:

The object dependency relationship helps primary data professionals, such as analysts, scientists, compliance officers, and other business users, to have confidence that the data originates from a trustworthy source.

Limitations¶

In addition to the view usage notes, note the following limitations when querying the OBJECT_DEPENDENCIES view:

Session parameters:

Snowflake cannot accurately compute the dependencies of objects that include session parameters in their definitions because session parameters can take on different values depending on the context.

Snowflake recommends not using session variables in view and function definitions.

Snowflake implementations:

This view does not capture dependencies that are necessary for Snowflake implementations. For example, the view does not record the dependency necessary to create a new table from the clone of another table.

Object resolution:

If a view definition uses a function to call an object to create the view, or if an object is called inside another function or view, Snowflake does not record an object dependency. For example:

create or replace view v_on_stage_function
as
select *
from T1
where get_presigned_url(@stage1, 'data_0.csv.gz')
is not null;

Copy

In this example, the function get_presigned_url calls the stage stage1. Snowflake does not record that the view named v_on_stage_function depends on the stage named stage1.

Broken dependencies:

If the dependency type value is BY_NAME_AND_ID and an object dependency changes due to a CREATE OR REPLACE or ALTER operation on an object, Snowflake only records the object dependency prior to these operations.

Snowflake does not record the object dependency in the view query result after these operations because the result is a broken reference.

Object dependencies with snowflake features and services¶

External objects:

Snowflake tracks object dependencies for Snowflake objects only. For example, if a Snowflake object depends on an Amazon S3 bucket, this view does not record the dependency on the bucket because the bucket is an Amazon object, not a Snowflake object.

Replication:

While a secondary object depends on the primary object, this view does not record dependencies due to a replication operation.

Data sharing:

For provider accounts, this view does not allow a data sharing provider account to determine dependent objects in the data sharing consumer account. For example, a data sharing provider creates a view and shares the view. The data sharing provider cannot use this view to determine any object in the consumer account that was created from the shared view (e.g. new tables or views).

For consumer accounts, this view does not allow a data sharing consumer account to determine dependent objects in the data sharing provider account. For example, if a data sharing consumer account uses a UDF made available by the data sharing provider account, the data sharing consumer cannot use this view to identify any objects the shared UDF depends on.

For more details, refer to the Data sharing usage notes.

Show objects depending on an external table¶

Create a materialized view named sales_view from the external table named sales_staging_table:

CREATE OR REPLACE MATERIALIZED VIEW sales_view AS SELECT * FROM sales_staging_table;

Copy

Query the OBJECT_DEPENDENCIES view in the Account Usage schema of the shared SNOWFLAKE database. Note that the materialized view is the referencing_object_name and the external table is the referenced_object_domain:

SELECT referencing_object_name, referencing_object_domain, referenced_object_name, referenced_object_domain
FROM snowflake.account_usage.object_dependencies
WHERE referenced_object_name = 'SALES_STAGING_TABLE' and referenced_object_domain = 'EXTERNAL TABLE';

Copy

+-------------------------+---------------------------+------------------------+--------------------------+
| REFERENCING_OBJECT_NAME | REFERENCING_OBJECT_DOMAIN | REFERENCED_OBJECT_NAME | REFERENCED_OBJECT_DOMAIN |
+-------------------------+---------------------------+------------------------+--------------------------+
| SALES_VIEW              | MATERIALIZED VIEW         | SALES_STAGING_TABLE    | EXTERNAL TABLE           |
+-------------------------+---------------------------+------------------------+--------------------------+

Impact analysis: Find the Objects referenced by a table¶

Consider a base table named SALES_NA, where NA indicates North America, US indicates United States, and CAL indicates California, with a series of nested views:

• (table) SALES_NA » (view) NORTH_AMERICA_SALES » (view) US_SALES

• (table) SALES_NA » (view) NORTH_AMERICA_SALES » (view) CAL_SALES

To create the table and nested views, execute the following commands:

CREATE TABLE sales_na(product string);
CREATE OR REPLACE VIEW north_america_sales AS SELECT * FROM sales_na;
CREATE VIEW us_sales AS SELECT * FROM north_america_sales;
CREATE VIEW cal_sales AS SELECT * FROM north_america_sales;

Copy

Similarly, consider the relationship of the base table SALES_NA to its nested views, and consider the base table SALES_UK, where UK indicates the United Kingdom, to its nested view.

Note that two different views serve as source objects to derive the view named GLOBAL_SALES:

• (table) SALES_NA » (view) NORTH_AMERICA_SALES » (view) GLOBAL_SALES

• (table) SALES_UK » (view) GLOBAL_SALES

To create these nested views, execute the following commands:

CREATE TABLE sales_uk (product string);
CREATE VIEW global_sales AS SELECT * FROM sales_uk UNION ALL SELECT * FROM north_america_sales;

Copy

Query the OBJECT_DEPENDENCIES view in the Account Usage schema of the shared SNOWFLAKE database to determine the object references for the table SALES_NA. Note the fourth row in the query result, which specifies the table SALES_NA but does not reference the table SALES_UK:

WITH RECURSIVE referenced_cte
(object_name_path, referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id)
    AS
      (
        SELECT referenced_object_name || '-->' || referencing_object_name as object_name_path,
               referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id
          FROM snowflake.account_usage.object_dependencies referencing
          WHERE true
            AND referenced_object_name = 'SALES_NA' AND referenced_object_domain='TABLE'

        UNION ALL

        SELECT object_name_path || '-->' || referencing.referencing_object_name,
              referencing.referenced_object_name, referencing.referenced_object_domain, referencing.referencing_object_domain, referencing.referencing_object_name,
              referencing.referenced_object_id, referencing.referencing_object_id
          FROM snowflake.account_usage.object_dependencies referencing JOIN referenced_cte
            ON referencing.referenced_object_id = referenced_cte.referencing_object_id
            AND referencing.referenced_object_domain = referenced_cte.referencing_object_domain
      )

  SELECT object_name_path, referenced_object_name, referenced_object_domain, referencing_object_name, referencing_object_domain
    FROM referenced_cte
;

Copy

+-----------------------------------------------+------------------------+--------------------------+-------------------------+---------------------------+
| OBJECT_NAME_PATH                              | REFERENCED_OBJECT_NAME | REFERENCED_OBJECT_DOMAIN | REFERENCING_OBJECT_NAME | REFERENCING_OBJECT_DOMAIN |
+-----------------------------------------------+------------------------+--------------------------+-------------------------+---------------------------+
| SALES_NA-->NORTH_AMERICA_SALES                | SALES_NA               | TABLE                    | NORTH_AMERICA_SALES     | VIEW                      |
| SALES_NA-->NORTH_AMERICA_SALES-->CAL_SALES    | NORTH_AMERICA_SALES    | VIEW                     | CAL_SALES               | VIEW                      |
| SALES_NA-->NORTH_AMERICA_SALES-->US_SALES     | NORTH_AMERICA_SALES    | VIEW                     | US_SALES                | VIEW                      |
| SALES_NA-->NORTH_AMERICA_SALES-->GLOBAL_SALES | NORTH_AMERICA_SALES    | VIEW                     | GLOBAL_SALES            | VIEW                      |
+-----------------------------------------------+------------------------+--------------------------+-------------------------+---------------------------+

GDPR: Find the data source for a given view¶

Derived objects (e.g. views, CTAS) can be created from many different source objects to provide a custom view or dashboard. To meet regulatory requirements such as GDPR, compliance officers and auditors need to be able to trace data from a given object to its original data source.

For example, the view GLOBAL_SALES is derived from two different dependency paths that point to two different base tables:

• (table) SALES_NA » (view) NORTH_AMERICA_SALES » (view) GLOBAL_SALES

• (table) SALES_UK » (view) GLOBAL_SALES

To create these nested views, execute the following commands:

CREATE TABLE sales_na (product string);
CREATE OR REPLACE VIEW north_america_sales AS SELECT * FROM sales_na;
CREATE TABLE sales_uk (product string);
CREATE VIEW global_sales AS SELECT * FROM sales_uk UNION ALL SELECT * FROM north_america_sales;

Copy

Query the OBJECT_DEPENDENCIES view in the Account Usage schema of the shared SNOWFLAKE database to find the data source(s) of the view GLOBAL_SALES. Each row in the query result specifies a dependency path to a unique object.

WITH RECURSIVE referenced_cte
(object_name_path, referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id)
    AS
      (
        SELECT referenced_object_name || '<--' || referencing_object_name AS object_name_path,
               referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id
          from snowflake.account_usage.object_dependencies referencing
          WHERE true
            AND referencing_object_name = 'GLOBAL_SALES' and referencing_object_domain='VIEW'

        UNION ALL

        SELECT referencing.referenced_object_name || '<--' || object_name_path,
              referencing.referenced_object_name, referencing.referenced_object_domain, referencing.referencing_object_domain, referencing.referencing_object_name,
              referencing.referenced_object_id, referencing.referencing_object_id
          FROM snowflake.account_usage.object_dependencies referencing JOIN referenced_cte
            ON referencing.referencing_object_id = referenced_cte.referenced_object_id
            AND referencing.referencing_object_domain = referenced_cte.referenced_object_domain
      )

  SELECT object_name_path, referencing_object_name, referencing_object_domain, referenced_object_name, referenced_object_domain
    FROM referenced_cte
;

Copy

+-----------------------------------------------+-------------------------+---------------------------+------------------------+--------------------------+
| OBJECT_NAME_PATH                              | REFERENCING_OBJECT_NAME | REFERENCING_OBJECT_DOMAIN | REFERENCED_OBJECT_NAME | REFERENCED_OBJECT_DOMAIN |
+-----------------------------------------------+-------------------------+---------------------------+------------------------+--------------------------+
| SALES_UK<--GLOBAL_SALES                       | GLOBAL_SALES            | VIEW                      | SALES_UK               | TABLE                    |
| NORTH_AMERICA_SALES<--GLOBAL_SALES            | GLOBAL_SALES            | VIEW                      | NORTH_AMERICA_SALES    | VIEW                     |
| SALES_NA<--NORTH_AMERICA_SALES<--GLOBAL_SALES | NORTH_AMERICA_SALES     | VIEW                      | SALES_NA               | TABLE                    |
+-----------------------------------------------+-------------------------+---------------------------+------------------------+--------------------------+

Data sharing¶

Consider the following table, which is an excerpt from the OBJECT_DEPENDENCIES view in the consumer account, where:

• V1 specifies a view that the consumer creates from a shared object.

• S_V1 specifies a view that the provider shares.

• S_T1 specifies a table that the provider shares.

Given this table, note the following:

• If the provider revokes S_T1 from the share, the consumer continues to see rows that specify S_T1 (row 2) in their local view as long as S_T1 was not renamed prior to the revocation.

• If the provider drops a table or view in their account, the table or view is no longer included in the share. The local consumer view preserves existing records for the dropped table or view because the table or view was shared prior to the drop operation in the provider account.

  The consumer cannot observe view changes in the provider account.