Table Lineage with Recursive CTE using view definition in information schema

Question

I have the problem that in our Snowflake DWH views were built on of top views (& again on top of views). If am now trying to change the underlying table the first view is derived from, I am afraid to break things.

Heres an example:

We have a table with REGISTRATIONS for several countries. Based on that table a view is created only showing data from US (REGISTRATIONS_US). This view is being used to built a view containing US data from lets say 2021 (REGISTRATIONS_US_2021). This view is again being used to build a view containing data from US in 2021 for a specific month, lets say July (REGISTRATIONS_US_202106). It can also happen that a view is used to generate more than one view (see for example REGISTRATIONS_US_NYC in the table provided below).

If I would now change the registrations table all the related views are affected. Therefore I need to find out how the views are related.

The only information I have is the one available in INFORMATION_SCHEMA:

TABLE_NAME	VIEW_DEFINITION
REGISTRATIONS_US	CREATE VIEW REGISTRATIONS_US AS SELECT * FROM REGISTRATIONS WHERE market = 'US'
REGISTRATIONS_US_2021	CREATE VIEW REGISTRATIONS_US_2021 AS SELECT * FROM REGISTRATIONS_US WHERE year = 2021
REGISTRATIONS_US_202106	CREATE VIEW REGISTRATIONS_US_202106 AS SELECT * FROM REGISTRATIONS_US_2021 WHERE month_id = '202106'
REGISTRATIONS_US_NYC	CREATE VIEW REGISTRATIONS_US_NYC AS SELECT * FROM REGISTRATIONS_US WHERE city = 'NYC'

I would like to gain the following solution:

LINEAGE
REGISTRATIONS_US > REGISTRATIONS_US_NYC
REGISTRATIONS_US > REGISTRATIONS_US_2021 > REGISTRATIONS_US_202106

I guess that should be somehow possible using a recursive CTE. But I just cannot imagine how since most examples of recursive CTEs to be found on Google already show the hierarchy in the table based on ids (typical manager/ employee example).

I hope my question is clear & someone can help.

user_5070879 · Answer

GET_OBJECT_REFERENCES could be used to track dependencies between objects:

Returns a list of objects that a specified object references. Input is currently limited to the name of a view.

select * 
from table(get_object_references(
             database_name=>'...', 
             schema_name=>'public', 
             object_name=>'REGISTRATIONS_US_NYC')
          );

EDIT:

OBJECT_DEPENDENCIES:

An object dependency means that in order to operate on an object, the object that is being operated on must reference metadata for itself or reference metadata for at least one other object.

Prep:

CREATE OR REPLACE TABLE REGISTRATIONS(
  market STRING,
  year INT,
  month_id INT,
  city STRING
);

CREATE VIEW REGISTRATIONS_US AS
SELECT * 
FROM REGISTRATIONS
WHERE market = 'US';

CREATE VIEW REGISTRATIONS_US_2021 AS
SELECT * FROM REGISTRATIONS_US WHERE year = 2021;

CREATE VIEW REGISTRATIONS_US_202106 AS
SELECT * FROM REGISTRATIONS_US_2021 WHERE month_id = '202106';

CREATE VIEW REGISTRATIONS_US_NYC AS
SELECT * FROM REGISTRATIONS_US WHERE city = 'NYC';

Query (Source: Querying the OBJECT_DEPENDENCIES View):

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 = 'REGISTRATIONS' 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;

Output:

Table Lineage with Recursive CTE using view definition in information schema

1 Answers