Snowflake

Snowflake is an advanced data platform provided as Software-as-a-Service (SaaS).

Snowflake Account Prerequisites

You must have a Snowflake account, preferably in the same region as your Salesforce account.

  1. The Virtual Private Snowflake Edition is not supported, as Salesforce must be able to reach a public endpoint.

  2. If you use Network Policies in Snowflake, you must allowlist the Salesforce addresses for your region.

  3. You must create a Snowflake user which belongs to a role that can read the tables/views.

Authentication Methods

When using the original (not publicly documented) Snowflake SQL API, the following are supported:

  • Username & Password

  • OAuth

When using the newer official Snowflake SQL API, the following are supported:

  • JWT (certificate based)

  • OAuth

  • Custom Named Credentials (Public Preview, see section below)

Note on OAuth tokens

By default, Snowflake OAuth refresh tokens only last 90 days, which means the OAuth connection between Salesforce and Snowflake would need to be manually re-authenticated every 90 days, which is generally considered impractical.

To increase this to, for example, 5 years, you must log a ticket with Snowflake support and ask that the maximum OAUTH_REFRESH_TOKEN_VALIDITY for a SECURITY INTEGRATION be raised to 157784630 seconds.

Using Custom Named Credentials

The OAuth configuration process Omnata offers assumes that Snowflake is both the Authorization Server and the Resource Server, and that an Authorization Code flow is used to obtain a refresh token.

Some users may have a more complex/custom OAuth configuration, so we offer a way for users to nominate their own Named Credentials to apply the Authorization header to outbound requests (noting the Snowflake requirements here).

The process is:

  1. Create an Auth Provider. The standard OpenID Connect type should work for most Authorization Code flows. For a Client Credentials flow, Omnata offers a custom auth provider implementation.

  2. Create an External Credential (these are in a tab under Named Credentials). Use OAuth 2.0 browser flow and the above auth provider.

  3. Create a Legacy Named credential which includes the url for your Snowflake account, linked to the above External Credential and with the "Generate Authorization Header" option enabled. Ensure it all connects successfully.

  4. In the Omnata UI, when creating a Snowflake connection, ensure "New Snowflake API" is ticked, and choose "Custom Named Credential" as the auth method:

    The name of the Omnata Connection will need to match the name of the Named Credential from step 3, the UI will warn if it doesn't find a match.

  5. After you save the connection, it will test auth to Snowflake and let you know if it works. Once successful, you can continue on the Omnata Connect tab.

How to cycle certificates when using JWT

The generated certificate used for JWT will eventually expire, so there is a process to cycle in the new certificate.

  1. Create a new, second Omnata connection to Snowflake (e.g. "Snowflake_2")

  2. Configure it with JWT, except when you get to the step where it asks you to alter the user, set the RSA_PUBLIC_KEY_2 parameter instead of RSA_PUBLIC_KEY, e.g.:

alter user my_user set RSA_PUBLIC_KEY_2='........';

  1. Once the connection test is successful, refresh the page and select the new connection

  2. Click the "Apply to another connection" button

  3. Choose the original connection and click Apply

  4. The original connection will be replaced with the new connection, preserving all of the original settings and external objects

Supported Data Types

The following table describes the current mapping between Snowflake data types and Salesforce.

Snowflake
Corresponding Salesforce type

NUMBER

NUMBER_TYPE if the precision is < 18, otherwise STRING_SHORT_TYPE

FLOAT

Floating point numbers are not supported by Apex, but the adapter converts them to STRING_SHORT_TYPE to allow them to be displayed.

For proper treatment as a number, the Snowflake type NUMBER should be used instead.

VARCHAR

STRING_SHORT_TYPE if the max length is <255, otherwise STRING_LONG_TYPE

BINARY

Not supported

BOOLEAN

NUMBER_TYPE

(not BOOLEAN_TYPE, because NULL values wouldn't be supported)

DATE

Dates are not supported by Salesforce Connect, but the adapter converts to DATETIME_TYPE (at midnight) for convenience.

This makes them usable but potentially misleading.

TIMESTAMP

DATETIME_TYPE

TIME

Not supported

VARIANT

Not supported

OBJECT

Not supported

ARRAY

Not supported

PreviousConnect to your data warehouseNextBigQuery

Last updated