Skip to main content

Snowflake

The Snowflake data source connects Data Hub to a Snowflake account using Snowflake's REST and SQL APIs. It authenticates with the custom OAuth authorization code flow and supports detecting deleted rows through Snowflake's change tracking.

Prerequisites​

Before adding a Snowflake data source, an account administrator needs to:

  • Create an OAuth security integration in Snowflake
  • Optionally enable change tracking on any tables that need deleted-row detection

We also recommend authorizing the connector as a dedicated read-only user rather than ACCOUNTADMIN. See Create a dedicated role and user below.

Create the OAuth security integration​

The connector uses Snowflake's custom OAuth authorization code flow. Sign in to Snowflake as ACCOUNTADMIN and run:

CREATE OR REPLACE SECURITY INTEGRATION ZAP_DATA_HUB
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
    OAUTH_REDIRECT_URI = 'https://services.zapbi.com/OAuthLoginRedirect'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_SINGLE_USE_REFRESH_TOKENS_REQUIRED = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 2592000;

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('ZAP_DATA_HUB');

The second statement returns the OAuth client ID and client secret needed when configuring the data source. Store the client secret somewhere safe, as Snowflake does not display it again.

tip

Set OAUTH_ENFORCE_PKCE = TRUE if your security policy requires PKCE.

important

By default, Snowflake blocks ACCOUNTADMIN, SECURITYADMIN, GLOBALORGADMIN, and ORGADMIN from authorizing OAuth integrations. To authorize Data Hub as one of these roles, an account administrator must remove it from the integration's BLOCKED_ROLES_LIST.

For full details on each parameter, see the Snowflake create security integration OAuth documentation.

Create a dedicated role and user​

We recommend authorizing the OAuth flow as a dedicated read-only user. The role only needs USAGE on the target warehouse and database, and SELECT on the tables and views that should be available to Data Hub.

See the Snowflake documentation for creating roles, granting privileges, and creating users.

important

If the user is created with a temporary password, sign in to Snowflake once as that user to clear it before authorizing the OAuth flow. Snowflake blocks OAuth sign-in while a forced password change is pending.

Connection settings​

Define the following connection settings for a Snowflake data source:

  • Url - The account URL of your Snowflake instance, such as https://abcd-xy12345.snowflakecomputing.com/.
  • OAuth client ID - The client ID returned by SYSTEM$SHOW_OAUTH_CLIENT_SECRETS for the security integration.
  • OAuth client secret - The client secret returned by SYSTEM$SHOW_OAUTH_CLIENT_SECRETS for the security integration.
  • Warehouse - The Snowflake warehouse used to run queries. Choose from the list of warehouses the authorized user has access to.
  • Database - The Snowflake database to load tables and views from. Choose from the list of databases the authorized user has access to.

After entering the Url, OAuth client ID, and OAuth client secret, sign in through the Snowflake authorization prompt. Once authorized, the Warehouse and Database dropdowns populate with the values available to the authenticated user.

Detect deleted rows​

The Snowflake connector can identify rows that have been deleted from a source table since the last load by reading Snowflake's change tracking metadata. To use this capability, enable change tracking on each source table:

ALTER TABLE <schema>.<table> SET CHANGE_TRACKING = TRUE;
important

Snowflake only records changes from the moment change tracking is enabled. Rows that already existed when change tracking was turned on are not retroactively tracked, and rows deleted before that point are not detected.