Snowflake Data Cloud Connector > Part II: Data Integration with Snowflake Data Cloud Connector > Targets for Snowflake Data Cloud > Target properties for Snowflake Data Cloud
  

Target properties for Snowflake Data Cloud

You can configure a Target transformation to represent a Snowflake Data Cloud target.
The following table describes the Snowflake Data Cloud target properties that you can configure in a Target transformation:
Property
Description
Connection
Name of the target connection.
You can select an existing connection, create a new connection, or define parameter values for the target connection property.
Note: You can switch between a non-parameterized and a parameterized Snowflake Data Cloud connection. When you switch between the connections, the advanced property values are retained.
To overwrite the target connection properties at runtime, select the Allow parameter to be overridden at run time option.
Target Type
Type of target object. Select Single Object or Parameter.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the target object or click New Parameter to define a new parameter for the target object.
The Parameter property appears only if you select parameter as the target type.
If you want to overwrite the target object at runtime, select the Allow parameter to be overridden at run time option. When the task runs, it uses the parameters from the file that you specify in the task advanced session properties.
Object
The target object for the task. Select the target object.
You can either select an existing table or create a new table. You can write data by selecting an existing table or creating a new table in the target by using the Create New at Runtime option.
Create New at Runtime
Creates a Snowflake target table at runtime based on the table type and the path you specify.
To create a target table at runtime, provide the following parameters:
  • - Optional. Specify the table type as table.
  • - In the Path field, specify the Snowflake database name and schema in the following format: <database name>/<schema>
The agent creates the target table based on the object name and the path you specify.
Note: You can edit the metadata of the source fields before creating the target.
Use Exact Source Field Names in Target
Applies to the Create New at Runtime option.
Determines if you can create a target object at runtime with the exact source field names.
Select to retain all the source field names in the target exactly as in the source, including any special characters. If you disable this option, special characters in the source are replaced with underscore characters in the target.
Default is disabled.
Operation
The target operation. Select Insert, Update, Upsert, Delete, or Data Driven.
Update columns
The primary key column to update, upsert, or delete data in a Snowflake target.
If you do not specify a primary key column, the mapping considers the target column that you configured as the primary key column to update, upsert, or delete data.
Data Driven Condition
Enables you to define expressions that flag rows for an insert, update, upsert, or delete operation.
The following table describes the advanced properties that you can configure in a Target transformation:
Advanced Property
Description
UpdateMode
Loads data to the target based on the mode you specify.
This property applies when you select the Update, Upsert, or Data Driven operation with the update or upsert condition.
Select one of the following modes:
  • - Update As Update. Updates records in the target table if the specified primary key column value matches with the incoming column value.
  • - Update Else Insert. Updates records in the target table if the specified primary key column value matches with the incoming column value. If the primary key column value does not match, the mapping inserts a new row with the records.
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 Size1
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.
For more information on configuring the batch size value, see Configuring the batch size and the number of local staging files
Number of local staging files1
The number of files that represents a single batch of data. The default number of files considered 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.
For more information on configuring the number of local staging files, see Configuring the batch size and the number of local staging files.
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
The additional runtime parameters that you can use when you write to Snowflake.
You can enter multiple write runtime parameters, separated by ampersand (&), in the following format:
<param1>=<value>&<param2>=<value>&<param3>=<value>....
For example, if you want to specify the user-defined stage in the Snowflake database to upload the local staging files and don't want to compress files before you write to Snowflake tables, enter the following runtime parameters:
remoteStage=REMOTE_STAGE&Compression=Off
For the list of additional write runtime parameters that you can configure, see Write runtime parameters.
Table Name
Overrides the table name of the Snowflake target table.
Rejected File Path1
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.
Success File Directory
Not applicable.
Error File Directory
Not applicable.
Forward Rejected Rows
Determines whether the transformation passes rejected rows to the next transformation or drops rejected rows. By default, the agent forwards rejected rows to the next transformation.
1Doesn't apply to mappings in advanced mode.

Write runtime parameters

You can customize and set any additional runtime parameters when you write to Snowflake.
If you specify more than one additional write runtime parameter, separate each key-value pair with an ampersand (&).
You can configure the following properties as additional write runtime parameters in the Target transformation:
For more information about these additional parameters, see Copy into table in the Snowflake documentation.