# 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

{% hint style="info" %}
You must be an ACCOUNTADMIN on Snowflake to perform this step
{% endhint %}

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](https://docs.snowflake.com/en/user-guide/key-pair-auth#generate-the-private-keys)) 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](https://docs.getdbt.com/docs/cloud/about-cloud/access-regions-ip-addresses#accessing-your-account))
2. Username (if you changed it from WOVEN\_USER)
3. Private Key
4. dbt schemas (what db.schema are your dbt models under)