Snowflake Data Cloud Connector

Back Next

Target objects and operations

In a Target transformation, you can use a single object or parameter as the target type.

When you choose the target type, you can select the operation to insert, update, upsert, or delete data in a Snowflake target. You can also use the data driven operation to define expressions that flag rows for an insert, update, delete, or reject operation.

Target operation restrictions

Some rules apply when you configure a target operation.

Parameterized target

When you parameterize the target object and connection and enable the

Allow parameter to be overridden at run time

option in a transformation, you cannot override the object name using the fully qualified name such as

db.schema.tablename

. You must pass the

db=<dbname>&schema<schemaname>

values in the

Additional JDBC URL Parameters

field in the Snowflake Data Cloud connection.

Update columns

You need to specify the temporary key column to update data to or delete data from a Snowflake target. If the Snowflake target does not include a primary key column, click Add
to add a temporary key. You can select multiple columns.

If the records from the source tables contain duplicate primary keys, perform one of the following tasks in mappings to update or delete records in Snowflake:

Before you import the target table, define multiple primary keys in the target table.

Define more than one custom key for the target object using the Update Columns option in the advanced target properties.

When you configure a mapping in advanced mode, you must additionally consider the following guidelines:

When you specify an expression for a data driven condition and do not specify a column in the Update Columns
field, a validation message does not appear. For DD_INSERT and DD_REJECT, the update column is not mandatory. For other operations such as DD_UPDATE and DD_DELETE, select at least one field in the update columns for the operation to work.

When you use a parameterized Snowflake connection in a Target transformation, the Update Columns
field does not display in the target properties. In the Target transformation, select a valid connection and object that display in the list and then select the operation.

When the Snowflake target object in the mapping is parameterized and the selected operation is data driven or upsert, the Update Column field does not display in the dynamic mapping task target properties.

Data driven operation

When you configure a data driven expression to update the rows in Snowflake from a mapping in advanced mode, you need to specify the third argument in the IIF condition. If you do not specify a third argument in the IIF condition, the agent treats the operation as an insert for all non-matching rows.

For example, if you give a condition

IIF(Update_column=2,DD_UPDATE)

without including the third argument, such as

DD_DELETE

in the following expression

IIF(Update_column=2,DD_UPDATE,DD_DELETE)

, for any other row where the value of the update_column is not equal to 2, the agent performs an insert by default.

In the example, from the expression

IIF(COL_INTEGER = 2, DD_UPDATE)

, COL_INTEGER=2 resolves to 1 and COL_INTEGER!=2 resolves to 0, where 1 is the internal ID for the Update operation and 0 is the internal ID for the Insert operation. The value of the third argument when not specified defaults to 0.

You can use data driven operation with the following restrictions for mappings in advanced mode:

If one of the source rows that is marked for Insert is already available in the target, the agent still inserts the row to the target.

When you define an expression for a Data Driven operation type, the expression editor displays column names up to 120 characters only.

If the expression in the data driven condition contains special characters, the agent fails to validate the data driven condition and the mapping runs successfully.