Snowflake as a Target

Introduction

Snowflake is a powerful data warehouse that can be used as a Target for data integration workflows. With Rivery, you can easily extract data from a variety of Sources and load it into Snowflake for analysis and reporting.

This documet outlines how to work with Snowflake as a Target.

Before you use this guide, Please make sure you have a valid Snowflake account.

If you don't have any valid Snowflake account, please create one. If you or your company already has a Snowflake account, you can connect/create a Rivery user.

Setting up your Snowflake Environment

Rivery's Snowflake environment can be configured in two ways, but only one method for granting access must be chosen:

The first method will grant an existing user the SYSADMIN role, which will allow rivery access to all object in the database and will be able to extract from, and ingest to, new and existing tables.

The second method will either create or use existing user, role, database and warehouse depending on the settings, and will either give Rivery the ability to ingest data to and/or extract data from (depending on the settings) said objects.

First Method - Global Permission With Sysadmin Role

Snowflake allows a single user to have multiple roles. Rivery will need to execute various commands on Snowflake, so you must assign a SYSADMIN role to the Rivery user you’ll be leveraging:

1. Access your Snowflake console. Utiliza a user that has access to the ACCOUNTADMIN role

2. Select the worksheet tab.

3. Run the following commands:

   begin;
   
    /* Set variables for script, select an existing user or create a new one, replace the value after var_user with 
    the name of the user, do not forget to replace {password} with the password of your choice 
    if you are creating a new user. */
   
      set var_user = 'RIVERY_USER';
      set var_password = '{password}';
      
    /* switch to ACCOUNTADMIN role: only an ACCOUNTADMIN can set or unset a user’s role */
       USE ROLE accountadmin;
       
       /* Create a user for Rivery or use an existing one */
      create user if not exists identifier($var_user)
      password = $var_password;
      
       GRANT ROLE SYSADMIN TO USER identifier($var_user);
       ALTER USER identifier($var_user) SET DEFAULT_ROLE = SYSADMIN;
   commit; 
   

Second Method - Specific Permission With Custom Role

Copy the following script to your Snowflake console, make sure to change the variables to your desired variable and only run necessary steps, pay attention to the instructions on the script.

begin;
 
   /* Set variables for script, You can choose to work with the default suggested values in the script or use your own. 
   Do not forget to replace {password} with the password of your choice if you are creating a new user. */
   set var_user = 'RIVERY_USER';
   set var_password = '{password}';
   set var_role = 'RIVERY_ROLE';
   set var_database = 'RIVERY_DATABASE';
   set var_warehouse = 'RIVERY_WAREHOUSE';

   
   /* Switch to securityadmin role:  
Role that can manage any object grant globally, as well as create, monitor, and manage users and roles */
   use role securityadmin;
 
   /* Create role for Rivery */
   create role if not exists identifier($var_role);
   grant role identifier($var_role) to role SYSADMIN;
 
   /* Create a user for Rivery */
   create user if not exists identifier($var_user)
   password = $var_password
   default_role = $var_role
   default_warehouse = $var_warehouse;
 
   grant role identifier($var_role) to user identifier($var_user);
 
   /* switch to sysadmin role: 
Role that has privileges to create warehouses and databases (and other objects) in an account. */
   use role sysadmin;
 
   /* Create a warehouse for Rivery, this step is optional */
   create warehouse if not exists identifier($var_warehouse)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 300
   auto_resume = true
   initially_suspended = true;
 
   /* Create database for Rivery, this step is optional */
   create database if not exists identifier($var_database);
   
 
   /* Grant Rivery role access to warehouse */
   grant USAGE
   on warehouse identifier($var_warehouse)
   to role identifier($var_role);
 
   /* grant Rivery access to database */
   grant CREATE SCHEMA, MONITOR, USAGE
   on database identifier($var_database)
   to role identifier($var_role);
   
   /* ATTENTION! if you are looking to ingest and extract data from objects created within Rivery 
   and not objects that already exist you can stop the flow here */
   
   /* Grant access to all existing sachems on the database */
   grant ALL on all SCHEMAS IN DATABASE identifier($var_database) to ROLE identifier($var_role);

   /* Grant access to all  existing tables on the database, might take several minutes if there are many tables */
  grant ALL on ALL TABLES IN DATABASE identifier($var_database) to ROLE identifier($var_role);
   
 
 commit; 
 

Please Note:
If you wish to add Masking Policy permissions for this user, please refer to the 'Enforce Masking Policy' section.

Create Network Policy for Rivery IPs [Optional]

In some cases, your Snowflake account may be access restricted by IPs or domains. In such cases, you must add Rivery IPs to your Snowflake Network Policy in order to connect successfully.

Note: Open Rivery IPs in Snowflake Network Policies may block any other unspecified IPs in the network policy. Make sure you've whitelisted all of your IPs in Snowflake’s network policies before creating Rivery's. Read more about network policies here .

In order to create a Network Policy for Rivery IPs:

1. Log into your Snowflake account.

2. Make sure the user is set to a ACCOUNTADMIN or SYSADMIN role

3. In the worksheet, run the following command:

CREATE OR REPLACE NETWORK POLICY RiveryIPs ALLOWED_IP_LIST = (Copy our most recent whitelisted IPs here)

Connection Procedure

You can connect to Snowflake using one of two methods:

• Basic Authentication
• Key-Pair Authentication

Basic Authentication

To connect to your Snowflake account using Basic Authentication, follow the steps below:

1. Go to the Connections menu in Rivery:
   

2. In Snowflake Connection form type in your Connection Name.

3. Select the Basic Authentication Type.

4. Enter your Username and Password.

5. Enter the name of your Warehouse (mandatory).

6. Enter the name of your Role (optional).
   Note:
   If you leave it blank, the account's default one will be used, and in that case, the default Role must have access to the selected Warehouse, or the connection will fail.

7. Input your Locator.

8. Use the Test Connection function to see if your connection is up to the task.
   If the connection succeeded, you can now use this connection in Rivery.

3. Set your Custom FileZone to save the data in your own staging area (Optional).
4. Use the Test Connection function to see if your connection is up to the task. If the connection succeeded, you can now use this connection in Rivery.
   You can now pipe data to Snowflake by leveraging this connection in any river!

Key-Pair Authentication

To connect to your Snowflake account using Key-Pair Authentication, follow the steps below:

1. Open a Terminal window to generate the private key.
   (Mac / Linux)

2. Enter the following command into your Terminal window to download the Key file to your Documents folder:

cd Documents

Please Note:
To confirm the command, click the Enter key.

3. Run the following command to generate an unencrypted version of the Key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

4. Create the Public key by referencing the Private key. The following command assumes the private key is encrypted and stored in the file rsa key.p8.
   Simply copy and run it:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

5. This will allow you to generate a private key in PEM (Privacy Enhanced Mail) format:

cat rsa_key.pub

This is the PEM format:

6. To assign the public key to a Snowflake user, run the ALTER USER command in your Snowflake console:
   Note:
   Replace < Username > with your snowflake Username.

alter user < Username > set rsa_public_key='';

7. Copy the PEM format from step 5 and paste it between the apostrophes in the Snowflake console code:

8. Go to the Connections menu in Rivery:
   

9. Fill out the Snowflake Connection form with the following information:

a. Type in Connection Name.
b. Select the Key-Pair Authentication Type.
c. Enter your Username.
d. Enter the name of your Warehouse (mandatory).
e. Enter the name of your Role (optional).
Note:
If you leave it blank, the account's default one will be used, and in that case, the default Role must have access to the selected Warehouse, or the connection will fail.
f. Input your Locator.

10. Drag the rsa_key.p8 file from your Documents folder to the label, or simply browse for it.

11. Set your Custom FileZone to save the data in your own staging area (Optional).

12. Use the Test Connection function to see if your connection is up to the task.
    If the connection succeeded, you can now use this connection in Rivery.

Consult the Snowflake documentation to generate an encrypted Key or for more information on the Key-Pair configuration process.

Enforce Masking Policy

A column-level security feature called Masking Policy employs masking strategies to selectively hide plain-text data in the Target table columns.

Masking policies in Snowflake are schema-level objects, hence a database and schema must exist in Snowflake before a masking policy can be applied to a column.

When this feature is selected, Rivery makes sure to confirm your masking policy and apply it to each location the column appears during the River run.

Prerequisites

To use the Enforce Masking Policy feature, you must have your account set up in Snowflake with the necessary permissions, and have this account listed under Snowflake Enterprise Edition.

To grant the permissions required to use this functionality, follow the steps below:

• If you set up your Snowflake Environment using the First Method, you are ready to go and your account has the required permissions.
• If you configured Snowflake using the Second Method, add the following query to the second method's query and run it in the Snowflake console after specifying your Role, Database, and Schema in the relevant variables:

begin;
   /* ATTENTION! Only Enterprise account holders that employ masking policies should use the script below. */

   /* Set variables for script */
   set var_role = 'RIVERY_ROLE';
   set var_database = 'RIVERY_DATABASE';
   set var_schema = 'RIVERY_SCHEMA';
   /*  Switch to Securityadmin role:
        Role that can create and control Users and other Roles as well as manage any        object. */
   use role securityadmin;
   /* Grant Masking Policies on a specific schema */
   grant create masking policy on schema    identifier($var_database).identifier($var_schema) to role identifier($var_role);
   grant apply masking policy on account to role identifier($var_role);
commit;

Working With Enforce Masking Policy

In Rivery, the Enforce Masking Policy option can be found in 3 different locations:

• Source to Target Rivers
  • Predefined Reports / Multi-Tables Loading Mode
• Logic Rivers

Source to Target Rivers

When working with Source to Target Rivers, you get to choose you preferable River Mode. The Enforce Masking Policy Appears in 2 locations:
In Predefined Reports or Multi-Tables modes, the option appears in the Table Setting of a specific table:

Any other River mode will include it under Additional Options:

Logic Rivers

In Logic Rivers you can locate it under the Target section:

Limitations

• Please make sure not to use quotations while defining the Masking Policy Identifier value. It must also begin with an alphabetic character and cannot contain spaces or any other special characters.
• Your account must be configured with the proper permissions in Snowflake and listed under Snowflake Enterprise Edition.
• In the event that 'Enfore Masking Policy' was checked and there isn't a masking policy associated with the Target table, the River won't run.

Escape Character (Optional)

Escape characters allow special characters in strings to be interpreted as literal characters, rather than as control characters. In SQL, escape characters are used to change the behavior of certain characters, such as the single quote ('), the double quote ("), the backslash (\), etc.

Working With Escape Characters

Rivery offers the option to use escape characters in Snowflake, making it easier to run complex SQL statements.
To activate this feature, navigate to the Additional Options section located at the bottom of the Target's tab screen, choose the escape character of your choice from the list, and Rivery will properly encode and run the statement in Snowflake.

Snowflake Connector for Python

Rivery makes use of the Snowflake Connector for Python, which enables Python developers to establish a connection to the Snowflake data warehouse and run SQL queries.