Snowflake

Access configuration details to connect Soda to a Snowflake data source.

Connection configuration reference

Install the following package:

pip install -i https://pypi.dev.sodadata.io/simple -U soda-snowflake

Core connection:

type: snowflake
name: my_datasource_name
connection:
  # Core
  account: ${env.SNOWFLAKE_ACCOUNT} # <account locater>.<region>, e.g.,ET23172.eu-west-1
  database: <database>
  warehouse: <warehouse>
  role: <role>                            # optional, strongly recommended
  connection_timeout: 240                 # optional
  client_session_keep_alive: false        # optional

Though optional, best practice dictates that you provide a value for role. If you do not provide a role, and Snowflake has not assigned a Snowflake System-Defined Role to the user account, Snowflake may, confusingly, deny access to the data source.

Core connection properties

Property

Required

Type

Description

type

yes

string

Identify the type of data source for Soda. In this case, must be snowflake.

account

yes

string

Provide the unique value that identifies your account. Consider using system variables to retrieve this value securely using, for example, ${SNOWFLAKE_ACCOUNT}. Note: Account sometimes needs to take the form of <account_identifier>-<account_name> or <account_identifier>.<region>.

database

yes

string

Provide an identifier for your database.

warehouse

yes

string

Provide an identifier for the cluster of resources that is a Snowflake virtual warehouse. See Overview of Warehouses.

role¹

recommended

string

Specify a Snowflake role that has permission to access the database and schema of your data source.

connection_timeout

integer

Set the timeout period in seconds for an inactive login session.

client_session_keep_alive

boolean

Use this parameter to keep the session active, even with no user activity. Default value: false.

session_parameters

object

Pass-through Snowflake session params (e.g., QUERY_TAG, QUOTED_IDENTIFIERS_IGNORE_CASE).

proxy_http

string

HTTP proxy (agent environments); see Troubleshoot section.

proxy_https

string

HTTPS proxy (agent environments); see Troubleshoot section.

¹ Though optional, best practice dictates that you provide a value for role. If you do not provide a role, and Snowflake has not assigned a Snowflake System-Defined Role to the user account, Snowflake may, confusingly, deny access to the data source.

Authenticator selector

Property

Required

Values / Example

Description

authenticator²

externalbrowser | OAUTH_CLIENT_CREDENTIALS

Add an authenticator paramater with value externalbrowser to authenticate the connection to your Snowflake data source using any SAML 2.0-compliant identity provider (IdP) such as

Okta or OneLogin.

If omitted, Soda uses user/password auth (default)

² Use this parameter when adding Snowflake connection configurations to a configuration.yml file. However, if you are adding connection configuration details directly in Soda Cloud (connecting to your Snowflake data source via a Soda Agent) to authenticate using Okta, you must follow the instructions documented by Snowflake for Native SSO - Okta Only.

Choose exactly one authentication approach of the four options below.

Supported by Soda

Default (SnowflakePasswordAuth)
External browser (SnowflakeSSOAuth)
key_pair (SnowflakeKeyPairAuth)
OAuth (SnowflakeOAuthAuth)
OAuth client credentials (SnowflakeClientCredentialsOAuthAuth)

Not supported by Soda

OAuth Authorization Code grant
User/password MFA flows
Workload identity authenticator
Programmatic access token (PAT)
Programmatic access token with external session

Notes:

Use one authentication method per connection.
While role is technically optional, providing it avoids confusing access errors.
Private key auth can use inline private_key (PEM) or private_key_path.
externalbrowser SSO uses your SAML 2.0 IdP (e.g., Okta/OneLogin).
Proxy parameters are supported when connecting via an agent behind a proxy.

1. User + password

  # 1) User + password
  user: ${env.SNOWFLAKE_USER}
  password: ${env.SNOWFLAKE_PASSWORD}

User/password properties (when no authenticator is set)

Property

Required

Description

user

yes

Snowflake user login. Consider using system variables to retrieve this value securely using, for example, ${SNOWFLAKE_USER}.

password

yes

Password for the user. Consider using system variables to retrieve this value securely using, for example, ${SNOWFLAKE_PASSWORD}.

2. External browser SSO

  # 2) External browser SSO (SAML 2.0 IdP such as Okta/OneLogin)
  authenticator: externalbrowser

External browser SSO properties (when authenticator: externalbrowser)

Property

Required

Description

user

Depending on IdP settings, may be required. Consider using system variables to retrieve this value securely using, for example, ${SNOWFLAKE_USER}.

authenticator

yes

Must be externalbrowser to enable SAML 2.0 browser-based SSO.

3. Key pair (JWT) authentication

You can use the private_key and private_key_passphrase parameters to specify for key pair authentication. In you configuration YML file, add the parameters as per the following example.

  # 3) Key pair (JWT) authentication
  authenticator: SNOWFLAKE_JWT
  user: <user>
  private_key: |
    -----BEGIN ENCRYPTED PRIVATE KEY-----
    -----END ENCRYPTED PRIVATE KEY-----
  private_key_passphrase: ${env.SNOWFLAKE_PASSPHRASE}
  # or use a file path instead of inline key:
  private_key_path: /path/to/private-key.pk8

Key pair / JWT properties (when authenticator: SNOWFLAKE_JWT)

Property

Required

Description

user

yes

Snowflake user that owns the key pair.

private_key

yes*

Inline PEM private key. Use either this or private_key_path.

private_key_path

yes*

Path to private key file (.pk8). Use either this or private_key.

private_key_passphrase

Passphrase for encrypted private key.

Do not include password when authenticator: SNOWFLAKE_JWT is used.

4. OAuth 2.0 Client Credentials

  # 4) OAuth 2.0 Client Credentials
  authenticator: OAUTH_CLIENT_CREDENTIALS
  oauth_client_id: <client-id>
  oauth_client_secret: <client-secret>
  oauth_token_request_url: https://<idp>/oauth/token
  oauth_scope: "scope1 scope2"        # space-delimited; optional if derived by role

OAuth 2.0 client properties (when authenticator: OAUTH_CLIENT_CREDENTIALS)

Property

Required

Description

oauth_client_id

yes

Client ID from the IdP for the Snowflake security integration.

oauth_client_secret

yes

Client secret from the IdP for the Snowflake security integration.

oauth_token_request_url

yes

IdP token endpoint that issues access tokens to the driver. (With Snowflake as IdP, derive from server/account parameters.)

oauth_scope

Space-delimited, case-sensitive scopes. Defaults may be derived from role; specify explicitly for multiple/custom scopes.

Only the Client Credentials grant is supported. The Authorization Code grant is not supported.

Other parameters

  # Optional Snowflake session parameters (passed through)
  session_parameters:
    QUERY_TAG: soda-queries
    QUOTED_IDENTIFIERS_IGNORE_CASE: false

  # Optional proxies (for agents behind proxy)
  proxy_http: http://host:port
  proxy_https: https://host:port

Other parameters properties

Property

Required

Notes

other params

optional

You can pass any other Snowflake parameters you wish by adding the key:value pairs to your Snowflake connection configuration. See Snowflake Python Connector API documentation for a list of passable parameters.

QUERY_TAG

optional

See QUERY_TAG in Snowflake documentation.

QUOTED_IDENTIFIERS_ IGNORE_CASE

optional

See QUOTED_IDENTIFIERS_IGNORE_CASE in Snowflake documentation.

Use a values file to store private key authentication values

If you use a private key authentication with Snowflake and have deployed a Soda Agent, you can provide the required private key values in a values.yml file when you deploy or redeploy the agent.

You can also use the values.yml file to store other environment variables for the Soda Agent to use, such as SNOWFLAKE_USER, SNOWFLAKE_ACCOUNT, SNOWFLAKE_PASSPHRASE, etc.

First, run the following command to create a local path to the Snowflake private key. Replace the local path to the Snowflake private key with your own value.

kubectl create secret generic -n <soda-agent-namespace> snowflake-private-key --from-file=snowflake-private-key.pk8=<local path to the Snowflake private key>

Then, add the following to the your values.yml file, adjusting the values to your own specific details.

soda:
  scanLauncher:
    volumeMounts:
      - name: snowflake-private-key
        mountPath: /opt/soda/etc
    volumes:
      - name: snowflake-private-key
        secret:
          secretName: snowflake-private-key
          items:
            - key: snowflake-private-key.pk8
              path: snowflake-private-key.pk8
  contractLauncher:
    volumeMounts:
      - name: snowflake-private-key
        mountPath: /opt/soda/etc

Adjust the configuration.yml file to include the new path in the connection details, as in the following example.

data_source ltsnowflakecustomer:
  type: snowflake
  account: ${env.SNOWFLAKE_ACCOUNT}
  database: PUBLISH_DEV
  warehouse: ${env.SNOWFLAKE_WAREHOUSE}
  role: ${env.SNOWFLAKE_ROLE}
  user: ${env.SNOWFLAKE_USER}
  authenticator: SNOWFLAKE_JWT
  private_key_passphrase: ${env.SNOWFLAKE_PASSPHRASE}
  private_key_path: /opt/soda/etc/snowflake-private-key.pk8
  client_session_keep_alive: true
  session_parameters:
    QUERY_TAG: soda-queries
    QUOTED_IDENTIFIERS_IGNORE_CASE: false

Deploy, or redeploy, the agent for the changes to take effect.

Connection test

Test the data source connection:

soda data-source test -ds ds.yml

Supported data types

Troubleshoot

Problem: When testing the connection to your Snowflake data source, Snowflake returns an error message about using the use database command.

Solution: Use the role parameter to specify a Snowflake role that has permission to access the database and schema of your data source. Though optional, best practice dictates that you provide a value for role. If you do not provide a role, and Snowflake has not assigned a Snowflake System-Defined Role to the user account, Snowflake may, confusingly, deny access to the data source.

Problem: When Soda attempts to connect to your Snowflake data source, it produces a connectivity error that includes something like RuntimeError: Command failed with exit code 2: ..... ocsp_response_validation_cash.lock.

Solution: Use Snowflake's troubleshooting guide to triage OCSP-related connectivity issues.

Problem: You have defined a Group By check and the scan that executes the check yields an error.

Solution: Be aware that, by default, Snowflake returns columns names in uppercase. Therefore, when defining a Group By check, you must specify the custom name of the check in uppercase as in the following example.

checks for VOLUME:
  - group by:
      group_limit: 50
      query: |
        SELECT TRADER, count(*) as trader_row_count
        FROM TRADEVOLUME
        WHERE TRADE_DATE = '2023-01-01'
        GROUP BY TRADER
      fields:
        - TRADER
      checks:
        - TRADER_ROW_COUNT > 40:
            name: Trader row count

Problem: While attempting to connect Soda to a Snowflake data source using proxy parameters, you encounter an error that reads something similar to Could not connect to data source "name_db": 250001 (08001): Failed to connect to DB: mydb.eu-west-1.snowflakecomputing.com:443. Incoming request with IP/Token xx.xxx.xx.xxx is not allowed to access Snowflake.

data_source my_data_source:
  type: snowflake
  ...
  session_parameters:
    QUERY_TAG: soda-test
    QUOTED_IDENTIFIERS_IGNORE_CASE: false
  proxy_http: http://a-proxy-o-dd-dddd-net:8000
  proxy_https: https://a-proxy-o-dd-dddd-net:8000

Solution: When connecting to a Snowflake data source by proxy, be sure to set the new proxy environment variables from the command-line using export statements, as in the following example.

export HTTP_PROXY=http://a-proxy-o-dd-dddd-net:8000
export HTTPS_PROXY=https://a-proxy-o-dd-dddd-net:8000

You are not logged in to Soda and are viewing the default public documentation. Learn more about Documentation access & licensing.

PreviousSQL Server NextSynapse

Last updated 1 month ago

Was this helpful?