All Collections
Data Destinations
Data Warehouses
How To Setup Snowflake Data Destination
How To Setup Snowflake Data Destination

Automated Snowflake warehouse, database, and schema configuration

Openbridge Support avatar
Written by Openbridge Support
Updated over a week ago

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!

set openbridge_role = 'OPENBRIDGE_ROLE';
set openbridge_username = 'OPENBRIDGE_USER';
set openbridge_warehouse = 'OPENBRIDGE_WAREHOUSE';
set openbridge_database = 'OPENBRIDGE_DATABASE';
set openbridge_schema = 'OPENBRIDGE_SCHEMA';
-- set user password
set openbridge_password = 'password';
begin;
-- create openbridge role
use role accountadmin;
create role if not exists identifier($openbridge_role);
grant role identifier($openbridge_role) to role sysadmin;
-- create openbridge user
create user if not exists identifier($openbridge_username)
password = $openbridge_password
default_role = $openbridge_role
default_warehouse = $openbridge_warehouse;
grant role identifier($openbridge_role) to user identifier($openbridge_username);
-- change role to sysadmin for warehouse / database steps
use role sysadmin;
-- create openbridge warehouse
create warehouse if not exists identifier($openbridge_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;
-- create openbridge database
create database if not exists identifier($openbridge_database);
-- grant openbridge warehouse access
grant USAGE
on warehouse identifier($openbridge_warehouse)
to role identifier($openbridge_role);
-- grant openbridge database access
grant OWNERSHIP
on database identifier($openbridge_database)
to role identifier($openbridge_role);
commit;
begin;
USE DATABASE identifier($openbridge_database);
-- create schema for openbridge data
CREATE SCHEMA IF NOT EXISTS identifier($openbridge_schema);
commit;
begin;
-- grant openbridge schema access
grant OWNERSHIP
on schema identifier($openbridge_schema)
to role identifier($openbridge_role);
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. You should see a "Statement executed successfully" in your worksheet. If you reload the page, see your database and schema:

Make sure to note the connection details, as you will need to enter those into the Openbridge destination registration process.

Please note: If you change anything after you register a destination with Openbridge, for example a schema or database name, this will cause a connection failure. as Snowflake, nor Openbridge, will be able to reconcile the different configuration states.

Did this answer your question?