Snowflake is quick to get configured, especially with our fully-automated Snowflake Worksheet commands. Please note, that the process detailed in this doc will not work without an active, billing-enabled Snowflake account. If you have not done so, please make sure you have updated your billing information in Snowflake.

Getting Started

There are a few key connection details that are needed to configure Snowflake as a destination properly:

  • Account
  • Warehouse
  • Database
  • Schema
  • User
  • Password

You will take note of all of these details as they will be needed within the Openbridge Snowflake data destination registration process.

Understanding Snowflake Connection Details

The process to configure key Snowflake connection details is mostly automated, as we detail below. However, there are specific aspects of Snowflake required for us to connect and deliver data properly.

Account: The name of your Snowflake account. The account name would be those items in bold:

  • <account>.snowflakecomputing.com
  • <account>.<region_id>.snowflakecomputing.com
  • <account>.<region_id>.azure.snowflakecomputing.com

For example, If your Snowflake address is

https://hzc88038.us-east-1.snoflakecomputing.com then your account name is hzc88038.us-east-1. If you are on Azure, your address would be https://hzc88038.us-east-1.azure.snoflakecomputing.com, and the account name is hzc88038.us-east-1. azure

For reference, see this:

Lastly, only reference the account name, not the full hostname or protocol (HTTPS://).

Verify Snowflake Network Policies

Your Snowflake account may restrict external access. If this is the case, you need to CREATE or ALTER your Snowflake Network Policy to allow us to connect successfully. This means our IP addresses must be added to a network policy's Allowed IP List for a connection to be successful. This can be done in the Snowflake web interface or via SQL command in the Snowflake Worksheet. Below is an example of commands you would run in the Worksheet:

/*
If you want to use a network policy or modify an exsiting one, uncomment the one (create or alter) that aligns with your needs.
Our IP addresses are 52.2.68.68/32, 52.54.227.22/32
*/

--create network policy openbridge_ip_whitelist ALLOWED_IP_LIST = ('52.2.68.68/32', '52.54.227.22/32');
--alter network policy <your_current_network_policy_name> SET {[ALLOWED_IP_LIST] = ('52.2.68.68/32', '52.54.227.22/32')]};

These commands are present in the automated SQL command setup command. You would uncomment the most appropriate (CREATE or ALTER) for your use case. For more information on network policies, see Snowflake network policies documentation.

Automating Snowflake Configuration

With the definitions done, we can now configure Snowflake. The process will use an automated Snowflake Worksheet approach.

The following inputs are needed by Openbridge to register Snowflake as a destination:

  • Warehouse: We recommend creating a new warehouse dedicated to Openbridge. Our automated config process defaults to an X-Small warehouse to avoid conflicts. You can always scale this upwards as needed.
  • Database: Enter the name of the database you created.
  • Schema: Enter the name of the database you created.
  • Username: Enter the Snowflake database user's username.
  • Password: Enter the password for the Snowflake database user.

In the steps below we detail how to get these configured and ready.

Step 1: Log into your Snowflake account using a web browser or a SQL client

If you log in via a web browser, click the Worksheet icon at the top of the page. Ensure that the role for the session is set to ACCOUNTADMIN to complete this process.

Step 2: Prepare your automated commands

Your Snowflake environment will use the automated setup SQL command below. There are a few edits you will need to make. First, make sure you change the username, password, and any other variables as needed in the commands. They will be:

  • role_name = 'OPENBRIDGE_ROLE';
  • user_name = 'changeme';
  • user_password = 'changeme';
  • warehouse_name = 'OPENBRIDGE_WAREHOUSE';
  • database_name = 'OPENBRIDGE_DATABASE';
  • schema_name = 'OPENBRIDGE_SCHEMA';

Step 3: Run The Automation Script

Here is the script to paste into your Snowflake WORKSHEET space. Don't forget to change the items from Step 2!

/*
You can set up access to Snowflake in a few simple steps.
First, log in to your Snowflake account and open up an SQL worksheet.
The SQL commands below will allow you to configure a complete data destination

Ensure that the role for the session is set to ACCOUNTADMIN in order to complete this process.
*/

begin;

/*
The following variables must be set. You can change these defaults to values suited for your Snowflake deployment.
Remembers to change the password a unique value.
*/
set role_name = 'OPENBRIDGE_ROLE';
set user_name = 'changeme';
set user_password = 'changeme';
set warehouse_name = 'OPENBRIDGE_WAREHOUSE';
set database_name = 'OPENBRIDGE_DATABASE';
set schema_name = 'OPENBRIDGE_SCHEMA';

/*
We need to set the role to properly be authorized to perform various operations
*/
use role accountadmin;

/*
If you want to use a network policy or modify an exsiting one, uncomment the one (create or alter) that aligns with your needs.
Our IP addresses are 52.2.68.68/32, 52.54.227.22/32
*/

--create network policy openbridge_ip_whitelist ALLOWED_IP_LIST = ('52.2.68.68/32', '52.54.227.22/32');
--alter network policy <your_current_network_policy_name> SET {[ALLOWED_IP_LIST] = ('52.2.68.68/32', '52.54.227.22/32')]};

/*
We will set a role to use for making connection
*/
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

/*
Create a user and assign authorizations
*/
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;

grant role identifier($role_name) to user identifier($user_name);

/*
We can now switch to the default system admin role for the next operations
*/
use role sysadmin;

/*
Creating the warehouse data destination. Adjust these defaults as needed in accordance with current Snowflake best practices
*/
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 600
auto_resume = true
initially_suspended = true;

/*
Creating the database and schema within your data destination
*/
create database if not exists identifier($database_name);
create schema if not exists identifier($schema_name);

/*
Authroize the role to use the warehouse, database, and schema
*/
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);

grant CREATE SCHEMA, MONITOR, USAGE
on database identifier($database_name)
to role identifier($role_name);

grant ALL
on schema identifier($schema_name)
to role identifier($role_name);

commit;

Step 3: RUN your Snowflake Worksheet

With your edits in place, you can paste the content in the Snowflake Worksheet. Make sure you have selected "All Queries" when you start your RUN. You should see a "success" output from Snowflake. If not, check your inputs in the script. We also suggest reviewing that the Warehouse, Database, Schema...are present and align with your configuration.

Snowflake is ready to receive data!

If all looks good, you have now configured a Snowflake destination. Make sure to note the connection details, as you will need to enter those into the Openbridge destination registration process.

Did this answer your question?