In this tutorial, we’ll walk you through spinning up your own Snowflake data warehouse and connecting it to Stitch.
Snowflake Setup Requirements
To set up Snowflake in Stitch, you need:
A Snowflake account.
ACCOUNTADMINrole privileges in Snowflake, OR privileges equivalent to the
SYSADMINroles. More info on Snowflake’s user roles can be found here.
Familiarity with Snowflake’s SQL Worksheet feature OR access to to a SQL client.
This tutorial will use the SQL Worksheet in the Snowflake web app to run SQL commands.
Step 1: Create a Snowflake Data Warehouse
- 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.
Create the warehouse by running this command, changing the
[square_brackets]to the values you want:
CREATE WAREHOUSE [stitch_warehouse] WITH AUTO_SUSPEND = [time_in_seconds] AUTO_RESUME = TRUE|FALSE WAREHOUSE_SIZE = [size];
The parameters in this command define the following:
- AUTO_SUSPEND: Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.
Important: **Make sure Auto-Suspend is enabled:
Make sure the `auto_suspend` parameter is included in the warehouse creation command. This parameter determines how many seconds of inactivity must pass before a warehouse is automatically suspended. If this parameter isn't included, the default will be `NULL`, meaning that the warehouse will never automatically suspend. As a result, Snowflake credits will continue to be consumed even if the warehouse is inactive.
- **AUTO_RESUME**: If `TRUE`, the warehouse will be automatically resumed when accessed by a SQL statement. If `FALSE`, the warehouse will only start again when explicitly resumed through the Snowflake web interface or using `ALTER WAREHOUSE`. - **WAREHOUSE_SIZE**: Specifies the size of the warehouse to create. Accepted values are `XSMALL`, `SMALL`, `MEDIUM`, `LARGE`, `XLARGE`, `XXLARGE`, `XXXXLARGE`, and `XXXXLARGE`. The default is `XSMALL`.
Additional warehouse parameters are available. Check out Snowflake’s documentation for detailed explanations.
Step 2: Create a Stitch Database & Database User
Next, you’ll create a database and database user for Stitch.
Step 2.1: Create the Database
Create the database for Stitch, changing
[stitch_database] to what you want the database to be called:
CREATE DATABASE [stitch_database];
Step 2.2: Create the Database User
Create a role for the Stitch user:
CREATE ROLE [stitch_role] COMMENT = 'Role for Stitch access';
Optional: If you’ve created a hierarchy that assigns all custom roles to the
SYSADMINrole, grant the
GRANT ROLE [stitch_role] to role SYSADMIN;
Grant warehouse privileges to the Stitch role, using the name of the warehouse you created for Stitch:
GRANT ALL ON WAREHOUSE [warehouse] TO ROLE [stitch_role];
Note: This will grant all privileges except ownership.
Grant database privileges to the Stitch role, using the name of the database you created for Stitch:
GRANT ALL ON DATABASE [stitch_database] TO ROLE [stitch_role];
Note: The privileges granted in steps 3 and 4 of this section will only apply to the warehouse and database you specify in the above queries. The Stitch user will not be granted privileges to any other warehouse or database unless you elect to do so.
Create the Stitch user and grant the Stitch role to the user:
CREATE USER [stitch_user] PASSWORD='[password]' COMMENT='User for Stitch database user' DEFAULT_ROLE='[stitch_role]' DEFAULT_WAREHOUSE='[warehouse]'; GRANT ROLE [stitch_role] TO USER [stitch_user];
Step 3: Configure Network Access Settings
In Snowflake, access is configured and managed through Network Security Policies.
Stitch’s IP addresses must be added to a network policy’s Allowed IP List for the connection to be successful.
Create the network policy and add Stitch’s IP addresses to the list of allowed IP addresses.
In the command below, change
[your-current-ip-address]to the current IP address of the computer you’re working on - this is required for the next step:
CREATE NETWORK POLICY [stitch_policy] ALLOWED_IP_LIST = ('220.127.116.11/32','18.104.22.168/32','22.214.171.124/32','126.96.36.199/32','[your-current-ip-address]');
Apply the network policy to the account. Note that your current IP address must be included in the Allowed IP List to run this command successfully:
ALTER ACCOUNT SET NETWORK_POLICY = [stitch_policy];
If you encounter an error, ensure that your current IP address is in the Allowed IP List and try again. Contact Snowflake support if errors persist.
Step 4: Connect Stitch
- If you aren’t signed into your Stitch account, sign in now.
- Click the Destination tab.
- Click the Snowflake icon.
Fill in the fields as follows:
Host (Endpoint): Enter the URL of your Snowflake account. For example:
Username: Enter the Stitch Snowflake user’s username.
Password: Enter the password associated with the Stitch Snowflake user.
Database: Enter the name of the Snowflake database that you created for Stitch.
Warehouse: Enter the name of the Snowflake warehouse that you created for Stitch.
When finished, click Update Snowflake Settings.
Stitch will perform a connection test to the Snowflake database; if successful, a Success! mesage will display at the top of the screen. Note: This test may take a few minutes to complete.
Troubleshooting connection errors
If you receive an error message, we recommend trying these troubleshooting steps for common destination connection problems before reaching out to support.
We’ve found that the majority of the time, these resources can resolve most issues.
Learning about Stitch and Snowflake
Now that your Snowflake data warehouse is up and running, we recommend learning about how Stitch will load your data into it.
Snowflake Destination Overview: This overview will give you the high-level basics for working with your Snowflake data warehouse.
Destination Data Loading Guide: This article covers many of the common scenarios Stitch will encounter when loading data into your Snowflake destination and the expected behavior for each scenario.