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

Target objects and operations

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.

General restrictions

In advanced mode, mappings that write data to more than 500 columns fail with the following error:
HTTP POST request failed due to IO error

0 COMMENTS

We’d like to hear from you!