Snowflake

Prerequisites

• An active Snowflake account.

• The user has either ACCOUNTADMIN or SECURITYADMIN role in Snowflake to create a new role for Hevo.

• The user must have ACCOUNTADMIN or SYSADMIN role in Snowflake, if a warehouse is to be created.

• Hevo is assigned USAGE permissions on data warehouses.

• Hevo is assigned USAGE and CREATE SCHEMA permissions on databases.

• Hevo is assigned USAGE, MONITOR, CREATE TABLE, CREATE EXTERNAL TABLE, and MODIFY permissions on the current and future schemas.

• You are assigned the Team Collaborator, or any administrator role except the Billing Administrator role in Hevo to create the Destination.

Refer to section, Create and Configure your Snowflake Warehouse to create a Snowflake warehouse with adequate permissions for Hevo to access your data.

---

Perform the following steps to configure your Snowflake Destination:

(Optional) Creating a Snowflake Account

---

Create and Configure your Snowflake Warehouse

Hevo provides you with a ready-to-use script to configure the Snowflake warehouse you intend to use as the Destination.

Follow these steps to run the script:

1. Log in to your Snowflake account.

2. In the top right corner of the Worksheets tab, click the + icon to create a new worksheet.

   

3. Paste the following script in the worksheet. The script creates a new role for Hevo in your Snowflake Destination. Keeping your privacy in mind, the script grants only the bare minimum permissions required by Hevo to load the data in your Destination.

   -- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
   set role_name = 'HEVO'; -- Replace "HEVO" with your role name
   set user_name = 'HARRY_POTTER'; -- Replace "HARRY_POTTER" with your username
   set user_password = 'Gryffindor'; -- Replace "Gryffindor" with the user password
   set warehouse_name = 'HOGWARTS'; -- Replace "HOGWARTS" with the name of your warehouse
   set database_name = 'RON'; -- Replace "RON" with the name of your database
   set schemaName = 'DARK_ARTS'; -- Replace "DARK_ARTS" with the database schema name
   
   set db_schema = concat($database_name, '.', $schemaName);
   
   begin;
   
   -- change role to securityadmin for user / role steps
   use role securityadmin;
   
   -- create role for HEVO
   create
   role if not exists identifier($role_name);
   grant role identifier($role_name) to role SYSADMIN;
   
   -- create a user for HEVO
   create user if not exists identifier($user_name)
   password = $user_password
   default_role = $role_name
   default_warehouse = $warehouse_name;
   
   -- Grant access to the user
   grant role identifier($role_name) to user identifier($user_name);
   
   -- change role to sysadmin for warehouse / database steps
   use role sysadmin;
   
   -- create a warehouse for HEVO, if it does not exist
   create
   warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;
   
   -- create database for HEVO
   create database if not exists identifier($database_name);
   
   -- grant HEVO role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);
   
   -- grant HEVO access to current schemas
   use role accountadmin;
   grant CREATE SCHEMA, MONITOR, USAGE, MODIFY
   on database identifier($database_name)
   to role identifier($role_name);
   
   -- grant Hevo access to future schemas
   use role accountadmin;
   grant SELECT on future tables in database identifier($database_name) to role identifier($role_name);
   grant MONITOR, USAGE, MODIFY on future schemas in database identifier($database_name) to role identifier($role_name);
   
   use role accountadmin;
   CREATE SCHEMA IF not exists identifier($db_schema);
   GRANT USAGE, MONITOR, CREATE TABLE, CREATE EXTERNAL TABLE, MODIFY ON SCHEMA identifier($db_schema) TO ROLE identifier($role_name);
   
   commit;
   

4. Replace the sample values provided in lines 2-7 of the script with your own to create your warehouse. These are the credentials that you will be using to connect your warehouse to Hevo. You can specify a new warehouse, role, and or database name to create these now, or use pre-existing ones to load data into.

   Note: The values for role_name, user_name, warehouse_name, database_name and schemaName must be in upper case.

5. Press CMD + A (Mac) or CTRL + A (Windows) inside the worksheet area to select the script.

6. Press CMD+return (Mac) or CTRL + Enter (Windows) to run the script.

7. Once the script runs successfully, you can use the credentials from lines 2-7 of the script to connect your Snowflake warehouse to Hevo.

---

Obtain your Snowflake Account URL

The Snowflake data warehouse may be hosted on any of the following Cloud providers:

• Amazon Web Services (AWS)

• Google Cloud Platform (GCP)

• Microsoft Azure (Azure)

The account name, region, and cloud service provider are visible in your Snowflake web interface URL.

For most accounts, the URL looks like https://account_name.region.snowflakecomputing.com.

For example, https://westeros.us-east-2.aws.snowflakecomputing.com. Here, westeros is your account name, us-east-2 is the region, and aws is the service provider.

Similarly, if your Snowflake instance is hosted on AWS, US West region, the URL looks like https://account_name.snowflakecomputing.com.

Perform the following steps to obtain your Snowflake Account URL:

1. Log in to your Snowflake instance. Click Admin in the left panel.

   

2. Under the Admin tab, click Accounts.

   

3. Hover the mouse on the LOCATOR field corresponding to the account for which you want to obtain the URL.

   

4. Click on the link icon to copy your account URL.

---