Extract Data from Snowflake Task

About the Extract Data from Snowflake Task

Snowflake is a third-party data cloud software used for storing, maintaining, and sharing data. You can import data from Snowflake to use in a Qualtrics workflow.

Integrating with Snowflake

To use this task, you must create a new integration in Snowflake and connect it to Qualtrics.

Setup in Snowflake

1. Log into Snowflake.
2. In the Worksheets tab, click the plus sign ( + ) and select SQL Worksheet.
3. Paste the following code into the worksheet:
   

   
   CREATE SECURITY INTEGRATION
   
   QUALTRICS_INTEGRATION_NAME_PUBLICAPI_G1TYPE = OAUTH
   
   OAUTH_CLIENT = CUSTOM
   
   OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
   
   OAUTH_REDIRECT_URI ='https://publicapist3.qualtrics.com/oauth-client-service/redirect'
   
   ENABLED = TRUE
   
   OAUTH_ISSUE_REFRESH_TOKENS = TRUE
   
   OAUTH_REFRESH_TOKEN_VALIDITY = 7776000

4. Replace the bolded QUALTRICS_INTEGRATION_NAME_PUBLICAPI_G1 line with a name for the integration. Please only use letters, numbers, and underscores for the integration name.
   
   Qtip: Copy down your integration name as you’ll need it later to complete the setup. Snowflake will automatically capitalize every letter in your integration name. For example, if I name my integration “Qualtrics_Integration,” then it will be saved as “QUALTRICS_INTEGRATION” in Snowflake. This is the name I should save for later.
5. Replace the bolded publicapist3 with information about your brand. This information will be different depending on if you want to set up an individual connection within the workflow itself, or an account-wide connection in the Admin tab.
   • If creating an individual connection, replace this with your brandID.
     Example: If my brandID is “dsx”, then my updated value is https://dsx.qualtrics.com/oauth-client-service/redirect’
   • If creating an account-wide connection, replace this with your datacenterID.
     Example:If my datacenterID is “iad1,” then my updated value is https://iad1.qualtrics.com/oauth-client-service/redirect’
6. Click Run.
7. You will receive a message “Integration ‘Name of Integration’ successfully created.”
   
   Qtip: If you receive a “insufficient privileges” error, then your Snowflake permissions must be updated. See this Snowflake support page for more information on which permissions are required.
8. In the same worksheet, enter the following code:

   ​​​​DESCRIBE SECURITY INTEGRATION NAME_OF_SECURITY_INTEGRATION

9. Replace the bolded INTEGRATION NAME_OF_SECURITY_INTEGRATION with your capitalized integration name from step 4.
10. Click Run.
11. You will be presented with various pieces of information about the integration. Copy down the values for the following fields:
    
    • OAUTH_AUTHORIZATION_ENDPOINT
    • OAUTH_TOKEN_ENDPOINT
    • OAUTH_CLIENT_ID
12. In the same worksheet, enter the following code:

    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS(‘NAME_OF_SECURITY_INTEGRATION’)

13. Replace the bolded NAME_OF_SECURITY_INTEGRATION with your capitalized integration name from step 4.
14. Click Run.
15. Copy one of the Client Secret values.

Setup in Qualtrics

1. If creating a brand-wide connection (i.e., a Snowflake account available to all users in your Qualtrics license), navigate to the Extensions section of the Admin tab and search for the Extract data from Snowflake integration.
2. If creating an individual connection (i.e., a Snowflake account only available to you), navigate to Workflows and create a new ETL workflow. When choosing your extractor task, choose the Extract data from Snowflake task.
3. Click Add Account if in the Admin tab, or Add a user account if in the workflow task. The remaining steps are the same regardless of where you’re setting up your account.
   
4. Name your account so you can identify it later.
   
5. Choose Authorization Code for the Grant Type.
6. Enter your authorization details:
   • Client ID: The OAUTH_CLIENT_ID you copied in step 10 of Setup in Snowflake.
   • Client secret: The CLIENT_SECRET you copied in step 14 of Setup in Snowflake.
   • Token endpoint: The OAUTH_TOKEN_ENDPOINT you copied in step 10 of Setup in Snowflake.
   • Authorization endpoint: The OAUTH_AUTHORIZATION_ENDPOINT you copied in step 10 of Setup in Snowflake.
   • Scopes (optional): You can use scopes to limit the level of access. For most use cases, you can leave this blank.
7. Click Connect Account.
8. A window will open for you to log into Snowflake. Enter your Snowflake Username and Password.
9. Click Sign in.

Setting up an Extract Data from Snowflake Task

1. Follow the linked instructions to create a new ETL workflow.
2. For your workflow task, choose the Extract data from Snowflake task.
3. Choose the Snowflake account you connected earlier.
   
4. Click Next.
5. Enter the following pieces of information about the data you want to import from Snowflake:
   
   • File name pattern: A text string used to identify the files to import.
     Qtip: We recommend including “Qualtrics” at the beginning of all files you want to import. You can then enter “Qualtrics” for this field to import all relevant files.
   • Pickup directory: The Snowflake directory that contains the files.
     Qtip: If the file is in the base stage, then you can leave this field blank.
   • Account identifier: The Snowflake account identifier of the database that contains the files to import.
   • Database identifier: The Snowflake database identifier of the database that contains the files to import.
   • Schema identifier: The Snowflake schema identifier of the schema that contains the file to import.
   • Stage name: The name of the Snowflake stage that contains the files to import.
6. If desired, enabled Skip bad files. When enabled, corrupted files and files that don’t match your headers are ignored.
7. You must upload a sample file that contains the general format of the data you want to import into Qualtrics. Click Choose file and select the file on your computer.
   Qtip: If needed, you can download one of the Sample files listed inside in the task window.
8. Click Upload.
9. Review your sample file to make sure everything looks correct.
   Qtip: If you need to change your file, click Back in the upper left corner.
10. Click Save.
11. Choose your loader task, which determines where the data is saved. See Available Loader Tasks for more information about the options available to you.