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

3. Create or assign a Warehouse to the user

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

-- 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_password = '<ENTER a secure password>';
-- 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)
PASSWORD = $user_password
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

Please reach out to your friends at Woven to plan sharing. We need the following information:

1. Username (if you changed it from WOVEN_USER)

2. Role (if you changed if from WOVEN_ROLE)

3. Password

4. Snowflake Account Identifier (follow this guide)

We are working on a self-service experience.