Setting up your Snowflake Data Warehouse

To create an Account in Snowflake, navigate to the Accounts section under Admin and click on
‘+ Account’:

Select the Cloud of the Filestore service you plan on using (Microsoft Azure, AWS s3 or GCS) along with the region your filestore is hosted in.

❗️

Please ensure the region of the Snowflake account is aligned with the Filestore.

In the next screen, please enter appropriate Account Name and User Name e.g. <COMPANY_NAME>_KLEENE and ACCOUNTADMIN_<COMPANY_NAME>

Generate fresh passwords (we recommend the password generator from 1Password for safe secure credentials).

Choose an email e.g. WAREHOUSE@<COMPANY_NAME>.com

📘

💳 NOTE:

You automatically have a 30 day free trial - please add billing details so that your account doesn’t get locked after this period.

Below are the Snowflake Prep Scripts which set up Warehouses, Databases and Users for the ELT process -

Snowflake Prep Scripts:

CREATE OR REPLACE WAREHOUSE elt WITH WAREHOUSE_SIZE = 'XSMALL' 
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 
AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1;

CREATE OR REPLACE WAREHOUSE analysis WITH WAREHOUSE_SIZE = 'XSMALL' 
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 
AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1;

CREATE OR REPLACE WAREHOUSE reporting WITH WAREHOUSE_SIZE = 'XSMALL' 
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60
AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1;

CREATE OR REPLACE DATABASE PROD;
CREATE OR REPLACE ROLE ELT;
GRANT ROLE ELT TO ROLE SYSADMIN;

grant usage on warehouse analysis to role ELT;
grant usage on warehouse reporting to role ELT;
grant usage on warehouse ELT to role ELT;

CREATE OR REPLACE USER eltuser PASSWORD = 'GENERATE AND INSERT A PASSWORD HERE ' DEFAULT_WAREHOUSE = 'ELT' MUST_CHANGE_PASSWORD = FALSE DEFAULT_ROLE = ELT;
GRANT ROLE ELT TO user eltuser;

CREATE OR REPLACE USER reporting PASSWORD = 'GENERATE AND INSERT A PASSWORD HERE ' DEFAULT_WAREHOUSE = 'reporting' MUST_CHANGE_PASSWORD = FALSE DEFAULT_ROLE = ELT;
GRANT ROLE ELT TO user reporting;

grant imported privileges on database snowflake to role ELT; -- allows us to view credit usage on role ELT

grant ownership on database PROD to role ELT revoke current grants;
GRANT USAGE ON DATABASE PROD TO ROLE ELT;
GRANT ALL ON ALL SCHEMAS IN DATABASE PROD TO ROLE ELT;
GRANT ownership ON ALL SCHEMAS IN DATABASE PROD TO ROLE ELT revoke current grants;

ALTER ACCOUNT SET TIMEZONE = 'UTC'; -- stops the default being ‘America/Los_Angeles’

📘

🔏 NOTE:

The password set in the scripts for eltuser and reporting user need to be updated with brand new generated passwords. It is recommended to use 1Password’s Password Generator (or something similar) which automatically creates high level secure passwords.

Using the scripts above, please follow these steps when running them in your Snowflake instance:

1. Access the Snowflake environment using the Account Admin credentials.

2. Paste the prep scripts into the worksheet. Do not run them yet.

3. Update passwords - examples of their suggested complexity shown below (make a note of these to add into 1Password at the end!)

4. Ensure ACCOUNTADMIN role is active, no specific warehouse is selected (as we’ll be making the necessary ones) and run the scripts.

Credentials needed in Vault:

Now we need specific information to be shared in our secure 1Password vault so that all parties are clear on what the Kleene app will be pointing to:

Store these credentials (including passwords from earlier) in your customer 1Password Vault. A Snowflake template should be provided so that you may simply enter the information into the correct fields:

• Hostname (Account URL)
• Database name
• Warehouse name
• Eltuser username and password
• Reporting username and password

Template 1Password item to update with credentials

Setup Snowflake in Kleene App

Follow the setup panel and enter the information using the 1Password item created just before this.

Host name = [Account URL]

Database name = ‘PROD’ (written in prep scripts)

Warehouse name = ‘ELT’ (written in prep scripts)

Role = ‘ELT’ (written in prep scripts)

Database user = ‘eltuser’ (written in prep scripts)

Database password = [eltuser password]

📘

⚙️ NOTE:

The reporting user is not used in the initial data warehouse setup but for reporting purposes later on down the line e.g. for dashboards

You can see an example of what information goes where below: