Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration Connectors
  • All Products

Table of Contents

Search

  1. Preface
  2. Part 1: Getting Started with Snowflake Data Cloud Connector
  3. Part 2: Data Integration with Snowflake Data Cloud Connector
  4. Part 3: SQL ELT with Snowflake Data Cloud Connector
  5. Appendix A: Data type reference
  6. Appendix B: Additional runtime configurations
  7. Appendix C: Upgrading to Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Back Next

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.
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.
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 Size
1
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 files
1
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 Path
1
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.
1
Doesn't apply to mappings in advanced mode.
0 COMMENTS
We’d like to hear from you!
Kevin Golde - March 22, 2024

So to avoid entering duplicate records on Upsert, do we set the UpdateMode to "Update Else Insert"?

Informatica Documentation Team - March 27, 2024

Hi Kevin Golde,

We're working to address your comments and will get back to you.

Regards,
Informatica Documentation Team

Informatica Documentation Team - April 10, 2024

Hi Kevin Golde,

The Update Else Insert option does not handle duplicates. If there are duplicate records in the data, they will be inserted or updated if the primary key column value matches with the incoming column in the Snowflake table.

Regards,
Informatica Documentation Team