Snowflake

Woven connects with your Snowflake to pull metadata about your tables, such as information schema and query history. This guide shows you how to provision a system user in your Snowflake warehouse for Woven with the right permissions, and how to share the credentials with Woven.

1. Provision a system user

You must be an ACCOUNTADMIN on Snowflake to perform this step

The following SQL snippet does the following:

1. Create a Role called WOVEN_ROLE

2. Create an User called WOVEN_USER and set the above role as the default role

3. Assign a default Warehouse to the user

4. Grant access to the database Snowflake which contains query history

5. Create a RSA key pair (Snowflake guide) and use the public key below

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
SET role_name = 'WOVEN_ROLE';
SET user_name = 'WOVEN_USER';
SET user_public_key = '<ENTER the public key>';
-- it is okay to choose an existing warehouse
SET warehouse_name = '<ENTER a warehouse name to use>';

-- SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
SET database_name = 'SNOWFLAKE';

-- change role to securityadmin for user / role steps
USE ROLE SECURITYADMIN;

-- create role for woven and assign to sysadmin
CREATE ROLE IF NOT EXISTS identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

-- create a user for woven
CREATE USER IF NOT EXISTS identifier($user_name)
RSA_PUBLIC_KEY = $user_public_key
DEFAULT_ROLE = $role_name
DEFAULT_WAREHOUSE = $warehouse_name;

GRANT ROLE identifier($role_name) TO USER identifier($user_name);

-- change role to sysadmin for warehouse / database steps
USE ROLE SYSADMIN;

-- create a warehouse for woven if one doesn't exist
CREATE WAREHOUSE IF NOT EXISTS identifier($warehouse_name)
WAREHOUSE_SIZE = xsmall
WAREHOUSE_TYPE = standard
AUTO_SUSPEND = 60
AUTO_RESUME = true
INITIALLY_SUSPENDED = true;

-- use account admin since sysadmin does not have the priv to grant usage
USE ROLE ACCOUNTADMIN;

-- set binary_input_format to BASE64
ALTER USER identifier($user_name) SET BINARY_INPUT_FORMAT = 'BASE64';

-- grant woven role access to warehouse
GRANT USAGE
ON warehouse identifier($warehouse_name)
TO ROLE identifier($role_name);

-- grant access to history
USE ROLE ACCOUNTADMIN;

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE  identifier($role_name);

-- done. now test

USE ROLE identifier($role_name);

SELECT QUERY_TEXT, QUERY_TYPE, QUERY_ID, USER_NAME
        FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE QUERY_TYPE IN ('CREATE', 'SELECT')
        AND WAREHOUSE_SIZE IS NOT null
        AND USER_NAME NOT IN ('SYSTEM')
        LIMIT 10;
-- test

2. Share the credentials with Woven

Go to https://app.woven.dev/query-insights on the woven portal and enter the following:

1. Snowflake Account Identifier (follow this guide)

2. Username (if you changed it from WOVEN_USER)

3. Private Key

4. dbt schemas (what db.schema are your dbt models under)