Page Comparison

This page will guide you through the setup of Snowflake in K using the direct connect method.

Integration details

...

Create a Snowflake user with read access to following tables views in the Snowflake database.

• account_usage.history

• account_usage.views

• account_usage.tables

• account_usage.columns

• account_usage.copy_history

• account_usage.grants_to_roles

• account_usage.grants_to_users

• account_usage.schemata

• account_usage.databases

• account_usage.policy_references

Ability to run

• SHOW STREAMS IN ACCOUNT

• SHOW PRIMARY KEYS IN ACCOUNT

There are 2 options to create this user. Pick one approach that best suits your needs.

Option 1: Creating a user with access to Snowflake Account Usage Schema

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;

--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];

Option 2: Creating a user with access to specific views of Snowflake Account Usage Schema

To create a user with specific access to metadata in Snowflake Account Usage, you will need to create a new Snowflake database with views that select from the Snowflake database. This is a known Snowflake limitation.

--Log in with a user that has the permissions to create a role/user

-- create a new database
create database CATALOG_METADATA;

-- create a new schema 
create schema CATALOG_METADATA.ACCOUNT_USAGE;

— account_usage.access_history
create view CATALOG_METADATA.ACCOUNT_USAGE.ACCESS_HISTORY 
    as select * from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;

-- account_usage.views
create view CATALOG_METADATA.ACCOUNT_USAGE.VIEWS 
    as select * from SNOWFLAKE.ACCOUNT_USAGE.VIEWS;

-- account_usage.tables
create view CATALOG_METADATA.ACCOUNT_USAGE.TABLES 
    as select * from SNOWFLAKE.ACCOUNT_USAGE.TABLES;

-- account_usage.columns
create view CATALOG_METADATA.ACCOUNT_USAGE.COLUMNS
    as select * from SNOWFLAKE.ACCOUNT_USAGE.COLUMNS;

-- account_usage.copy_history
create view CATALOG_METADATA.ACCOUNT_USAGE.COPY_HISTORY
    as select * from SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY;

-- account_usage.grant_to_roles
create view CATALOG_METADATA.ACCOUNT_USAGE.GRANTS_TO_ROLES
    as select * from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES;

-- account_usage.grant_to_grant_to_users
create view CATALOG_METADATA.ACCOUNT_USAGE.GRANTS_TO_USERS
    as select * from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS;

-- account_usage.schemata
create view CATALOG_METADATA.ACCOUNT_USAGE.SCHEMATA
    as select * from SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA;

-- account_usage.databases
create view CATALOG_METADATA.ACCOUNT_USAGE.DATABASES
    as select * from SNOWFLAKE.ACCOUNT_USAGE.DATABASES;
    
-- account_usage.policy_references
create view CATALOG_METADATA.ACCOUNT_USAGE.POLICY_REFERENCES
    as select * from SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES;
    
-- create a new role
create role CATALOG_READ_ONLY;

-- grant access for the role to a warehouse and the database and schema created
grant usage on warehouse [MY_WAREHOUSE] to role CATALOG_READ_ONLY;
grant usage, monitor on database CATALOG_METADATA to role CATALOG_READ_ONLY;
grant usage, monitor on schema CATALOG_METADATA.ACCOUNT_USAGE to role CATALOG_READ_ONLY;
grant select on all views in schema CATALOG_METADATA.ACCOUNT_USAGE to CATALOG_READ_ONLY;
grant select on future views in schema CATALOG_METADATA.ACCOUNT_USAGE to CATALOG_READ_ONLY;

-- create a new Kada user
create user [kada_user] password=[‘<add password>’] default_role = CATALOG_READ_ONLY default_warehouse = [warehouse];

Configuring User Authentication

Configure the user to use key-pair authentication (Recommended)

https://docs.snowflake.com/en/user-guide/key-pair-auth

or set the user type to be a legacy user if you wish to use only a Password for authentication (not recommended).

Set the user type to legacy using the following command

ALTER USER [kada_user] SET TYPE = LEGACY_SERVICE

From the above record down the following to be used for the setup

1. User Kada user name /

2. Password

3. Private Key (if configured)

4. Role

5. Warehouse

6. (If creating a new database for metadata) Database name

7. Snowflake account (found in the URL of your Snowflake instance - between https:// and .snowflakecomputing.com/…)

...

...

Step 2) Connecting K to Snowflake

...

• 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 DB in K

  • Host: Add your Snowflake Account (found in your Snowflake URL)

    • Omit the https:// from the URL

  • 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

  • Enable data masking (optional): Select this option if you wish K to mask literals in Snowflake code objects.

  • Enterprise (optional): Select this option to leverage Enterprise features such as Object dependencies that improve lineage coverage

    • Note object dependency limitations here - /wiki/spaces/DAT/pages/1265434659

  • Use Key Pair Authentication (optional): Select this option if the Snowflake user is configured for key pair authentication

• 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 Host settingaccount information part of your Snowflake account. Do not include snowflakecomputing.com

  • Username: Add the Snowflake user name

  • Password: Add the Snowflake user password

...

• • Private key: Add the Private key for the Snowflake user
    
    

    Image Added

    
    

• Test your connection and click Save

• Select the Databases you wish to load into K and click Finish Setup

  • All databases will be listed. If you have a lot of databases this may take a few seconds to load

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

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

...