Configure Snowflake as a Source

Overview:

Snowflake can be used as a source for users who have high security demands and apprehensions regarding sharing credentials or providing access to the CRM data. You can integrate Snowflake as a source and set up workflows in which you can decide what tables/data you want to import into Revsure for ingestion.

Steps to Setup

1. Navigate to Revsure App Instance

2. Click "Data Hub"

3. Click "Data Sources"

4. In a new tab, navigate to https://app.snowflake.com/

5. Navigate to a SQL Worksheet and run the following command to add Revsure Integration
   
   US :
   CREATE SECURITY INTEGRATION REVSURE
   TYPE = OAUTH
   ENABLED = TRUE
   OAUTH_CLIENT = CUSTOM
   OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
   OAUTH_REDIRECT_URI = 'https://gw.revsure.cloud/data-hub/v1/oauth/callback'
   OAUTH_ISSUE_REFRESH_TOKENS = TRUE
   OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;
   
   EU :
   CREATE SECURITY INTEGRATION REVSURE
   TYPE = OAUTH
   ENABLED = TRUE
   OAUTH_CLIENT = CUSTOM
   OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
   OAUTH_REDIRECT_URI = 'https://gw.revsure-eu.cloud/data-hub/v1/oauth/callback'
   OAUTH_ISSUE_REFRESH_TOKENS = TRUE
   OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

8. Execute the Query

9. After Integration is created, run the following command
   SELECT
   PARSE_JSON(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('REVSURE')):OAUTH_CLIENT_ID::STRING AS client_id,
   PARSE_JSON(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('REVSURE')):OAUTH_CLIENT_SECRET::STRING AS client_secret;

10. Retrieve Client ID

11. Client Secret

12. Next, we also need the custom Instance URL for the connection.
    
    Execute the following query :
    
    SELECT
    'https://' || CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() || '.snowflakecomputing.com'
    AS custom_instance_url;

12. Back onto the Revsure App Data Hub, Click Add New

13. Select Snowflake from the list of Sources

14. Set the "Connection Name" field.

15. Fill your instance URL in the "Custom URL" field.

16. Set the "Warehouse" field.

17. From the Snowflake, use the retrieved information to set the "Client Id" and "Client Secret" fields.

18. Click "Connect"

19. This takes you to  "Sign in to Snowflake to authorize REVSURE"

20. Click here.

21. Use your credentials to authorize the same.

22. A message of  "Authorization successful" would be received

23. The Connection is created. The details can be seen on the Configuration Tab.
    Move to the Workflow tab to set up the workflows for the Extraction of Tables

24. Click "New Workflow"

25. Choose the Database Schema.

26. Select the Tables you wish to pull

27. Select the Columns of the table you wish to pull

28. Select the Primary Key (Essential for Delta Syncs)

29. Under Sync Setting, Full and Delta Options are Available.

30. If Delta is selected, a Date/Timestamp Column for Delta should be selected

31. Click on the Dropdown and choose the Column

32. Give the Workflow a Name.
    Set Historical Date from the Date Widget -  From where you want data to be pulled.
    Also Frequency for the time for Sync.

33. Type "Daily Workflow"

34. Click "Save"

35. At Save, there would be 2 Options - Sync Now and Sync Later.
    Sync Now - Triggers a Sync right away.
    Sync Later -  The next Sync would start at the Selected Frequency.

36. You can check the Status and Other Live information at the Workflow Summary now.