Snowflake Setup Guide

Snowflake Direct Connect

Note:

Network policies differ between customers and how their architecture is set up. You may need to apply a network policy allowing DataBee to talk to your Snowflake instance. For a list of IP addresses that need to be whitelisted, contact DataBee Support or reach out to your Technical Account Manager.

To learn more about configuring the Snowflake Network Policy, see Create Network Policy.

Key Pair Setup

DataBee connects to Snowflake using a secure key-pair authentication mechanism. Before configuring your Snowflake environment, you will need to generate a key pair for the connection. This 2-step process generates a private encrypted key and a public key. To generate the private key you can run the following openssl command:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

Private Key Password

This command will ask you to setup a password to protect the private key. DO NOT LOSE this password or you will be unable to complete the Snowflake connection.

To generate the public key that matches the private key created above you can use this openssl command:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Password Input

This command requests the same password you used to create the private key.

You will use this public key when setting up the user in Snowflake and the private key and password when connecting in the DataBee UI. Click on Key-pair authentication with Snowflake for more details.

Snowflake Setup

Prior to configuring your Snowflake connection in DataBee, your Snowflake administrator must perform the steps in the script below:

Step 1: Creating Role

-- Creating Role
USE ROLE ACCOUNTADMIN;
create ROLE CTSCYBER_RL comment = 'Role created for Comcast to manage the connected app product' ;
USE ROLE ACCOUNTADMIN;
grant OWNERSHIP on ROLE CTSCYBER_RL to ROLE SYSADMIN;
USE ROLE SYSADMIN;
GRANT ROLE CTSCYBER_RL to ROLE SYSADMIN;

Step 2: Creating Warehouse

-- Creating Warehouse
USE ROLE SYSADMIN;
CREATE OR REPLACE WAREHOUSE CTSCYBER_WH
WITH WAREHOUSE_SIZE = SMALL -- default as XSMALL [| SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE]
MAX_CLUSTER_COUNT = 2 -- default to 2
MIN_CLUSTER_COUNT = 1 -- default to 1
SCALING_POLICY = STANDARD -- always default to STANDARD [| ECONOMY]
AUTO_SUSPEND = 60 -- warehouses automatically bill for the first minute, so we default to 60 second suspension
AUTO_RESUME = TRUE -- always default to TRUE [| FALSE]
INITIALLY_SUSPENDED = TRUE -- always default to TRUE [| FALSE]
COMMENT = 'This warehouse is utilized by the Comcast DataBee team to load and monitor data in your SF account'
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 1800 -- default to 30 min
STATEMENT_TIMEOUT_IN_SECONDS = 3600 -- default to 60 min
;
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE CTSCYBER_WH TO ROLE CTSCYBER_RL;

Step 3: Creating Database

-- Creating Database
USE ROLE SYSADMIN;
CREATE DATABASE CTSCYBER_DB
COMMENT = 'Database used for/by Comcast DataBee';
USE ROLE SYSADMIN;
GRANT OWNERSHIP ON DATABASE CTSCYBER_DB TO CTSCYBER_RL COPY CURRENT GRANTS;
GRANT USAGE on database CTSCYBER_DB TO CTSCYBER_RL;

Step 4: Add Users

-- Add Users
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE USER SVC_CTSCYBER
LOGIN_NAME = 'SVC_CTSCYBER'
RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...' -- replace this with the public key you created for this user
DISPLAY_NAME = 'SVC USER CTSCYBER'
FIRST_NAME = ''
LAST_NAME = ''
DEFAULT_WAREHOUSE = 'CTSCYBER_WH' -- default warehouse
DEFAULT_NAMESPACE = 'CTSCYBER_DB' --default database
DEFAULT_ROLE = 'CTSCYBER_RL' --default role
MUST_CHANGE_PASSWORD=FALSE;

Step 5: Grant default role

-- Grant default role
USE ROLE ACCOUNTADMIN;
GRANT ROLE CTSCYBER_RL TO USER SVC_CTSCYBER;
ALTER USER SVC_CTSCYBER SET DEFAULT_ROLE = CTSCYBER_RL;

Step 6: Grant task permissions

-- Grant task permissions
USE ROLE ACCOUNTADMIN; 
GRANT EXECUTE TASK, EXECUTE MANAGED TASK ON ACCOUNT TO ROLE CTSCYBER_RL;

After running the script, click the Grant Role button located at the bottom-right corner of the page.