Skip to main content

Connect to Snowflake

Learn how to connect your Snowflake database to Retool.

You can connect to Snowflake and make it available as a resource in Retool. Once complete, your users can write queries that interact with Snowflake data.

Requirements and settings

The following requirements for a Snowflake resource depend on whether you are creating a resource on a cloud-hosted Retool organization or a self-hosted deployment.

Requirements for cloud-hosted Retool

The following requirements must be met to successfully create Snowflake resources.

RequirementDescription
Sufficient user permissions to create resources

All users for Retool organizations on Free or Team plans have global Edit permissions and can add, edit, and remove resources. If your organization manages user permissions for resources, you must be a member of a group with Edit all permissions.

Resource configuration settings

You must be able to provide the settings needed to create a resource. This may require you to perform actions, such as generating access credentials or creating a client application.

Resource authentication settings

You must have access to the data source and sufficient permissions to perform the actions needed, and be able to provide valid authentication settings.

Allow Retool access to the data source

If the data source is behind a firewall or restricts access based on IP address then you must ensure that your Retool organization can access it. If necessary, configure your data source to allow access from Retool's IP addresses.

Configuration settings for cloud-hosted Retool

Cloud-hosted Retool organizations support the following configuration settings.

SettingDescription
Database name

The name of the database to use.

Account ID

The ID for the account to use.

Schema

The default schema to use.

User role

The default user role for queries.

Warehouse

The default virtual warehouse to use.

Cloud-hosted Retool organizations can also optionally configure the following advanced options.

SettingDescription
Override default outbound Retool region

Specify a different outbound region from which Retool connects. This can improve performance if your resource is located in a different region to us-west-2.

Disable converting queries to prepared statements

Whether to disable SQL injection protection that allows dynamically generated SQL using JavaScript.

Show write GUI mode only

Whether to prevent users from writing raw SQL statements.

Fetch Number and Integer column types as BigInt

Whether to fetch these column types to allow higher precision of large numbers.

Authentication settings for cloud-hosted Retool

Cloud-hosted Retool organizations can authenticate with this resource using the following methods. You must be able to provide the necessary credentials for the method you wish to use.

Basic

Authentication is performed using Basic HTTP authentication with the provided credentials.

SettingDescription
Username

The username with which to authenticate.

Password

The password with which to authenticate

Key pair

Authentication is performed using RSA with the provided key-pair credentials.

SettingDescription
Username

The username.

Private key

The RSA private key

Private key passphrase

The passphrase for the private key.

OAuth 2.0

Authentication is performed using an OAuth 2.0 client application. You must create this application and then provide the details.

SettingDescription
Client IDThe client ID with which to authenticate.
Client secretThe client secret with which to authenticate.
Connect with OAuthClick to authenticate.

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.

Create a security integration in Snowflake

To use OAuth, you need a Client ID and Secret pair that's generated by creating an OAuth security integration in Snowflake for Retool. You must have the ACCOUNTADMIN role or a global CREATE INTEGRATION privileges.

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.

CREATE SECURITY INTEGRATION RETOOL TYPE = OAUTH ENABLED = TRUE OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' OAUTH_REDIRECT_URI = https://oauth.retool.com/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.

1. Create a resource

Sign in to your Retool organization and navigate to the Resources tab. Click Create new > Resource, then select Snowflake.

2. Configure the resource

Specify a name and location for your Snowflake resource. Retool displays the resource name and type in query editors to help users identify them. Next, provide the required information to create the resource. Depending on how your data source is configured, you may also need to provide optional settings for Retool to connect.

3. Test the connection

Click Test Connection to verify that Retool can successfully connect to Snowflake. If the test fails, check the resource settings and try again.

Testing only verifies connection

Testing a connection only checks whether Retool can successfully connect to the resource. It cannot check whether the provided credentials have sufficient privileges or can perform every supported action.

4. Save the resource

Click Create resource to complete the setup. You can then click either Create app to immediately start building a Retool app or Back to resources to return to the list of resources.

Wrap up

Your Snowflake resource is now ready to use. To start querying Snowflake data:

  1. Add a Resource query to an app or a Resource query block to a workflow.
  2. Select the new Snowflake resource from the resources dropdown.
  3. Write and run a query.