Snowflake

Perform the following steps to configure Snowflake as a Destination using Snowflake Partner Connect:

Note: If you are new to Hevo, setting up a Destination using Snowflake Partner Connect is the recommended method. If you are an existing user, you should connect using Snowflake Credentials.

1. Log in to your Snowflake account.

   Note: You must have an ACCOUNTADMIN role to connect.

2. In the left navigation pane, click Data Products and then click Partner Connect.

   

3. In the search field, enter Hevo and then click the Hevo tile.

   

4. In the pop-up window that appears, do one of the following:

   • If your organization’s domain is registered with Hevo, but you are not an existing user:

     1. Click Launch.

        

     2. Specify your email address and click Continue.

        

     3. Specify the password associated with your registered email address and click LOG IN.

        

     4. Perform one of the following steps in Hevo:

        • To create a new workspace, click + CREATE WORKSPACE.

          Note: This is the suggested option for new trial users, as it allows you to start exploring the Hevo product immediately.

          

        • Alternatively, you can join an existing workspace by requesting access from the workspace owner.

          Note: You can configure a Snowflake Destination only after your access request is approved and you have been assigned the required role to create Pipelines and or Destinations.

     5. Skip to Step 5 to configure your Source.

   • If your organization’s domain is not registered with Hevo, and you are a new user:

     1. Click Connect.

        

     2. In the Your partner account… pop-up window, click Activate.

        

     3. On the Set up your Password page, specify a password to create a Hevo account and then click Continue.

        

5. On the Select Source Type page, select and configure your Source, and then click TEST & CONTINUE. For example, here, we have selected Amazon RDS MySQL.

   

6. On the Select Destination page, select Snowflake.

   

7. Specify the Destination Table Prefix.

   

8. Ensure that Auto Mapping is enabled if you want to automatically map Source Event Types to the Destination table.

9. Select the ingestion frequency at which Hevo must ingest data from the Source. You can select Custom and define the ingestion frequency by specifying an integer value in hours.

10. Click CONTINUE.

Refer to the steps in this section to create a Snowflake account, connect to a warehouse, and obtain the Snowflake credentials.

(Optional) Create 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 left navigation bar, click Projects, and then click Worksheets.

   

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

   

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

5. 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 use to connect your warehouse to Hevo.

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

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

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

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

If you are a user in a Snowflake account created after the BCR Bundle 2024_08, Snowflake recommends connecting to ETL applications, such as Hevo, through a service user. For this, run the following command:

ALTER USER <your_snowflake_user> SET TYPE = SERVICE;

Replace the placeholder value in the command above with your own. For example, <your_snowflake_user> with HEVOSERVICEUSER.

Note: New service users will not be able to connect to Hevo via password authentication; they must connect with a key pair. Read Obtain a Private and Public Key Pair for the steps.

Obtain a Private and Public Key Pair (Recommended Method)

-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFJDBWBg...
----END ENCRYPTED PRIVATE KEY-----

openssl rsa -in <private_key_file> -pubout -out <public_key_file>

-----BEGIN PUBLIC KEY-----
MIIBIjANBgk...
-----END PUBLIC KEY-----

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 in the 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.

Configure Snowflake as a Destination

Perform the following steps to configure Snowflake as a Destination in Hevo:

1. Click DESTINATIONS in the Navigation Bar.

2. Click + CREATE DESTINATION in the Destinations List View.

3. On the Add Destination page, select Snowflake as the Destination type.

4. On the Configure your Snowflake Destination page, specify the following:

   

   • Destination Name: A unique name for your Destination, not exceeding 255 characters.

   • Select one of the methods to connect to your Snowflake data warehouse:

     • Connect using Key Pair Authentication:

       • Snowflake Account URL: The account URL that you retrieved in Step 4.

       • Database User: A user with a non-administrative role created in the Snowflake. This user must be the one to whom the public key is assigned.

       • Private Key: A cryptographic password used along with a public key to generate digital signatures. Click the attach () icon to upload the private key file that you generated in Step 3.

       • Passphrase: The password given while generating the encrypted private key.

       • Warehouse: The Snowflake warehouse associated with your database, where the SQL queries and DML operations are performed. The warehouse can be the one that you created above or an existing one.

       • Database Name: The name of the Destination database where the data is to be loaded. This database can be the one that you created above or an existing one.

       • Database Schema: The name of the schema in the Destination database to which the specified database user has access. This schema can be the one that you created above or an existing one.

         Note: The schema name is case-sensitive.

     • Connect using Access Credentials:

       

       • Snowflake Account URL: The account URL that you retrieved in Step 4.

       • Database User: A user with a non-administrative role created in the Snowflake database. This user can be the one that you created above or an existing user.

       • Database Password: The password of the database user.

       • Warehouse: The Snowflake warehouse associated with your database, where the SQL queries and DML operations are performed. The warehouse can be the one that you created above or an existing one.

       • Database Name: The name of the Destination database where the data is to be loaded. This database can be the one that you created above or an existing one.

       • Database Schema: The name of the schema in the Destination database to which the specified database user has access. This schema can be the one that you created above or an existing one.

         Note: The schema name is case-sensitive.

   • Advanced Settings:

     • Populate Loaded Timestamp: Enable this option to append the __hevo_loaded_at column to the Destination table to indicate the time when the Event was loaded to the Destination. Read Loading Data to a Data Warehouse.

     • Create Transient Tables: Enable this option to create transient tables. Transient tables have the same features as permanent tables minus the Fail-safe period. The fail-safe feature allows Snowflake to recover the table if you were to lose it, for up to seven days. Transient tables allow you to avoid the additional storage costs for the backup, and are suitable if your data does not need the same level of data protection and recovery provided by permanent tables, or if it can be reconstructed outside of Snowflake. Read Transient Tables.

5. Click TEST CONNECTION. This button is enabled once all the mandatory fields are specified.

6. Click SAVE & CONTINUE. This button is enabled once all the mandatory fields are specified.