Snowflake Connector

Query information from Snowflake to use with Tulip apps

Audience

Please note, the Snowflake integration is technical and may require your IT support to create the appropriate objects, roles and permissions.

Support Contact

This connector was created by collaboration with Spaulding Ridge. for more information or questions, please reach out to Spaulding Ridge at info@spauldingridge.com

Purpose

The Snowflake connector streamlines how to query information from a Snowflake Data Cloud. Snowflake SQL REST API documentation can be found here.

Connector Notes

The Snowflake connector can manipulate single records relatively easily. Custom widgets may be necessary for querying multiple rows of data or manipulating outputs in Tulip (intercative table, etc.).

Snowflake OAuth uses Snowflake’s built-in OAuth service, and supports Tulip as a custom client. Snowflake OAuth documentation can be found here. Custom client configuration details can be found here.

Setup

This section provides the procedure for configuring an OAuth token from Snowflake's OAuth server to establish connectivity with Tulip as a client, as well as the steps to set up the Snowflake connector in Tulip.

Step 1: Register Tulip as a client application in Snowflake

Log in to your Snowflake instance with a user that has the role/priviledge to create and manage Security Integrations (ACCOUNTADMIN).

Create a new SQL worksheet in the COMPUTE_WH warehouse.

Run the following script to create a Security Integration of type OAuth. Do not forget to update values for INTEGRATION_NAME, TULIP_INSTANCE_NAME, and TIME_IN_SECONDS.

CREATE OR REPLACE SECURITY INTEGRATION <INTEGRATION_NAME>
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://<TULIP_INSTANCE_NAME>.tulip.co/oauth-redirect-handler'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_ISSUE_REFRESH_TOKEN_VALIDITY = <TIME_IN_SECONDS>;

Note: the Refresh Token validity can be as long as 90 days (7776000 seconds).

Step 2: Gather details for the OAuth configuration in Tulip

Run the following commands to describe the Security Integration you just created and note down the below details:
OAUTH_CLIENT_ID
OAUTH_REDIRECT_URI
OAUTH_AUTHORIZATION_ENDPOINT
OAUTH_TOKEN_ENDPOINT
OAUTH_CLIENT_SECRET

DESCRIBE SECURITY INTEGRATION <INTEGRATION_NAME>;
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<INTEGRATION_NAME>');

Step 3: Configure your Snowflake connector

Log into Tulip and add the Snowflake Connector from the Tulip Library (Web or Embedded).

Click the “Production & Testing & Development” button to edit the connection details as below. You can find your SNOWFLAKE_ACCOUNT_NAME in the OAUTH_AUTHORIZATION_ENDPOINT or OAUTH_TOKEN_ENDPOINT from Step 2.

Click “Test” and “Save”. The connector will go online.

Step 4: Edit your Snowflake connector’s authentification details

Click the “Production & Testing & Development” button, and the “Edit Headers” button.

Choose the authentication type as either "OAuth 2 (Admin)" or "OAuth 2 (Operator)" based on your requirements. Further information on this can be found in the Authentication section of the How to Configure a Connector article.

Edit the authentication fields, using the details from Step 2:
Authorization Code URL = OAUTH_AUTHORIZATION_ENDPOINT
Access Token URL = OAUTH_TOKEN_ENDPOINT
Client ID = OAUTH_CLIENT_ID
Client Secret = OAUTH_CLIENT_SECRET

Click “Test”. You will be taken to the Login Window of your Snowflake account.

A user must login. Note that its default role must not be ACCOUNTADMIN, SECURITYADMIN, or ORGADMIN. You can alter users’s default role for OAuth, as detailed in this Snowflake resource: Managing user consent for OAuth.

After successful authentication, a message will appear for consent. Please read it carefully to understand the operation that is being performed and click “Allow”.
You are now able to connect securely to Snowflake from Tulip!

Usage

You have two options to begin using the connector:

Start by customizing the connector template functions to suit your specific requirements.

Follow the Snowflake Unit Test instructions for a practical demonstration of how to use the connector for tasks including: retrieving all table records from a Snowflake Table in Tulip, creating a new record, updating an existing record, and deleting a record.

Template Functions

The Library connector includes the following template functions:
Template: Query Database
Description: Allows you to submit one or more SQL statements for execution.
Method: POST
Endpoint: /api/v2/statements
Reference: https://docs.snowflake.com/en/developer-guide/sql-api/reference#post-api-v2-statements
Template: Check Query Status
Description: Enables you to check the status of the execution of a statement.
Method: GET
Endpoint: /api/v2/statements/{statementHandle}
Reference: https://docs.snowflake.com/en/developer-guide/sql-api/reference#get-api-v2-statements-statementhandle
Template: Cancel Query
Description: Allows you to cancel the execution of a statement.
Method: POST
Endpoint: /api/v2/statements/{statementHandle}/cancel
Reference: https://docs.snowflake.com/en/developer-guide/sql-api/reference#post-api-v2-statements-statementhandle-cancel

Additional References

This Snowflake Community guide also provides additional context to the Snowflake integration