Onboarding Guide - Snowflake | Reco Help Center

Prerequisites

The Snowflake user performing the integration must be an account admin
A Reco user with Admin Role

Choose Your Authentication Method

You may choose how to integrate Reco with Snowflake:

OAuth - More secure but you will need to re-integrate every 90 days.
1. When choosing OAuth, you are not required to create a new dedicate user for Reco with a dedicated role, but it is highly recommended also for governance and also to avoid missing permissions in the connected user. If you integrate via OAuth and want the dedicated user & role, go through the "Generate Snowflake User" part, then the "Create Security Integration (Snowflake OAuth App)".
2. If you choose to use your own user, only the "Create Security Integration (Snowflake OAuth App)" part is required.
Basic Auth (User/Password) - No need to re-integrate.
1. When choosing Basic Auth, you must go through all of the steps, besides "Create Security Integration (Snowflake OAuth App)".

Generate Snowflake User

*Note: Required for Basic Auth & OAuth with dedicated user & role

Login to Snowflake
Open a new SQL Worksheet

Create a role with access to SNOWFLAKE database by running the commands

CREATE ROLE RECO_ROLE; 

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE RECO_ROLE;

GRANT ROLE SECURITYADMIN TO ROLE RECO_ROLE;

GRANT MONITOR USAGE ON ACCOUNT TO ROLE RECO_ROLE;

GRANT USAGE ON WAREHOUSE DATA_API_WH TO ROLE RECO_ROLE;

*Note: The default warehouse name may differ across Snowflake versions (commonly also "COMPUTE_WH").

Create a dedicated user and assign the role you created to the user by running the commands:
```
CREATE USER RECO_API
PASSWORD='your_password' 
DEFAULT_ROLE=RECO_ROLE 
MUST_CHANGE_PASSWORD=FALSE 
DEFAULT_WAREHOUSE=DATA_API_WH; 

GRANT ROLE RECO_ROLE TO USER RECO_API;
```
*See note regarding default warehouse.
**Snowflake is sensitive in regards to usernames. If username was changed to lowercase, "" are required around the username when altering and granting (last command in this block, and if adding a security policy for the user).
Save the username and the password you chose
Get your account name by running the following query and save the results
```
SELECT LOWER(CONCAT(CURRENT_ORGANIZATION_NAME(), '-', CURRENT_ACCOUNT_NAME()));
```
Validate The New User - Not required for integration success
Log into snowflake with the new created user and open new worksheet, and run the following query to validate the new account permissions:
```
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.USERS ORDER BY USER_ID LIMIT 100 OFFSET 0
```
Optional - Add Network Policy To Dedicated User

To add a network policy that limits the user to run query only from Reco IPs create a network policy by running

CREATE NETWORK POLICY reco_ips ALLOWED_IP_LIST=('34.238.227.56', '54.165.205.157', '3.221.91.232', '63.33.29.180', '18.203.170.26', '54.73.236.84');

Assign the network policy to the user you created
```
ALTER USER RECO_API SET NETWORK_POLICY = reco_ips
```
*Note: Users might take UP TO 3 HOURS TO BE UPDATED IN SNOWFLAKE - thus this query might fail in the beginning
**See note regarding quotation marks in the user creation section

Create Security Integration (Snowflake OAuth App)

*Note: Required OAuth only (dedicated or non-dedicated user)

Login to Snowflake
Open a new SQL Worksheet

Create reco_security_integration (Snowflake OAuth App). Make sure to change the redirect uri to your Reco env (see example):

create security integration reco_security_integration
type = oauth
oauth_client = custom
oauth_client_type = 'confidential'
oauth_redirect_uri = "https://your_reco_domain/api/v1/auth-callback/oauth"
enabled = true
oauth_issue_refresh_tokens = true
oauth_refresh_token_validity = 7776000;

Retrieve & save the app client_secret and client_id (save the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET, ignore OAUTH_CLIENT_SECRET_2):
```
select system$show_oauth_client_secrets('RECO_SECURITY_INTEGRATION')
```
*Note: The name of the integration was created in lowercase but we need to reference it as uppercase and surround with ''.
Retrieve & save the app auth_url and token_url (save the values of OAUTH_AUTHORIZATION_ENDPOINT and OAUTH_TOKEN_ENDPOINT):
```
DESC SECURITY INTEGRATION RECO_SECURITY_INTEGRATION;
```
*Note: The name of the integration was created in lowercase but we need to reference it as uppercase.

Integrate Snowflake with Reco (OAuth)

Login to the Reco Platform.
Click on "Configurations" then click on "Integrations".
Click "Add integrations" and locate the "Snowflake" object.
Pick the OAuth Auth in the Pick Auth Method dropdown.
Fill the form with all the saved information and press Connect:
Continue with the OAuth flow (fill in your username + password -> admin role needed, or using the dedicated user we created):
press Allow to grant the app permissions:

Integrate Snowflake with Reco (Basic)

Login to the Reco Platform.
Click on "Configurations" then click on "Integrations".
Click "Add integrations" and locate the "Snowflake" object.
Pick the Basic Auth in the Pick Auth Method dropdown.
Fill the form with all the saved user, password & account and press Connect: