How to OAuth Connect to Snowflake for Your Alteryx Macro

Alteryx’s graphical workflow tool allows users to prepare and analyze data from various sources without requiring extensive coding knowledge. Users can perform a wide range of data operations, such as data cleansing, transformation, blending, modeling, predictive analytics, and spatial analytics.

We will walk you through connecting Snowflake Data Cloud to an Alteryx Analytics Cloud Service, with a focus on authentication using an OAuth 2.0 client. For Basic Type credentials that use a username & password and more ways to connect, you can reference the “Snowflake Connections” section in Alteryx’s documentation.

In this blog, you will learn how to:

Set up the Environment.
Create a Snowflake Alteryx Role in Snowflake.
Create OAuth credentials that use our new role in Snowflake.
Create a network policy to whitelist Alteryx cloud servers in Snowflake.
Register Snowflake OAuth client in Alteryx.
Create a connection that authenticates with the registered OAuth client in Alteryx.
Import a dataset in Alteryx.

Why Should You Consider This Implementation?

Alteryx is the leader in data blending and advanced analytics software. Alteryx Analytics provides analysts with a graphical workflow for data blending and advanced analytics. The Alteryx analytics platform delivers deeper insights by blending internal, third-party, and cloud data and then analyzing it using spatial and predictive drag-and-drop tools.

This is all done in a single workflow, with no programming required. Together, Snowflake Data Cloud and Alteryx deliver both the infrastructure and the interface to power analytics for all.

Step 1: Set up the Environment

Set Up Snowflake

Before you start, you will need to have a Snowflake Virtual Warehouse up and running with a user who can connect and query the cluster. Here are some helpful links below to get you started:

Set Up Alteryx

For this you will need to create an Amazon RDS account.

Set Up Sample Data

Feel free to use your own data, but for the sake of convenience, when going through this blog, you can use the code snippet below.

				
					// Database
DROP DATABASE IF EXISTS PHDATA_SNOWFLAKE_ALTERYX_DATABASE;
CREATE DATABASE PHDATA_SNOWFLAKE_ALTERYX_DATABASE;

// Table
DROP TABLE IF EXISTS PUBLIC.PHDATA_SAMPLE_DATA;
CREATE TABLE IF NOT EXISTS PUBLIC.PHDATA_SAMPLE_DATA (
	code varchar(1) NULL,
	code_text varchar(8) NULL
);

// Data
INSERT INTO PUBLIC.PHDATA_SAMPLE_DATA (code,code_text) VALUES
    ('1','phdata_1'),
    ('2','phdata_2'),
    ('3','phdata_3'),
    ('4','phdata_4'),
    ('5','phdata_5'),
    ('6','phdata_6'),
    ('7','phdata_7'),
    ('8','phdata_8'),
    ('9','phdata_9'),
    ('0','phdata_0')
;

Step 2: Create a Snowflake Alteryx Role in Snowflake

				
					DROP ROLE IF EXISTS SNOWFLAKE_ALTERYX_ROLE;
CREATE ROLE IF NOT EXISTS SNOWFLAKE_ALTERYX_ROLE;
GRANT ROLE SNOWFLAKE_ALTERYX_ROLE to ROLE ACCOUNTADMIN; // Current Role 
GRANT USAGE on WAREHOUSE COMPUTE_WH to role SNOWFLAKE_ALTERYX_ROLE; // Default Warehouse
GRANT USAGE on database PHDATA_SNOWFLAKE_ALTERYX_DATABASE to role SNOWFLAKE_ALTERYX_ROLE; 
GRANT USAGE on schema PHDATA_SNOWFLAKE_ALTERYX_DATABASE.PUBLIC to role SNOWFLAKE_ALTERYX_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA PHDATA_SNOWFLAKE_ALTERYX_DATABASE.PUBLIC to ROLE SNOWFLAKE_ALTERYX_ROLE;
GRANT SELECT,INSERT ON FUTURE TABLES IN SCHEMA PHDATA_SNOWFLAKE_ALTERYX_DATABASE.PUBLIC to ROLE SNOWFLAKE_ALTERYX_ROLE;

Confirm Role access.

				
					// Test Permissions of Role
USE ROLE SNOWFLAKE_ALTERYX_ROLE;
USE DATABASE PHDATA_SNOWFLAKE_ALTERYX_DATABASE
SELECT * FROM PUBLIC.PHDATA_SAMPLE_DATA;

// Switch Back for More Admining
USE ROLE accountadmin;

Step 3: Create OAuth Credentials that use our new Role in Snowflake

				
					CREATE OR REPLACE SECURITY INTEGRATION OAUTH_ALTERYX
         TYPE = OAUTH
         OAUTH_CLIENT = CUSTOM
         OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
         OAUTH_REDIRECT_URI = 'https://us1.alteryxcloud.com/oauth2/callback' 
         ENABLED = TRUE
         OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE
         OAUTH_ISSUE_REFRESH_TOKENS = TRUE
         OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
         PRE_AUTHORIZED_ROLES_LIST = ('SNOWFLAKE_ALTERYX_ROLE')
    NETWORK_POLICY = 'ALERYX_CLOUD_SERVER'
;

Run the Query below to obtain:

OAUTH_CLIENT_ID
OAUTH_AUTHORIZATION_ENDPOINT
OAUTH_TOKEN_ENDPOINT
OAUTH_REFRESH_TOKEN_VALIDITY

				
					DESC SECURITY INTEGRATION OAUTH_ALTERYX;

Run the Query below to obtain:

OAUTH_CLIENT_SECRET

				
					SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_ALTERYX');

Step 4: Create a Network Policy to Whitelist Alteryx Cloud Servers in Snowflake

Navigate to Admin > Security:

IP Addresses obtained via here.

After successfully creating, be sure to activate the Network Policy.

Step 5: Register Snowflake OAuth Client in Alteryx

Navigate to Admin Console > OAuth 2.0 Clients.

Click Register OAuth 2.0 Client and register using the appropriate information from the Snowflake OAUTH_ALTERYX Security Integration.

600000 is provided via official documentation here

Step 6: Create a connection with the registered OAuth Client in Alteryx

Navigate to the Connections tab. Click Create Connection.

Select Snowflake.

Create Connection.

Authenticate Connection.

Step 7: Import A Dataset in Alteryx

Click Browse Data to browse database objects.

Navigate to Appropriate Table and click Continue to Create.

Click Preview to preview data to confirm success.

Conclusion

This post demonstrated how to connect and load data into The Alteryx Data Platform from Snowflake. This gives you the flexibility to develop your analytics infrastructure further by quickly loading data from your Snowflake Data Cloud and combining it without sources to power your analytics further.

If your team is interested in learning more about how to extract more value from your Snowflake account, please reach out! We’d love to help kick off your project!

If you’re looking for more helpful Snowflake resources, check out our free guide to getting started with Snowflake.