Connections for INFACore

Back Next

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:

Standard authentication

Key pair authentication

OAuth 2.0 authorization code authentication

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.