Skip to main content

Connect to Snowflake

Retool's Snowflake integration provides a convenient UI for connecting to and interacting with your Snowflake databases.

Connecting to Snowflake​

First, create a new resource to connect to your Snowflake instance. Resources sit on top of our integrations and store the authentication and metadata fields required for you to connect to your data source.

To get started, log in to Retool and select the Resources tab. Click Create new and then select Snowflake as your resource type. Give your resource a name, enter your account identifier and database information, then select an authentication method.

note

Refer to Snowflake's documentation to learn how to find your account identifier. There are two account identifier formats, and the one you use depends on when you created your account.

Retool currently supports 3 methods of authenticating to Snowflake:

  • Basic authentication: Password-based authentication with credentials shared across users.
  • Key Pair authentication: Key based authentication with credentials shared across users. Offers enhanced authentication security as an alternative to basic authentication.
  • OAuth: OAuth based authentication with individual user credentials. Each user is prompted to authenticate with Snowflake via an OAuth authentication flow.
caution

To access your Snowflake resource, you must also:

Key Pair authentication​

To use Snowflake's Key Pair Authentication, you must first generate a private key that Retool can use.

Once you have a private key, select Key Pair as the authentication method for your new resource. Enter the username the public key is assigned to and the private key you created in the previous step. If you encrypted your private key, provide the encryption passcode.

Click Test Connection to confirm your credentials are correct.

Per-user OAuth​

Connecting to Snowflake with OAuth allows database administrators to:

  • Audit Retool users who run queries against the database.
  • Enforce role-based access controls using Snowflake permissions.
  • Use OAuth tokens for all processes and actions that access Snowflake, instead of embedding Snowflake IDs and passwords in multiple places
  • Revoke authorization for a given user through Snowflake

With Snowflake connections that use OAuth, users must log in again periodically when their OAuth tokens expire. The duration of validity for Snowflake OAuth tokens is set through Snowflake itself.

To use OAuth, you need a Client ID and Secret pair that's generated by creating a security integration in Snowflake for Retool.

Create a security integration in Snowflake​

note

Creating a SECURITY_INTEGRATION in Snowflake requires the ACCOUNTADMIN role or a global CREATE INTEGRATION privilege

First, create a security integration in Snowflake—this creates an interface between Snowflake and Retool using OAuth. Run the following command in the Snowflake UI, replacing OAUTH_REDIRECT URI with the OAuth callback URL value provided when setting up the resource. The value of <your-retool-domain> depends on whether you use Retool Cloud or deploy Retool on-premise:

  • Retool Cloud: Use oauth.retool.com
  • Retool on-premise: Use the hostname of your Retool instance
CREATE SECURITY INTEGRATION RETOOL
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = '<your-retool-domain>/oauth/user/oauthcallback'

Next, retrieve the OAuth Client ID and Client Secret for the security integration you just created by running the following command in the Snowflake UI:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('RETOOL');

You should see the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET in the response of this query.

Configuring OAuth in Retool​

Select OAuth as the authentication method and input the Client ID and Client Secret for the security integration you just created.

Click Test OAuth integration with your own account. You should see the browser open up a Snowflake login page. After logging in, the page should successfully redirect back to the resource.

Query Snowflake

You can now select your newly-created Snowflake resource from the Resource dropdown when creating queries in your Retool apps. You can toggle between SQL mode for raw SQL statements or the GUI mode for structured writes.

note

You need to wrap schema column names that contain lowercase letters with quotes (e.g., "id").

You can display the results of Snowflake queries as with any other query in Retool: