Connection types
You can use Snowflake in two ways in Prophecy.Feature support
The following table shows which Prophecy features each connection type supports.Prerequisites
Prophecy runs all data operations, such as reads and writes, with the Snowflake credentials you provide in the connection. Grant your Snowflake user or role the privileges for the operations your pipelines perform.
When you write data through a Snowflake connection, Prophecy uploads Parquet files to your user stage and then loads them into Snowflake tables. This is why write operations require file format and stage access.
Connection parameters
To create a connection with Snowflake, enter the following parameters:Key-pair authentication
Key-pair authentication signs in to Snowflake with an RSA private key that you store in a Prophecy secret. Before you begin, assign the public key to the Snowflake user that the connection uses. The private key must be an unencryptedPKCS#8 key. If your key is encrypted, convert it with OpenSSL.
To configure key-pair authentication:
- In the fabric, create a Text secret. Click Or Upload File and upload the private key file. Upload the file instead of pasting the key into Value. Pasting can remove the line breaks the key requires.
- In the connection, under Authentication method, select Key Pair.
- Enter the Username of the Snowflake user that has the public key assigned.
- For Private Key, select the secret you created.
- Leave Private Key Passphrase (Optional) empty.
Key-pair authentication doesn’t support passphrase-protected private keys. Use an unencrypted private key.
OIDC authentication
OIDC authentication signs in to Snowflake as a service principal whose credentials are issued by your identity provider. Prophecy exchanges these credentials for a bearer token through an OIDC federator, caches the token, and reconnects with a new token before the old one expires. OIDC doesn’t require an app registration or an interactive sign-in. Unlike key-pair authentication, the certificate and private key come from your identity provider, not from a key pair registered on a Snowflake user. The connection doesn’t take a username. Instead, Snowflake maps a claim in the token to a Snowflake user through an External OAuth security integration, which must exist before you create the connection. To configure OIDC authentication:- In the fabric, create secrets for the service principal’s certificate and private key, and for its client secret if you have one.
- In the connection, under Authentication method, select OIDC.
- Enter the Auth Server URL of the token issuer.
- Enter the Resource that the token is requested for.
- Enter the Service Principal Client ID.
- Optionally, for Service Principal Client Secret, select the client secret.
- For Certificate, select the certificate secret.
- For Private Key, select the private key secret.
OIDC authentication requires an OIDC federator URL in the Prophecy deployment configuration. If the federator URL isn’t configured, connections that use OIDC fail with the error
OIDC federator URL is not configured. Contact your Prophecy administrator to configure it.Data type mapping
When an Ingress/Egress connection reads Snowflake data into pipelines that run on another SQL warehouse, such as Databricks or BigQuery, Prophecy converts Snowflake-specific data types to compatible types. The following table shows how Snowflake data types map to Databricks and BigQuery types.Learn more in Supported data types.
Limitations
Snowflake connections have the following data type limitations:- Prophecy reads
OBJECT,ARRAY, andVARIANTcolumns asSTRING. - Prophecy does not support writing
BINARYcolumns. - On Databricks and BigQuery, Prophecy maps
DECIMALandNUMERICto floating-point types, which can reduce precision. Avoid this conversion for values that require exact precision, such as currency. - On Databricks and BigQuery, Prophecy maps
NUMBERto an integer type, so values with a decimal scale lose their fractional digits. - On Databricks, Prophecy maps
TIMEtoSTRING.

