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.
The Virtual Private Snowflake Edition is not supported, as Salesforce must be able to reach a public endpoint.
If you use Network Policies in Snowflake, you must allowlist the Salesforce addresses for your region.
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:
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.
Create an External Credential (these are in a tab under Named Credentials). Use OAuth 2.0 browser flow and the above auth provider.
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.
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.
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.
Create a new, second Omnata connection to Snowflake (e.g. "Snowflake_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 ofRSA_PUBLIC_KEY
, e.g.:
Once the connection test is successful, refresh the page and select the new connection
Click the "Apply to another connection" button
Choose the original connection and click Apply
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 |
Last updated