Snowflake Connector: Setup + FAQs

Where do I begin?

STEP 1: CUSTOM FIELD CREATION

Custom fields are required to be created in Snowflake for the Vertify integration. Please create these fields on the appropriate table(s) or object(s) (i.e., Leads, Contacts, and Accounts). Reference the API Name column in the table below for the list of how to title the fields, the needed data type, and the purpose of the field for context. 

STEP 2: SNOWFLAKE CREDENTIAL CREATION

It is preferred that users are created with an Administrator role to ensure that no permissions errors are encountered during the implementation.

1. Log into Snowflake as an ACCOUNTADMIN
2. Action Item: Please note the organization_name, account_name and account_locator that will be generated in this query in a separate document, such as text editor or notepad. You will need these when connecting with Vertify in Step 3 below.
   • Execute query:

use role orgadmin; 

show organization accounts;

3. Action Item: Please note the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET that will be generated in this query in your open document. You will also need these when connecting with Vertify in Step 3 below.

This will create OAUTH refresh tokens that expire after the maximum of 90 days. This means a Vertify user would need to disconnect and reconnect to Snowflake once every 3 months. You can request via Snowflake Support to change the refresh token timeout for your Snowflake environment. For example, to request a timeout of 5 years, request the maximum OAUTH_REFRESH_TOKEN_VALIDITY for a SECURITY INTEGRATION to be increased to 157784630 seconds. 

• Execute query:

CREATE SECURITY INTEGRATION OAUTH_VERTIFY_INTEGRATION

TYPE = OAUTH

ENABLED = TRUE

OAUTH_CLIENT = CUSTOM

OAUTH_CLIENT_TYPE = ‘CONFIDENTIAL’

OAUTH_REDIRECT_URI = ‘https://oauth-connect.vertify.com/snowflake’

OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

Select system$show_oauth_client_secrets (‘OAUTH_VERTIFY_INTEGRATION’);

Caveats:

• Tables must have a primary key defined or they will not be found
• Views support has not yet been added (current as of Aug 2023)
• Primary Keys are not enforced as unique in Snowflake; therefore, it is possible that an UPDATE will update more than one row. 
• Snowflake does not support retrieving the values of primary keys for inserted rows; therefore, we only support inserts and updates if the primary key fields are part of the mapping.

Please note, the following are unsupported Datatypes in Snowflake: 

• VARIANT
• GEOMETRY
• GEOGRAPHY

STEP 3: VERTIFY SYSTEM SETUP

Refer to the steps outlined below to set up the system in Vertify. Alternatively, you’re welcome to schedule a working session with your Vertify Integration Specialist for assistance.

1. In Vertify, navigate to the Connect page
2. Click the New System button -

3. Select Snowflake from the Remote System dropdown
4. Wait for other parameters to auto-populate, and then select the most recent connector version
   • Note: System Name and Prefix must be uniquely named if more than one connection to that System exists in your Workspace
5. Enter the required system credentials from the text document you had open in Step 2 above- 
   • Account [Required]
   • Client Id [Required]
   • Client Secret [Required]
   • Database [Required]
   • Locator [Required]
   • Organization [Required]
   • Warehouse [Required]

6. Click Connect to Snowflake
7. Press Save
8. System will then scan automatically, and the response will be returned either successful or an error message with the authentication issue. Please reach out to your specialist for additional assistance as needed.