Connecting Alteryx to Snowflake Using an Automation Account

This guide will help you connect Alteryx Designer and Server to Snowflake using your University of Pittsburgh Automation Account. Automation accounts are special accounts (prefixed with aa_) provisioned for scheduling and automating Alteryx workflows.

Prerequisites

• A University of Pittsburgh Automation Account - request one by contacting the Pitt IT HelpDesk

• An Alteryx Designer licensed and installed, and some experience with the tool

• Administrative privileges to install drivers if needed

Step 1: Install the Snowflake DSN-less ODBC Driver

1. Try that file to the left, or log into MyAlteryx. You may need to register if you have not visited before.

   Open

   

2. Click the download button for Drivers, and select Snowflake.

   Open

   

3. Download the Simba Snowflake driver.

4. After download, click the driver file to install.

   Open

   

Step 2: Create Snowflake Data Source in Alteryx Designer (2024.2.1.94)

1. Launch Alteryx Designer

2. Find File > Manage Connections, and click New, and find Snowflake

   Open

   

3. You’ll be prompted to answer a few questions. Use the values below…

   1. Technology: Snowflake ODBC DSN-less with Simba

   2. Data Source Name: PROD_SNOWFLAKE

   3. Server: UNKLNRS-ZOB46881.snowflakecomputing.com

   4. Driver: Simba Snowflake ODBC Driver

   5. Database: PROD_DB

   6. Schema: Your choice! PUBLIC is a fine place to start.

   7. Warehouse: PROD_GENERAL_WH

   8. Role: PROD_<Your Username>

Open

Save your Snowflake connection. Next, we’ll add your Automation Account credentials.

Step 3: Creating Credentials for your Automation Account

1. Click Credentials on the left menu of the Connection Manager window.

2. Click New to create the credential.

3. Enter the following values

   1. Method: Username and password

   2. Credential Name: <Your Automation Account e.g. AA_SCR18>

   3. Open

      

Click Save! Next we’ll join the credential to the data source so we can use it in a Quick Connect.

Note: if you are connecting to a lower environment with an automation account, the automation account has the environment prefix as well - e.g. QA_AA_SCR18

Step 4: Join the Credential to the Data Source

1. Return to the Data Sources tab in the Connection Manager, and click on the PROD_SNOWFLAKE data source we created previously.

2. Scroll to the bottom and find the Connections section. Click the “Connect Credential” button.

3. Select your credential and select “Link”

4. Open

   

Your data source is now ready to connect in an Alteryx Workflow.

Step 5: Making the Connection

1. Drag a new Input Data tile to the workspace. Ensure “Use Data Connection Manger (DCM)” is selected, and click Set Up a Connection.

2. Select Data sources on the left, and select Snowflake | Quick Connect.

3. Click the PROD_SNOWFLAKE connection previously created, scroll to the bottom and identify your Automation Account credential, and click Connect.

4. You can now utilize the query builder tools and SQL editor to pull data from Snowflake

Open

5. One last important thing! You must Synchronize your connections with the Alteryx Server before you can publish your Snowflake connected workflow. Open File > Manage Connections and find the Synchronize option on the left menu. Click on the Alteryx Server Gallery, and click Synchronize.

6. You can now publish your Alteryx workflow to Alteryx server and schedule it to run.

Step 6: Creating Connections for Bulk Loading to Snowflake (for Snowflake Hosting users)

Analytics power users and other data professionals who need to store data utilize the Snowflake Hosting service. Those users can utilize Alteryx to create and store reusable data sets in their own responsibility center controlled schema. Alteryx can take advantage of Snowflake’s bulk load capability to rapidly ingest data. To do so, additional connection types have to be configured.

1. Open File > Manage Connections, and click Data Sources. Create a new Data Source

2. Select the following values

   1. Technology: Snowflake Bulk DSN-less with Simba (Local Staging User)

   2. Data Source Name: PROD_SNOWFLAKE_BULK

   3. Server: UNKLNRS-ZOB46881.snowflakecomputing.com

   4. Driver: Simba Snowflake ODBC Driver

   5. Database: PROD_DB

   6. Schema: You’ll need to select the schema name created for your responsibility center or project. You will need to have been authorized to be a member of the schema edit group for that schema.

   7. Warehouse: PROD_GENERAL_WH

   8. Role: PROD_<Your Account Name>

3. Save the Connection.

4. Scroll to the bottom of the screen, and find the Connections section. Click “Connect Credential” and add your Automation Account credential.

You can know use this PROD_SNOWFLAKE_BULK data source to create and manipulate tables in the specified schema, assuming permissions have been granted.

1. Be sure to select the Bulk option when writing out to Snowflake, in the Data Connections > Data Sources screen

   Open

   

2. Staging Method: Local

3. When prompted for the Bulk Connection window, select “Local” for the Staging Method, and click the “Set Up a Connection” button

4. The Connection Manager window will launch. Select PROD_SNOWFLAKE_BULK, and then scroll down and find your Automation Account Credential. Click the Connect button.

5. You can now write data to your designated Snowflake schema.

Best Practices for Automation Accounts

• Keep your automation account password secure. You will be responsible for updating the password on an annual basis.

• If you encounter issues, contact Pitt IT support.

Support

For help with setting up your automation account or troubleshooting Tableau-Snowflake connections, please submit a Pitt IT Help Request