Document toolboxDocument toolbox

Snowflake Data Metrics Functions

Owned by Dean Nguyen
Last updated: Sept 01, 2024
4 min read

Open in new tab

This page will guide you through the setup of Snowflake Data Metrics Functions (DMF) for integration with K using the direct connect method.

Integration details

Scope

Included

Comments

Scope

Included

Comments

Metadata

YES

 

Usage

YES

 

Data Quality

YES

See known limitations below

Known limitations

K does NOT currently support the out of the box DMFs (SNOWFLAKE.CORE).

K only uses user defined DMFs because K measures DQ results as a % of rows that meet the test condition.

K expects the DMF result to be a value between 0 to 100

Out of the box DMFs measure the number of rows that meet the test condition which does not provide sufficient context

Replacement DMFs for out of the box DMFs are available here Setting up Snowflake Data Metrics Functions

Step 1) Establish Snowflake Access

Ensure that you have setup Snowflake in K as per Snowflake

We recommend you use the same user for the Snowflake integration as the same user for DMF integration (option 1). A new user can be created specifically for this integration (option 2).

Add the following grants to the K user

 

Option 1: Extending the K user with access to Snowflake DMF

Assuming you followed the Snowflake integration instructions and created a role called CATALOG_READ_ONLY you can follow the steps to add grants to extend the user to be able to read Snowflake DMF details

--Log in with a user that has the permissions to assign/update roles --Add the following DQ related roles to the KADA role created previously GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE CATALOG_READ_ONLY; GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE CATALOG_READ_ONLY GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE CATALOG_READ_ONLY

 

Option 2: Creating a user with access to Snowflake DMF

To create a user with general access to metadata available in Snowflake Account Usage schema

--Log in with a user that has the permissions to create a role/user --Create a new role for the Catalog user Create role CATALOG_READ_ONLY; --Grant the role access to the Account usage schema grant imported privileges on database Snowflake to CATALOG_READ_ONLY; grant select on all tables in schema SNOWFLAKE.ACCOUNT_USAGE to CATALOG_READ_ONLY; grant monitor on account to role CATALOG_READ_ONLY; GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE CATALOG_READ_ONLY; GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE CATALOG_READ_ONLY GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE CATALOG_READ_ONLY --Create a new user for K and grant it the role (remove the []) create user [kada_user] password=['abc123!@#'] default_role = CATALOG_READ_ONLY default_warehouse = [warehouse];

 

Snowflake integration uses username/password. Using keys will be supported in an upcoming release. If keys are required consider using the Snowflake DMF collector

Step 2) Connecting K to Snowflake

  • Select Platform Settings in the side bar

  • In the pop-out side panel, under Integrations click on Sources

  • Click Add Source and select Snowflake


    image-20240831-082519.png



  • Select Direct Connect and add your Snowflake details and click Next

  • Fill in the Source Settings and click Next

    • Name: The name you wish to give your Snowflake DMF in K e.g. Snowflake DMF

    • Host: Add a host value

      • If you use your Snowflake account value make sure to add -dq at the end to differentiate this source from your Snowflake source with the same host value.

    • Information Database Role: Add the role the user should use

    • Information Database: Default is Snowflake if using Snowflake DB. Otherwise enter the Database created to store metadata views from Step 1

    • Warehouse: Add the warehouse the user should use

    • Snowflake Host: Add your Snowflake account (excluding the https:// at the start)

      • e.g. abc123.australia-east.azure.snowflakecomputing.com

 

  • Add the Connection details and click Save & Next when connection is successful

    • Snowflake account: Use the same details (Snowflake account) you previously added in the Snowflake Host setting however this time remove the .snowflakecomputing.com at the end

      • e.g abc123.australia-east.azure

    • Username: Add the Snowflake user name

    • Password: Add the Snowflake user password

  • Test your connection and click Save

  • Click Finish Setup to create your new Snowflake DMF source

  • Return to the Sources page and locate the new Snowflake DMF source that you loaded

  • Click on the clock icon to select Edit Schedule and set your preferred schedule for the Snowflake load

 

Note that scheduling a source can take up to 15 minutes to propagate the change.

Step 3) Manually run an ad hoc load to test Snowflake setup

  • Next to your new Source, click on the Run manual load icon

  • Confirm how you want the source to be loaded

  • After the source load is triggered, a pop up bar will appear taking you to the Monitor tab in the Batch Manager page. This is the usual page you visit to view the progress of source loads

 

A manual source load will also require a manual run of

  • DAILY

  • GATHER_METRICS_AND_STATS

To load all metrics and indexes with the manually loaded metadata. These can be found in the Batch Manager page