Skip to main content
All CollectionsOnboarding and Configuration Guides
Onboarding Guide - Snowflake
Onboarding Guide - Snowflake
Reco Product Management avatar
Written by Reco Product Management
Updated over 3 weeks ago

Prerequisites

  1. The Snowflake user performing the integration must be an account admin

  2. A Reco user with Admin Role

Choose Your Authentication Method

You may choose how to integrate Reco with Snowflake:

  1. 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.

  2. 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)".

Note
This integration operates on your Snowflake instance and is billed accordingly. Since Reco does not perform resource-intensive queries, an X-Small warehouse is sufficient for optimal performance. To minimize costs, you can allocate any available warehouse, but ensure that you set the default warehouse for the user (see point 4 under "Generate Snowflake User").

Generate Snowflake User

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

  1. Login to Snowflake

  2. Open a new SQL Worksheet
    ​

  3. 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 IMPORT SHARE ON ACCOUNT TO 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").

  4. 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).

  5. Save the username and the password you chose

  6. 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

Create Security Integration (Snowflake OAuth App)

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

  1. Login to Snowflake

  2. Open a new SQL Worksheet

  3. 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;

  4. 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 ''.

    ​

  5. 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.

  6. Check the BLOCKED_ROLES_LIST property_value , if the role SECURITYADMIN is in the list you have 3 options:

    1. Remove this role from the list, most of the times it will be defined in the account level.

      ALTER ACCOUNT SET OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = FALSE;
      ​

    2. Revoke the SECURITYADMIN role from the RECO_API user you have created - as a result you will be missing features.
      For more information about those missing features please see the appendix in the end of the guide.

      revoke role SECURITYADMIN from role RECO_ROLE;
      ​

    3. Use the Basic Auth which is less secure as mentioned above.
      ​

Integrate Snowflake with Reco (OAuth)

  1. Login to the Reco Platform.

  2. Click on "Configurations" then click on "Integrations".

  3. Click "Add integrations" and locate the "Snowflake" object.

  4. Pick the OAuth Auth in the Pick Auth Method dropdown.

  5. Fill the form with all the saved information and press Connect:

  6. Continue with the OAuth flow (fill in your username + password -> admin role needed, or using the dedicated user we created):

  7. press Allow to grant the app permissions:

Integrate Snowflake with Reco (Basic)

  1. Login to the Reco Platform.

  2. Click on "Configurations" then click on "Integrations".

  3. Click "Add integrations" and locate the "Snowflake" object.

  4. Pick the Basic Auth in the Pick Auth Method dropdown.

  5. Fill the form with all the saved user, password & account and press Connect:


​Optional - Add Network Policy To Dedicated User

  1. 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');

  2. 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

APPENDIX - Impact of Missing Admin Roles on Snowflake Integration and Security Policies

  1. Security Admin - required for the queries:

    SHOW SECURITY INTEGRATIONS
    SELECT SYSTEM$SHOW_APPLICATION_INSTANCES()
    SHOW PRIVILEGES IN APPLICATION "%s"
    SHOW REFERENCES IN APPLICATION "%s"

    1. The first one is relevant for the policies

      1. Snowflake - SCIM integration is configured to automatically provision and deprovision users and groups

      2. Snowflake - SSO is configured for your account / organization

      3. Snowflake - SSO is configured for your account / organization

      4. Snowflake - SCIM integration is configured to automatically provision and deprovision users and groups

    2. The others are required for App2App features.

  2. AccountAdmin - required for the query

    SHOW PARAMETERS LIKE 'NETWORK_POLICY' for user "%s

    and the relevant policies are:
    ​

    1. Snowflake - Account-level network policy has been configured to only allow access from trusted IP addresses

    2. Snowflake - User-level network policies have been configured for service accounts

    3. Snowflake - User-level network policies have been configured for service accounts

    4. Snowflake - Account-level network policy has been configured to only allow access from trusted IP addresses

Did this answer your question?