Connections for INFACore > Connections to source and target endpoints > Snowflake

Snowflake

Create a Snowflake connection to read from or write to Snowflake tables and views. You can also read from Snowflake external tables, hybrid tables, and materialized views.

Feature snapshot

Operation	Support
Read	Yes
Write	Yes

Before you begin

Before you configure the connection properties, you'll need to get information from your Snowflake account.

The following video shows you how to get information from your Snowflake account:

You can use the following authentication methods to connect to Snowflake:

•Standard. Uses Snowflake account user name and password credentials to connect to Snowflake.
•Authorization Code. Uses the OAuth 2.0 protocol with Authorization Code grant type to connect to Snowflake. Authorization Code allows authorized access to Snowflake without sharing or storing your login credentials.
•KeyPair. Uses the private key file and private key file password, along with the existing Snowflake account user name to connect to Snowflake.

Connection properties

For the connection properties, see the following topics:

Read properties

The following table describes the advanced source properties that you can configure in the Python code to read from Snowflake:

Advanced Property	Description
Database	Overrides the database specified in the connection.
Schema	Overrides the schema specified in the connection.
Warehouse	Overrides the Snowflake warehouse name specified in the connection.
Role	Overrides the Snowflake role assigned to user you specified in the connection. The warehouse name in the mapping overrides the warehouse name you specify in the connection. Even though you provide an incorrect warehouse name in the connection properties, the connection is successful. However, before you run the mapping, ensure that you specify the correct warehouse name in the mapping properties.
Pre SQL	The pre-SQL command to run on the Snowflake source table before the agent reads the data. For example, if you want to update records in the database before you read the records from the table, specify a pre-SQL statement. The query must include a fully qualified table name. You can specify multiple pre-SQL commands, each separated with a semicolon.
Post SQL	The post-SQL command to run on the Snowflake table after the agent completes the read operation. For example, if you want to delete some records after the latest records are loaded, specify a post-SQL statement. The query must include a fully qualified table name. You can specify multiple post-SQL commands, each separated with a semicolon.
Table Name	Overrides the table name of the imported Snowflake source table.
SQL Override	The SQL statement to override the default query used to read data from the Snowflake source.

Write properties

The following table describes the advanced target properties that you can configure in the Python code to write to Snowflake:

Advanced Property	Description
UpdateMode	Loads data to the target based on the mode you specify. Applicable when you select the Update operation or the Data Driven operation. Select from one of the following modes: - Update As Update. Updates all rows flagged for update if the entries exist. - Update Else Insert. The agent first updates all rows flagged for update if the entries exist in the target. If the entries do not exist, the agent inserts the entries.
Database	Overrides the database that you used to import the object.
Schema	Overrides the schema that you used to import the object.
Warehouse	Overrides the Snowflake name specified in the connection. The warehouse name in the mapping overrides the warehouse name you specify in the connection. Even though you provide an incorrect warehouse name in the connection properties, the connection is successful. However, before you run the mapping, ensure that you specify the correct warehouse name in the mapping properties.
Role	Overrides the Snowflake role assigned to the user specified in the connection.
Pre SQL	The pre-SQL command to run before the agent writes to Snowflake. For example, if you want to assign sequence object to a primary key field of the target table before you write data to the table, specify a pre-SQL statement. You can specify multiple pre-SQL commands, each separated with a semicolon.
Post SQL	The post-SQL command to run after the agent completes the write operation. For example, if you want to alter the table created by using create target option and assign constraints to the table before you write data to the table, specify a post-SQL statement. You can specify multiple post-SQL commands, each separated with a semicolon.
Batch Row Size	The number of rows written to a single file in the agent location. When the number of rows written to the file reaches the value specified, the agent flushes the data queue and starts processing the write commands.
Number of local staging files	The number of files that represents a single batch of data. The default number of files is 64. After the agent uploads the specified number of local staging files to the Snowflake user stage, Snowflake unloads the data to the target table.
Truncate Target Table	Truncates the database target table before inserting new rows. Select one of the following options: - True. Truncates the target table before inserting all rows. - False. Inserts new rows without truncating the target table Default is false.
Additional Write Runtime Parameters	Specify additional runtime parameters. For example, if you want to specify the user-defined stage in the Snowflake database to upload the local staging files, specify the name of the stage location in the following format: remoteStage=REMOTE_STAGE If you want to optimize the write performance, you can choose to compress files before writing to Snowflake tables. You can set the compression parameter to On or Off, for example: Compression=On By default, compression is on. Separate multiple runtime parameters with &.
Table Name	Overrides the table name of the Snowflake target table.
Rejected File Path	The filename and path of the file on the agent machine where you want to write the rejected records. For example, \rejectedfiles\reject7
Update Override	Overrides the default update query that the agent generates for the update operation with the update query.

Standard authentication

When you set up a Snowflake connection, configure the connection properties.

The following table describes the Snowflake connection properties for the Standard authentication mode:

Property	Description
Connection Name	Name of the connection. Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -, Maximum length is 255 characters.
Authentication	The authentication method that the connector must use to log in to Snowflake. Select Standard. Default is Standard.
Username	The user name to connect to the Snowflake account.
Password	The password to connect to the Snowflake account.
Account	The name of the Snowflake account. For example, if the Snowflake URL is https://<123abc>.us-east-2.aws.snowflakecomputing.com/console/login#/, your account name is the first segment in the URL before snowflakecomputing.com. Here, 123abc.us-east-2.aws is your account name. If you use the Snowsight URL, for example, https://app.snowflake.com/us-east-2.aws/<123abc>/dashboard, your account name is 123abc.us-east-2.aws Note: Ensure that the account name doesn't contain underscores. To use an alias name, contact Snowflake Customer Support.
Warehouse	The Snowflake warehouse name.
Role	The Snowflake role assigned to the user.
Additional JDBC URL Parameters	The additional JDBC connection parameters. Enter one or more JDBC connection parameters in the following format: <param1>=<value>&<param2>=<value>&<param3>=<value>.... For example: user=jon&warehouse=mywh&db=mydb&schema=public Important: Ensure that there is no space before and after the equal sign (=) when you add the parameters.

OAuth 2.0 authorization code authentication

The following table describes the Snowflake connection properties for an OAuth 2.0 - AuthorizationCode type connection:

Property	Description
Connection Name	Name of the connection. Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -, Maximum length is 255 characters.
Authentication	The authentication method that the Snowflake connection must use to log in to Snowflake. Select AuthorizationCode.
Account	The name of the Snowflake account. For example, if the Snowflake URL is https://<123abc>.us-east-2.aws.snowflakecomputing.com/console/login#/, your account name is the first segment in the URL before snowflakecomputing.com. Here, 123abc.us-east-2.aws is your account name. If you use the Snowsight URL, for example, https://app.snowflake.com/us-east-2.aws/<123abc>/dashboard, your account name is 123abc.us-east-2.aws Note: Ensure that the account name doesn't contain underscores. To use an alias name, contact Snowflake Customer Support.
Warehouse	The Snowflake warehouse name.
Additional JDBC URL Parameters	The additional JDBC connection parameters. Enter one or more JDBC connection parameters in the following format: <param1>=<value>&<param2>=<value>&<param3>=<value>.... For example: user=jon&warehouse=mywh&db=mydb&schema=public Important: Ensure that there is no space before and after the equal sign (=) when you add the parameters.
Authorization URL	The Snowflake server endpoint that is used to authorize the user request. The authorization URL is https://<account name>.snowflakecomputing.com/oauth/authorize, where <account name> specifies the full name of your account provided by Snowflake. For example, https://<abc>.snowflakecomputing.com/oauth/authorize Note: If the account name contains underscores, use the alias name. You can also use the Authorization Code grant type that supports the authorization server in a Virtual Private Cloud network.
Access Token URL	The Snowflake access token endpoint that is used to exchange the authorization code for an access token. The access token URL is https://<account name>.snowflakecomputing.com/oauth/token-request, where <account name> specifies the full name of your account provided by Snowflake. For example, https://<abc>.snowflakecomputing.com/oauth/token-request Note: If the account name contains underscores, use the alias name.
Client ID	Client ID of your application that Snowflake provides during the registration process.
Client Secret	Client secret of your application.
Scope	Determines the access control if the API endpoint has defined custom scopes. Enter space-separated scope attributes. For example, specify session:role:CQA_GCP as the scope to override the value of the default user role. The value must be one of the roles assigned in Security Integration.
Access Token Parameters	Additional parameters to use with the access token URL. Define the parameters in the JSON format. For example, define the following parameters: [{"Name":"code_verifier","Value":"5PMddu6Zcg6Tc4sbg"}]
Authorization Code Parameters	Additional parameters to use with the authorization token URL. Define the parameters in the JSON format. For example, define the following parameters: [{"Name":"code_challenge","Value":"Ikr-vv52th0UeVRi4"}, {"Name":"code_challenge_method","Value":"S256"}]
Access Token	The access token value. Enter the populated access token value, or click Generate Token to populate the access token value.
Generate Token	Generates the access token and refresh token based on the OAuth attributes you specified.
Refresh Token	The refresh token value. Enter the populated refresh token value, or click Generate Token to populate the refresh token value. If the access token is not valid or expires, the agent fetches a new access token with the help of the refresh token. Note: If the refresh token expires, provide a valid refresh token or regenerate a new refresh token by clicking Generate Token.

Key pair authentication

The following table describes the Snowflake connection properties for the KeyPair authentication type connection:

Connection property	Description
Connection Name	Name of the connection. Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -, Maximum length is 255 characters.
Authentication	The authentication method to log in to Snowflake. Select KeyPair.
Username	The user name to connect to the Snowflake account.
Account	The name of the Snowflake account. For example, if the Snowflake URL is https://<123abc>.us-east-2.aws.snowflakecomputing.com/console/login#/, your account name is the first segment in the URL before snowflakecomputing.com. Here, 123abc.us-east-2.aws is your account name. If you use the Snowsight URL, for example, https://app.snowflake.com/us-east-2.aws/<123abc>/dashboard, your account name is 123abc.us-east-2.aws. Note: Ensure that the account name doesn't contain underscores. To use an alias name, contact Snowflake Customer Support.
Warehouse	The Snowflake warehouse name.
Additional JDBC URL Parameters	Optional. The additional JDBC connection parameters. Enter one or more JDBC connection parameters in the following format: <param1>=<value>&<param2>=<value>&<param3>=<value>.... For example: user=jon&warehouse=mywh&db=mydb&schema=public Important: Ensure that there is no space before and after the equal sign (=) when you add the parameters.
Private Key File	Path to the private key file, including the private key file name, that the Secure Agent uses to access Snowflake. Note: Verify that the keystore is FIPS-certified.
Private Key Password	Password for the private key file.