Snowflake (Edge)

Prerequisites

• An active Snowflake account is available.

• You have either the ACCOUNTADMIN or SYSADMIN role in Snowflake to create a warehouse.

• A dedicated database and schema, both named HEVO, are available in your Snowflake account. Activate records the progress of each sync there.

• You have either the ACCOUNTADMIN or SECURITYADMIN role in Snowflake to create a new role for Hevo.

• Hevo is assigned the USAGE, OPERATE, and MONITOR permissions on the warehouse that runs your dataset queries.

• Hevo is assigned the USAGE permission on each database and schema that your datasets read from.

• Hevo is assigned the USAGE permission on the HEVO database, and the USAGE and CREATE TABLE permissions on the HEVO.HEVO schema.

• Hevo is assigned the SELECT permission on the current and future tables, views, and dynamic tables that your datasets read from, and the USAGE permission on the current and future functions those queries call.

Perform the following steps to configure your Snowflake Source in Activate:

(Optional) Create a Snowflake Warehouse

Create the Bookkeeping Database

Activate records the progress of each sync in a dedicated database in your Snowflake account, named HEVO, and a schema inside it, also named HEVO. This database helps Activate identify and send only the rows that changed since the last successful run and stores records that failed to sync. Your own data is never stored here. Read bookkeeping for more information.

Activate does not create the database and the schema for you. If either is missing, the connection test fails and reports that they must be created first.

Perform the following steps to create the bookkeeping database and schema:

1. Follow steps 1-3 from the Create a Snowflake Warehouse section to open a SQL worksheet.

2. Copy the following script into the worksheet:

   CREATE DATABASE IF NOT EXISTS HEVO;
   CREATE SCHEMA IF NOT EXISTS HEVO.HEVO;
   

3. Click the Run options drop-down, and then click Run all to run every command in the script.

   

Note: Do not place your own data in the HEVO database. Activate reserves it for bookkeeping and manages the tables inside it, so anything you store there can be changed.

Create a Snowflake User and Grant Permissions

Hevo does not need a user with the ACCOUNTADMIN role to connect to your Snowflake warehouse. You can create a non-administrative user and assign a custom role to it. Hevo provides you with a ready-to-use script that creates the role and the user. The script grants only the permissions that Activate requires, which are read access to the data your datasets return and write access to the HEVO bookkeeping schema.

The following table lists the permissions that the role for Hevo requires:

The script follows Snowflake’s recommended practice of placing the new role inside the existing role hierarchy, so that the SYSADMIN role inherits anything granted to it.

Perform the following steps to run the script:

1. Follow steps 1-3 from the Create a Snowflake Warehouse section to open a SQL worksheet.

2. In the role selector at the top right of the worksheet, ensure that the ACCOUNTADMIN or SECURITYADMIN role is selected. If it is not, click the drop-down and select that role.

   

3. Copy the following script and paste it into the worksheet. Replace the sample values for <database_name>, <schema_name>, and <password> with your own. If you are using an existing warehouse, also replace HEVO_ACTIVATE_WH with its name.

   Note: The script creates the role and the user, and grants them access to the warehouse you created in the Create a Snowflake Warehouse section. It grants read access to one of your data schemas and write access to the HEVO bookkeeping schema.

   -- Create a role for the Hevo user
   CREATE ROLE HEVO_ACTIVATE_ROLE;
   
   -- Ensure the SYSADMIN role inherits any permissions granted to the Hevo role.
   -- This does not grant SYSADMIN permissions to the Hevo role.
   GRANT ROLE HEVO_ACTIVATE_ROLE TO ROLE SYSADMIN;
   
   -- Let the Hevo role use the warehouse you created for Activate
   GRANT USAGE ON WAREHOUSE HEVO_ACTIVATE_WH TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT OPERATE ON WAREHOUSE HEVO_ACTIVATE_WH TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT MONITOR ON WAREHOUSE HEVO_ACTIVATE_WH TO ROLE HEVO_ACTIVATE_ROLE;
   
   -- Create the Hevo user
   CREATE USER HEVO_ACTIVATE WITH DEFAULT_ROLE = HEVO_ACTIVATE_ROLE DEFAULT_WAREHOUSE = HEVO_ACTIVATE_WH PASSWORD = '<password>';
   GRANT ROLE HEVO_ACTIVATE_ROLE TO USER HEVO_ACTIVATE;
   
   -- Let the Hevo user read the data you want to activate
   GRANT USAGE ON DATABASE "<database_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT USAGE ON SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT SELECT ON ALL TABLES IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT SELECT ON FUTURE TABLES IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT SELECT ON ALL VIEWS IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "<database_name>"."<schema_name>" TO ROLE HEVO_ACTIVATE_ROLE;
   
   -- Let the Hevo user record the progress of each sync in the bookkeeping schema
   GRANT USAGE ON DATABASE HEVO TO ROLE HEVO_ACTIVATE_ROLE;
   GRANT USAGE, CREATE TABLE ON SCHEMA HEVO.HEVO TO ROLE HEVO_ACTIVATE_ROLE;
   

   Note:

   • Do not set a default namespace on the Hevo user. Doing so changes the tables that are visible to Activate and can hide objects your datasets need.

   • If your datasets read from more than one schema, run the USAGE and SELECT grants again for each additional schema. The FUTURE grants cover objects created later in a schema you have already granted, but they do not cover a schema you have not granted at all.

4. Click the Run options drop-down, and then click Run all to run every command in the script.

   

5. Once the script runs successfully, you can specify the user name and password while configuring your Snowflake Source using Access Credentials.

   Note: Hevo recommends connecting to the Snowflake warehouse using key pair authentication. Read Obtain a Private and Public Key Pair for the steps.

Obtain a Private and Public Key Pair (Recommended Method)

You can authenticate Hevo’s connection to your Snowflake warehouse using a public-private key pair. For this, you need to:

1. Generate a private key.

2. Generate a public key for your private key.

3. Assign the public key to your Snowflake user.

1. Generate a private key

You can connect to Hevo using an encrypted or unencrypted private key.

Note: Hevo supports only private keys encrypted using the Public-Key Cryptography Standards (PKCS) #8-based triple DES algorithm.

Open a terminal window, and on the command line, do one of the following:

• To generate an unencrypted private key, run the command:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out <unencrypted_key_name> -nocrypt
  

• To generate an encrypted private key, run the command:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out <encrypted_key_name>
  

  You will be prompted to set an encryption password. This is the passphrase that you need to provide while connecting to your Snowflake Source using key pair authentication.

Note: Replace the placeholder values in the commands above with your own. For example, <encrypted_key_name> with encrypted_rsa_key.p8.

The private key is generated in the PEM format.

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

Open the private key file and remove the extra blank space or empty line at the bottom of the file. Save the private key file in a secure location and provide it while connecting to your Snowflake Source using key pair authentication.

2. Generate a public key

To use a key pair for authentication, you must generate a public key for the private key created in the Generate a private key section. For this:

Open a terminal window, and on the command line, run the following command:

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

Note:

• Replace the placeholder values in the command above with your own. For example, <private_key_file> with encrypted_rsa_key.p8.

• If you are generating a public key for an encrypted private key, you will need to provide the encryption password used to create the private key.

The public key is generated in the PEM format.

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

Save the public key file in a secure location. You must associate this public key with the Snowflake user that you created for Hevo.

3. Assign the public key to a Snowflake user

To authenticate Hevo’s connection to your Snowflake warehouse using a key pair, you must associate the public key generated in the Generate a public key section with the user that you created in the Create a Snowflake User and Grant Permissions section. To do this:

1. Follow steps 1-3 from the Create a Snowflake Warehouse section to open a SQL worksheet.

2. In the role selector at the top right of the worksheet, ensure that the SECURITYADMIN role or a higher role is selected. If it is not, click the drop-down and select that role.

3. Run the following command in the worksheet:

   ALTER USER <your_snowflake_user> SET RSA_PUBLIC_KEY='<public_key>';
   
   // Example
   
   ALTER USER HARRY_POTTER set RSA_PUBLIC_KEY='MIIBIjANBgk...';
   

   Note:

   • Replace the placeholder values in the command above with your own. For example, <your_snowflake_user> with HARRY_POTTER.

   • Set the public key value to the content between -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----.

To check whether the public key is configured correctly, read Verify the user’s public key fingerprint in the Snowflake documentation.

Obtain your Snowflake Account Identifier

Activate identifies your Snowflake account using either its account identifier or its account URL. Both are accepted in the same field.

Perform the following steps to obtain your account identifier:

1. Log in to your Snowflake account.

2. In the bottom left corner, click your account name to open the account menu.

   

3. Hover over Account, and then click View Account Details.

   

4. In the Account Details dialog, copy the Account identifier value. It is in the <organization_name>-<account_name> format. For example, HEVODATA-ABCDXYZ.

   

Specify this value in the Account Identifier/URL field while configuring your Source in Activate.