How-To: Connect to Snowflake

The Flywheel Audience Builder Application connects directly to Snowflake so you can leverage the customer data in your data warehouse to create audiences with just a few clicks.

To establish the connection to Snowflake, here are the key steps we will walk through together.

1. Creating a public/private key pair
2. Creating a Snowflake user and role with proper permissions
3. Create schema to store audience snapshots
4. Providing the connection information to the GrowthLoop App interface during onboarding
5. Create a storage integration

1. Create Public/Private Key Pair

The first step in the preparation process to connect your Snowflake data warehouse to the Flywheel Application is to create a key pair for API authentication. To do so, please follow these steps:

‍

1. Open your local machine’s command prompt or terminal and run the following command to generate your private key:

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

‍

After running this command, you will be prompted for an encryption password. Please enter any secure password and keep record of this for later use.

‍

2. To generate your public key next, please run the following command:

openssl rsa -in fw_private_key.p8 -pubout -out fw_pub_key.pub

‍

After running this command, you will be prompted for the encryption password set above. Please enter the encryption password and take note of where your public and private key are stored for later use.

2. Create the Snowflake User and Role for Connection with the Flywheel Application

You will now need to create a designated USER and ROLE you would like to have all privileges required for the GrowthLoop Application to query your data warehouse. You can use an existing user and role, but we recommend a new one for sustainability reasons. To do this, please follow these steps:

1. Login to your Snowflake account with a user that is assigned administrative privileges to manage users and roles. A common admin role with the necessary privileges is ACCOUNTADMIN or SECURITYADMIN.

2. Open a worksheet and run the following commands:

use role <INPUT_ROLE>;
create role <fw_access_role_name>;

‍

If you encounter any permission errors, ensure that you have a properly privileged role selected in your Snowflake worksheet (pictured in top right corner of image below).

//Replace <INPUT_ROLE> with with a role that has administrative privileges to manage users and roles (ex: SECURITYADMIN)

IMPORTANT NOTE: If running multiple commands ensure the “All Queries” box is checked like in the below example:

‍

3. Assign privileges to the role using the following commands:

use role <INPUT_ROLE>;
grant usage on warehouse COMPUTE_WH to role <fw_access_role_name>;
grant usage on database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;
grant usage on all schemas in database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;
grant select on all tables in database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;
grant select on all views in database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;
grant usage on future schemas in database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;
grant select on future tables in database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;
grant select on future views in database CUSTOMER_DATA_MODEL to role <fw_access_role_name>;

//Replace COMPUTE_WH with the name of your data warehouse

//Replace fw_access_role with the name of the role created in the 'Create a Role' step above

//Replace CUSTOMER_DATA_MODEL with the name of the database to be connected to flywheel

‍

4. Create User and assign Role with the following commands:

use role <INPUT_ROLE>;
create user flywheel_service_user password='abc123' default_role = <fw_access_role_name> default_warehouse = COMPUTE_WH default_namespace = CUSTOMER_DATA_MODEL;
grant role <fw_access_role_name> to user flywheel_service_user;

‍

5. Lastly, assign your public key to User with the following commands:

use role <INPUT_ROLE>;
alter user flywheel_service_user set rsa_public_key='MIIBIjANBgkqh...';

‍

//the rsa_public_key value can be found in the fw_pub_key.pub file created above. Exclude the '------BEGIN PUBLIC KEY------' and '-----END PUBLIC KEY-----' header and footer in this value.

3. Create schema to store audience snapshots

In the worksheet space in Snowflake, we will run a query to create a schema named flywheel_system that will be used to store audience snapshots when audiences are exported from the application. In the Snowflake UI, navigate to the database where your customer data is stored. This should be the same database that was referenced in step 2 as CUSTOMER_DATA_MODEL. Run the following query, and replace CUSTOMER_DATA_MODEL with the relevant database in your snowflake data warehouse.

use role <INPUT_ROLE>;
use database <CUSTOMER_DATA_MODEL>;
create schema flywheel_system;

‍

After creating the schema, ensure the role attached the Flywheel service account (created in step 2) has full permissions on this schema. Run the following command:

use role <INPUT_ROLE>;
grant all on schema <CUSTOMER_DATA_MODEL>.flywheel_system to role <fw_access_role_name>;

‍

4. Connect to Snowflake in the Flywheel Application

The last step is to establish the connection with your Snowflake data warehouse using the key pair, user, and role created in the previous steps.

Once you begin the onboarding process, you will see the options pictured below:

After selecting Snowflake, you’ll be prompted to enter inputs for the following fields (described below):

Name: This will be the name used to refer to your Snowflake DWH in the Flywheel Application going forward. This can be any name you choose.

Description: This will be the description associated with your Snowflake DWH in the Flywheel Application going forward, should you want to provide one. This is optional and can be any description string you choose.

Account: This can be pulled from the URL in your browser when logged into Snowflake. The account in the following screenshot example would be: xy12345.us-central-1.gcp

The Account input required will change slightly depending on the cloud provider in which your Snowflake account is hosted. Please refer to the Snowflake documentation below for specifics for your cloud provider (AWS, GCP, Azure, etc.).

https://docs.snowflake.com/en/user-guide/admin-account-identifier.html#non-vps-account-locator-formats-by-cloud-platform-and-regio

Username: This will be the USER created in the “Create Snowflake User and Role” Section of this documentation.

Role: This will be the ROLE created in the “Create Snowflake User and Role” Section of this documentation.

Passphrase: This will be the encryption password used when creating the private key in the “Create Public/Private Key Pair” section of this documentation.

Private Key: Please click upload file and select the private key created in Step 1, this will be stored on your machine with a .p8 extension

Now to Connect Your First Customer Table!

After your connection has been established, click continue and you will see the following page:

For Dataset, enter the correct [database] -> [schema] mapping.

For Table, select the desired customer table you are looking to connect found in the schema selected above.

For Table Alias, enter any name for this table you'd like. This name will be used in the application to help you know which table you are filtering on.

For Table Description, enter any description you'd like, if desired, to help explain this table to any application users.

Next, scroll down and you will see Table Configuration setup:

In this modal, select the unique key field for this table, the Device ID field (if you have one, otherwise indicate email or phone number field, if available), and then any fields you wish to show on default in the Audience Builder application. This can always be changed and configured later per your user's preferences, so no stress to get this perfect the first time around.

Then, click Finish and you will be redirected to your Audiences landing page. You are now all set to begin building your first audience!

‍

5. Configure a storage integration

You are now all set to begin building audiences, but before you export your first audience to your first destination you must also configure a storage integration. GrowthLoop provides a few different options for configuring a storage integration based on your organizations preferences and cloud provider. Please reach out to your assigned Solutions Architect or send an email to solutions@growthloop.com for more information and instructions.

Issues Connecting?

Reach out at any time to solutions@growthloop.com should you encounter any issues when connecting to your Snowflake data warehouse. We are always happy to help troubleshoot for your specific circumstance!

What’s next?

Now that you've connected your data warehouse source and set up your first customer table, learn how to create an audience! Check out Create an Audience for more details.

‍