Snowflake Insert Activity

You can use this activity to run the insert SQL statements on the Snowflake data warehouse. You can insert multiple rows in the database in batches. Error in one batch does not stop the execution of the subsequent batch. To stop the execution of batch for error, select the Fault on Batch Failure checkbox. The failure record of a batch depends on the behavior of the underlying Snowflake JDBC driver and Snowflake data warehouse engine. If a batch fails to insert one or more records, the detailed message is displayed in logs including the batch number, reason of failure, and rows that failed to insert. The insertion operation results in the formation of total rows attempted and total rows affected.

General

The General tab contains the following fields.

Field	Module Property?	Visual Diff?	Description
Name	No	Yes	Specify the name to be displayed as the label for the activity in the process.
Snowflake Connection Resource	Yes	Yes	Click the Create New Process Property icon to select a Snowflake JDBC Connection shared resource. If no matching Snowflake JDBC Connection shared resource is found, click Create Shared Resource to create one. For more details, see Creating and Configuring the Snowflake JDBC Connection Resource.
Create Table from XSD	No	Yes	Use this option to create a new table in Snowflake from the XSD provided by you and select table format from external schema. When this checkbox is selected, Entity field on General tab is disabled and Table Name field, Load Schema button from SQL Builder tab are enabled. Click SQL Builder > Load Schema to provide the XSD schema for the table.
Entity	No	Yes	Click the Browse resource icon to select an entity. The entities downloaded in Snowflake JDBC Connection shared resource's Schema tab is available for selection. Note: This field is disabled when Create Table from XSD checkbox is selected.
Batch Size	Yes	Yes	All incoming messages are inserted performing SQL, number specified here defines how many SQL statements can be batched together that can be represented as a single SQL. The default value is 100.
Time Out	Yes	Yes	Defines activity timeout in seconds. Default value is 0, means activity timeout disabled. Note: Insert activity works in multiple batches, so per batch timeout is evaluated using the timeout value specified. Refrain from using a smaller timeout value.

Description

On the Description tab, provide a short description for the activity.

SQL Builder

The SQL Builder tab displays the metadata of the entity selected in the General tab or details of the entity schema loaded using the Load Schema button.

Field	Editable	Description
Table Name	Yes	Displays the name of the table to be created. When General tab > Create Table from XSD checkbox is selected, this field is enabled. If you provide XSD using the Load Schema button, the value of this field is set to name of root complex element of the XSD. However, you can edit this value and set to desired value.
Field Name	No	Displays name of the column.
Data Type	No	Displays the data type of the column.
Primary Key	Yes, if 'Metadata' table is loaded using XSD provided with 'Load Schema' button from SQL Builder tab. No, if 'Metadata' table is loaded using Entity field from General tab.	Displays if the column is a primary key. The column is editable if table is selected from external schema.
Not Null	No	Displays if the column accepts null value. The checkbox is selected if the constraints to the column in snowflake DB is marked as 'Not Null'. The checkbox is cleared if the constraints to the column in snowflake DB is not marked as 'Not Null'. This checkbox is non-editable.
Dimension	No	Displays the dimension of the column.
Values	Yes	Displays the fields that must be part of the Values clause for an insert SQL statement. By default, Values column is selected for all the fields.
Load Schema	No	When General tab > Create Table from XSD checkbox is selected, this field is enabled. If you click this button, a dialog is opened where you can create or browse XSD present in the workspace. Once you select XSD in the dialog, its details are displayed into 'Metadata' table and the 'Table Name' field is set to name of root complex element of the XSD.

Advanced

This tab has the following fields:

Field	Module Property?	Description
Override Database Name	Yes	Overrides Database name specified in snowflakejdbcResource.
Override Schema Name	Yes	Overrides Schema name specified in snowflakejdbcResource.
Interpret Empty String as NULL	Yes	If this checkbox is selected, empty string is interpreted as NULL value.
Fault On Batch Failure	Yes	If this checkbox is selected and if an error is encountered in one of the batches, the batch insert process stops and does not continue to the next batch.
Merge	No	If this checkbox is selected and if the record already exists, the insert process updates the record by executing the merge query. Note: Merge is supported only when batch size is 1.
Merge On Columns	Yes	If the Merge checkbox is selected, this field is enabled. Enter column names on which the merge operation should be performed. For more than one column names, enter column names separated by comma. Column names are not case-sensitive.

Input

All the fields in the SQL Builder tab that have Values column selected are a part of the Input tab. The Input tab displays the input schema of the activity as a tree structure. The information in the schema depends on the fields selected on the SQL Builder tab.

Output

The Output tab displays the rowsAttempted and rowsAffected fields. The rowsAttempted field holds the count of the number of rows that were attempted by Snowflake Insert activity and the rowsAffected field holds the count of the number of rows inserted successfully after the Snowflake Insert activity is invoked. The difference between these two is the number of rows that failed to insert. The Output tab displays the output schema of the activity as a tree structure. The output is read-only.

Fault

The Fault tab lists exceptions that are thrown by this activity.