Snowflake
How to connect Snowflake to Count.
Last updated
How to connect Snowflake to Count.
Last updated
To enable Count to connect to your Snowflake database, you'll need your full Snowflake account name.
Note that your account name may have additional segments that identify the region and cloud platform that hosts your account.
For example:
xy12345
xy12345.eu-west-1
xy12345.eu-west-2.aws
xy12345.us-central1.gcp
xy12345.switzerland-north.azure
You can see your full account name when logged into Snowflake - it forms the first part of the URL.
For example, if your account name is
xy12345.eu-west-2.aws
then the URL will look like
https://xy12345.eu-west-2.aws.snowflakecomputing.com/...
This account also must be permitted to read the INFORMATION_SCHEMA table within this database.
To connect using username and password, select this option from the Authentication options dropdown, enter the required values and click Connect.
To connect using OAuth, you must first create a Snowflake Security Integration.
Navigate to the Snowflake UI console (or other Snowflake client)
Create a new SQL worksheet
Create the Snowflake Security Integration by running the following SQL command
CREATE SECURITY INTEGRATION COUNT_OAUTH
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://count.co/connection-oauth'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 2592000
OAUTH_ENFORCE_PKCE = TRUE
BLOCKED_ROLES_LIST = ('SYSADMIN');
Notes:
OAUTH_ISSUE_REFRESH_TOKENS
(in seconds) is set to 30 days in the above statement. Every 30 days, Count's connection will need to be re-authorized (see below) to re-enable to the connection to Snowflake. Count recommends a value between 1 week (604800) and 90 days (7776000) to avoid the need to re-authorize too often.
Once created, retrieve the OAUTH_CLIENT_ID
and OAUTH_CLIENT_SECRET
parameters using the following statement
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('COUNT_OAUTH');
Navigate to your Count workspace, click Connect a database, and choose Snowflake.
Select OAuth in the Authentication options dropdown, and enter your Account, the value obtained in step 4 for the OAUTH_CLIENT_ID
.
When you click Authorize, you will be redirected to your usual Snowflake login flow. Please not that certain roles are blocked from authorizing using this flow. Please contact Snowflake support should you have further questions.
Once you have logged successfully authorized on the Snowflake side, you will be automatically redirected back to your Connect to Snowflake page in Count. You'll now be asked for the value of the OAUTH_CLIENT_SECRET
obtained in step 4, your Database name, and any other optional properties you wish to configure, including the Refresh token validity (see below). Click Connect to complete the connection.
If an error is encountered during step 8, you will need to re-authorize once again (steps 6 - 8).
Re-authorization
The refresh token obtained by Count from Snowflake during step 8 enables Count to obtain a short lived (10 minutes) session token from Snowflake to, amongst other things, run queries against your database. This refresh token itself has an expiry given by the value of OAUTH_REFRESH_TOKEN_VALIDITY
set above. The connection will therefore need to be re-authorized whenever it is due to expire.
Navigate to your Snowflake connection settings page, and click Re-authorize to regenerate a new refresh token (steps 6 - 8 above).
Once you have connected you will see your connection details including a full list of tables accessible with this connection.
By default, you are able to query any table in the Snowflake database (limited by the supplied credentials). You are also able to query tables in other databases as long as the credentials have access to those databases, though they do not appear in the Count UI.
If table access is restricted within a Count project, then canvases in that project will be unable to query tables outside of this Snowflake database.
The Snowflake connection in Count supports a number of advanced settings:
Max concurrent connections - Set the maximum number of simultaneous connections between Count and your database. If your database has a lower limit than the value set here, then the lower limit will take precedence.
Query timeout - queries executed from Count will be automatically aborted after this length of time.
The most common issue when connecting to Snowflake is getting the account name correct. You can find more information about your account name in the Snowflake documentation.
If you have a network policy defined which whitelists connections based on IP address, you may need to allow connections to the following IP addresses for Count to establish a connection:
34.107.75.117
35.198.189.90
35.234.110.177
104.155.13.208
What do I do if we have MFA enabled?
You'll need to authenticate using OAuth.