Snowflake Data Cloud Connector

Back Next

Override the update operation

You can specify an update override to override the update query that the Secure Agent generates for the update operation.

When you configure an update override, the Secure Agent uses the query that you specify, stages the data in files, and then loads that data into a temporary table using the Snowflake's loader copy command. The data from the temporary table is then loaded to the Snowflake target table. The syntax that you specify for the update query must be supported by Snowflake.

Specify the update query in the following format:

UPDATE <Target table name> SET <Target table name>.<Column1> = :TU.<Column1>, <Target table name>.<Column2> = :TU.<Column2>, … <Target table name>.<ColumnN> = :TU.<ColumnN> FROM :TU WHERE <Target table name>.<Update Column1> = :TU.<Update Column1> AND <Target table name>.<Update Column2> = :TU.<Update Column2> AND … <Target table name>.<Update ColumnN> = :TU.<Update ColumnN>

where, :TU. represents the incoming data source for the target port.

The Secure Agent replaces :TU. with a temporary table name while running the mapping and does not validate the update query.

When you configure an update override in a mapping to write to Snowflake, consider the following rules:

Ensure that the column names for :TU matches the target table column names.

Ensure that the column names are fully qualified names.

Specify the update query with a valid SQL syntax because Snowflake Data Cloud Connector replaces :TU with a temporary table name and does not validate the update query.

Do not change the order of the column in the mappings when you configure the update override option.

The update query in the mapping must not contain unconnected fields to the target.

Ensure that the

UpdateMode

advanced property is set to

Update As Update

Target objects and operations

Download Guide

Watch

Comments

Cloud Connectivity Homepage

Communities