This page will guide you through the setup of Snowflake in K using the direct connect method.
Integration details
|
Scope |
Included |
Comments |
|---|---|---|
|
Metadata |
YES |
See below for know limitations |
|
Lineage |
YES |
|
|
Usage |
YES |
|
|
Sensitive Data Scanner |
YES |
|
Known limitations
-
Tasks are not currently included in the metadata extracted from Snowflake. Currently under review.
-
Access roles only consider grants to users. Access roles do not consider grants to other roles.
Snowflake Requirements
Snowflake Requirements
-
Access to Snowflake (see section below)
Snowflake Access
Create a Snowflake user with read access to following 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
-
account_usage.access_history (If you have Enterprise Edition)
Ability to run:
-
SHOW STREAMS IN ACCOUNT
-
SHOW PRIMARY KEYS IN ACCOUNT
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];
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;
-- account_usage.access_history
create view CATALOG_METADATA.ACCOUNT_USAGE.ACCESS_HISTORY
as select * from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;
-- 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];
Dynamic Tables
If you wish for K to catalog Dynamic Tables, individual monitor access at a database level is required.
GRANT MONITOR ON ALL DYNAMIC TABLES IN DATABASE {database_1} TO ROLE CATALOG_READ_ONLY;
GRANT MONITOR ON ALL DYNAMIC TABLES IN DATABASE {database_2} TO ROLE CATALOG_READ_ONLY;
Secure Shared Databases
Inbound Shares
For K to catalogue databases created from inbound shares:
-
Allow the role to see inbound shares:
GRANT IMPORT SHARE ON ACCOUNT TO ROLE CATALOG_READ_ONLY; -
Give the role read access to each database created from a share. Run this as the database owner or ACCOUNTADMIN:
GRANT IMPORTED PRIVILEGES ON DATABASE <shared_db> TO ROLE CATALOG_READ_ONLY;
If the provider shares through database roles, grant<shared_db>.<db_role>instead. Repeat this for every new shared database, because future grants don’t apply to them.
Only the objects the provider exposes in the share are catalogued.
GRANT IMPORT SHARE ON ACCOUNT TO ROLE CATALOG_READ_ONLY;
GRANT IMPORTED PRIVILEGES ON DATABASE <shared_db> TO ROLE CATALOG_READ_ONLY;
(This will not allow the role to CREATE any databases - as the role does not hold any create privileges).
Outbound Shares
K can show lineage from your tables to the outbound shares that expose them, and on to the consumer accounts. To do this it has to list outbound shares with SHOW SHARES. Snowflake only returns an outbound share to the role that owns it (or a role above that owner).
To turn on outbound share lineage, currently the only default grant available is to provide the share-owning role to the catalog role:
GRANT ROLE <share_owner_role> TO ROLE CATALOG_READ_ONLY;
This is not read-only. Through this grant, CATALOG_READ_ONLY inherits every privilege of <share_owner_role>. That includes changing or dropping shares, adding or removing objects and consumer accounts, and anything else the owner role can do in your account. K only runs SHOW SHARES and read queries, but the grant itself goes beyond that. If your shares are owned by ACCOUNTADMIN, this grant would make the catalog role an account administrator, which is not recommended.
Owner’s-rights stored procedure (recommended)
This method bypasses the above by creating a procedure owned by the share-owner role. It runs SHOW SHARES and returns only the outbound rows. They grant just USAGE on that procedure to the catalog role:
USE ROLE <share_owner_role>;
CREATE PROCEDURE <db>.<schema>.KADA_OUTBOUND_SHARES()
RETURNS TABLE (KIND STRING, OWNER_ACCOUNT STRING, SHARE_NAME STRING, DATABASE_NAME STRING, CONSUMERS STRING)
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
DECLARE res RESULTSET;
BEGIN
SHOW SHARES;
res := (SELECT "kind", "owner_account", "name", "database_name", "to"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE UPPER("kind") = UPPER('OUTBOUND'));
RETURN TABLE(res);
END;
$$;
GRANT USAGE ON DATABASE <db> TO ROLE CATALOG_READ_ONLY;
GRANT USAGE ON SCHEMA <db>.<schema> TO ROLE CATALOG_READ_ONLY;
GRANT USAGE ON PROCEDURE <db>.<schema>.KADA_OUTBOUND_SHARES() TO ROLE CATALOG_READ_ONLY;
If enabled, K can be configured to use the procedure instead.
Semantic Views
If you wish for K to catalog Semantic Views, the following additional grants are required.
GRANT SELECT ON ALL SEMANTIC VIEWS IN DATABASE <each_target_db> TO ROLE CATALOG_READ_ONLY;
GRANT SELECT ON FUTURE SEMANTIC VIEWS IN DATABASE <each_target_db> TO ROLE CATALOG_READ_ONLY;
Final Steps
From the above record down the following to be used for the setup
-
Username / Password
-
Role
-
Warehouse
-
(If creating a new database for metadata) Database name
-
Snowflake account (found in the URL of your Snowflake instance - between https:// and .snowflakecomputing.com/…)
Authentication Methods
Key-Pair Authentication
If you want to connect to Snowflake via Key Pair Authentication, follow these steps https://docs.snowflake.com/en/user-guide/key-pair-auth#step-1-generate-the-private-key and attach the key to the user you created.
Entra OAuth Connection
Follow the instructions here to setup your Snowflake environment for Entra OAuth Setting Up External OAuth Token Using Azure AD For OAuth Client
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
-
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
-
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 account 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 (if using key pair authentication)
-
-
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
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
-
Select Platform Settings in the side bar
-
In the pop-out side panel, under Platform Settings click on Sources
-
Next to the Source click on the menu icon and select Run
-
After the manual run is triggered, a pop up bar will appear containing a link that can take you to the Monitor tab where you can check the progress of the job.
Troubleshooting failed loads
-
If the job failed at the extraction step
-
Check the error. Contact KADA Support if required.
-
Rerun the source job
-
-
If the job failed at the load step, the landing folder failed directory will contain the file with issues.
-
Find the bad record and fix the file
-
Rerun the source job
-