Snowflake lineage configuration

To import lineage metadata from Snowflake, create a connection, data source definition and metadata import job.

Snowflake is a cloud-based data management platform that provides enterprises with a flexible, scalable architecture for effective data collaboration.

Processed metadata

The following Snowflake metadata is processed and displayed in lineage:

  • Data dictionaries
  • Scripts
  • Views
  • Functions
  • External tables
  • Procedures

Limitations

The following limitations apply:

  • The following data is not processed:
    • Snowpark (Java, Scala, Python)
    • Javascript Snowflake procedures
    • UDFs (User Defined Functions) in Java, Python, Javascript
    • Secure UDFs
    • Assets that use dynamically executed code through the 'EXECUTE IMMEDIATE' statement.
    • Recently introduced or changed features and some features that are rarely used.
    • Extraction of unexecuted tasks
    • Lineage for tables created by using CREATE TABLE ... AS SELECT ..., as the DDL is not available in Snowflake.
    • Lineage to Snowsight charts and dashboards
    • Window function syntax with statements like SUM(...) OVER (PARTITION BY ... [ORDER BY] ...)
  • Tasks are extracted from TASK_HISTORY in both extraction modes, as Snowflake currently does not provide appropriate views to extract defined tasks.
    • In INFORMATION_SCHEMA mode, TASK_HISTORY returns task activity within the last seven days or the next scheduled execution within the next eight days. It is limited to 10,000 records, showing executions with the most recent timestamp.
    • In ACCOUNT_USAGE mode, TASK_HISTORY returns the history of task usage within the last 365 days.
  • Snowflake scanner does not use the connectors truststore. In most cases, no additional configuration is required, because Snowflake certificates are typically signed by root and intermediate certificate authorities that are already included in the Java default truststore. If a custom certificate is required, it must be imported into the Java default truststore. For instructions, see Importing certificates into Java default truststore.
  • Streams are extracted by using SHOW STREAMS in both extraction modes, as Snowflake currently does not provide appropriate views to extract defined streams. This limits the number of extracted streams to 10,000 per schema.

Prerequisite configuration

Before you import lineage metadata, ensure that the following prerequisites are met:

  • You have permission to access all addresses, as described in Allowing Host names in Snowflake documentation.
  • A Snowflake account that is the root account representing a server provided by Snowflake is present.
  • You have a user role assigned with the appropriate privileges based on the extraction mode that you plan to use:
    • INFORMATION_SCHEMA mode
      For each database included in the extraction, the user’s role must have the following privileges:
      • USAGE on the extracted database
      • USAGE on each extracted schema inside the extracted database
      • At least one privilege, for example, SELECT, on every extracted Snowflake object, such as a table, view, or procedure.
      • MONITOR EXECUTION to enable extraction of task information from TASK_HISTORY
    • ACCOUNT_USAGE mode
      The user’s role must include IMPORTED PRIVILEGES on the Snowflake database that contains the ACCOUNT_USAGE schema.
      To grant this privilege, run GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO <role>.
    • Stream extraction (optional)
      To enable extraction of streams, the user’s role must also have these privileges:
      • USAGE on the extracted database
      • USAGE on each extracted schema within the database
      • At least one privilege, for example, SELECT on each extracted stream

Creating a metadata import asset

Data source connection

To connect to the data source from which you want to import lineage metadata, you need to select a data source definition and a connection. You can create them before you start creating the metadata import, or you can create them when you create and configure the metadata import asset.

Data source definition

Select Snowflake as the data source type.

Connection

For connection details, see Snowflake connection.

Include and exclude lists

You can include or exclude assets up to the schema level. Provide databases and schemas in the format database/schema. Each part is evaluated as a regular expression. Assets which are added later in the data source will also be included or excluded if they match the conditions specified in the lists. Example values:

  • myDB/: all schemas in myDB database.
  • myDB2/.*: all schemas in myDB2 database.
  • myDB3/mySchema1: mySchema1 schema from myDB3 database.
  • myDB4/mySchema[1-5]: any schema in my myDB4 database with a name that starts with mySchema and ends with a digit between 1 and 5.

External inputs

If you use external Snowflake SQL scripts, you can add them in a .zip file as an external input. You can organize the structure of a .zip file as subfolders that represent databases and schemas. After the scripts are scanned, they are added under respective databases and schemas in the selected catalog or project. The .zip file can have the following structure:

    <database_name>
        <schema_name>
           <script_name.sql>
    <database_name>
        <script_name.sql>
    <script_name.sql>
    replace.csv

The replace.csv file contains placeholder replacements for the scripts that are added in the .zip file. For more information about the format, see Placeholder replacements.

Advanced import options

Table stages extraction
You can add a regular expression to list table stages from which you want staged files to be extracted. Use a fully qualified name and enclose each segment with double quotation marks. Leave the field empty if you do not want to extract staged files from any table stages. Example value:
\\\"mydb\\\"\\.\\\"schema1\\\"\\.\\\".*\\\"|\\\"mydb\\\"\\.\\\"myschema\\\"\\.\\\"abc.*\\\
Transformation logic extraction
You can enable building transformation logic descriptions from SQL code in SQL scripts.

Learn more