Warehouse Guide: Snowflake

Provision a Snowflake account with the warehouses, database, role and users Kleene needs, then connect it as your data warehouse using key pair authentication — the method Kleene recommends over a username and password.

• Connector type Warehouse
• Auth method Key pair (RSA)

Before you start

• A filestore to pair it with — Kleene supports Snowflake with Azure, Google Cloud Storage or AWS S3
• Billing details for the Snowflake account — the free trial lasts 30 days, after which the account locks

Set up Snowflake as your warehouse

1. Provision your Snowflake warehouse

   1. In Snowflake, go to the Accounts section under Admin and click + Account.
   2. Select the cloud of the filestore service you plan to use (Microsoft Azure, AWS S3 or GCS), along with the region your filestore is hosted in.
   3. Enter an appropriate account name and user name — e.g. <COMPANY_NAME>_KLEENE and ACCOUNTADMIN_<COMPANY_NAME>.
   4. Generate a fresh password for signing in to Snowflake yourself, and choose an email such as WAREHOUSE@<COMPANY_NAME>.com.

   Make sure the region of the Snowflake account matches the region of your filestore.

2. Configure access for Kleene

   1. Access the Snowflake environment using the Account Admin credentials.
   2. Paste the prep scripts below into a worksheet. Do not run them yet.
   3. Ensure the ACCOUNTADMIN role is active and no specific warehouse is selected — the scripts create the ones you need — then run them.
   4. In Kleene, go to App settings → Account, click Edit on the warehouse and choose Key Pair as the authentication method.
   5. Click Generate key pair, add a passphrase if you want one, then Generate followed by Fill the form with keys.
   6. Copy the public key Kleene shows you and attach it to eltuser with the command below.
   Snowflake prep scripts 37 lines

   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 DEFAULT_WAREHOUSE = 'ELT' DEFAULT_ROLE = ELT;
   GRANT ROLE ELT TO user eltuser;
   
   CREATE OR REPLACE USER reporting DEFAULT_WAREHOUSE = 'reporting' DEFAULT_ROLE = ELT;
   GRANT ROLE ELT TO user reporting;
   
   grant imported privileges on database snowflake to 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';

   Attach the public key

   ALTER USER eltuser SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

   Rather generate the keys yourself? Kleene's warehouse setup guide gives the OpenSSL commands, then you paste the private key file into the Private key field instead of using the generator.

3. Add the warehouse in Kleene

   • Host nameYour Snowflake Account URL
   • Database namePROD — created by the prep scripts
   • Warehouse nameELT — created by the prep scripts
   • RoleELT — created by the prep scripts
   • Database usereltuser
   • Authentication methodKey Pair
   • Private keyFilled in by the generator, or pasted from your key file
   • PassphraseOnly if the private key is encrypted

   Kleene can now load data into your warehouse.

Good to know