Snowflake Snowpipe Streaming

Description

The Snowpipe Streaming process action allows a target Snowflake table to ingest source data using the Snowpipe Streaming API.

In a process, the action requires an input bind link that contains source data. The action also exposes an output bind that contains any errors reported by Snowflake.

Also see Getting started with Snowpipe.

Prerequisites

Valid Snowflake metadata must be dragged and dropped onto this action.
The metadata must have a valid Snowflake module defined.
The metadata must contain Snowpipe credentials.

Parameters

Parameter Default value Description

Parameter	Default value	Description
Name	Snowflake Snowpipe Streaming	Display name given to the action.
Target Database Name	(from metadata)	Name of the database in which the target table exists. Overrides metadata values.
Target Schema Name	(from metadata)	Name of the database schema in which the target table exists. Overrides metadata values.
Target Table Name	(from metadata)	Name of the target table. Overrides metadata values.
Channel Name	Semarchy xDI Channel	Name of the Snowpipe channel used to stream data to Snowflake.
Offset Token Column Name	`SNOWFLAKE_OFFSET_TOKEN`	Name of the column in the source data that contains the value to use as an offset token. This column will not be inserted into the target. It should contain a value that uniquely identifies the source line, such as an auto-incremented numeric column or a timestamp, which allows processes to restart the streaming from that line. If you want to use the default value, make sure the offset token column in your data source uses `SNOWFLAKE_OFFSET_TOKEN` as its alias.
Offset Token Comparison	Enable	Enable: the tool compares the last committed offset token to the source data, and starts streaming lines after the one with the last offset token. This avoids inserting the same data twice, as long as offset tokens are unique. Disable: the tool tries to insert all data from the source set regardless of the offset token values. This may result in duplicates if pre-inserted data has not been removed from the source.
On Error Behavior	Abort Execution	Continue Execution: execution continues after an error, and tries to insert all the lines from the source. Rejected lines must be consumed with a bind link and a target action. The reject result set will contain the columns from the source query, and an additional “REJECT_MESSAGE” column that that contains the reject message reported by Snowflake. Abort Execution: execution stops immediately after an error. The error is returned in the error message, and no lines from the job are commited to the target.
Batch Size	1000	Number of lines sent to Snowflake as one batch. Adjust this value to stay under 16 MB per batch.

Name

Snowflake Snowpipe Streaming

Display name given to the action.

Target Database Name

(from metadata)

Name of the database in which the target table exists.
Overrides metadata values.

Target Schema Name

(from metadata)

Name of the database schema in which the target table exists.
Overrides metadata values.

Target Table Name

(from metadata)

Name of the target table.
Overrides metadata values.

Channel Name

Semarchy xDI Channel

Name of the Snowpipe channel used to stream data to Snowflake.

Offset Token Column Name

SNOWFLAKE_OFFSET_TOKEN

Name of the column in the source data that contains the value to use as an offset token.

This column will not be inserted into the target. It should contain a value that uniquely identifies the source line, such as an auto-incremented numeric column or a timestamp, which allows processes to restart the streaming from that line.

If you want to use the default value, make sure the offset token column in your data source uses SNOWFLAKE_OFFSET_TOKEN as its alias.

Offset Token Comparison

Enable

Enable: the tool compares the last committed offset token to the source data, and starts streaming lines after the one with the last offset token. This avoids inserting the same data twice, as long as offset tokens are unique.

Disable: the tool tries to insert all data from the source set regardless of the offset token values. This may result in duplicates if pre-inserted data has not been removed from the source.

On Error Behavior

Abort Execution

Continue Execution: execution continues after an error, and tries to insert all the lines from the source.
Rejected lines must be consumed with a bind link and a target action. The reject result set will contain the columns from the source query, and an additional “REJECT_MESSAGE” column that that contains the reject message reported by Snowflake.

Abort Execution: execution stops immediately after an error. The error is returned in the error message, and no lines from the job are commited to the target.

Batch Size

1000

Number of lines sent to Snowflake as one batch. Adjust this value to stay under 16 MB per batch.

Statistics

Name Description

Name	Description
`SNOWPIPE_ERROR`	Number of records reported as errors by Snowpipe Streaming.
`SNOWPIPE_INSERT`	Number of records inserted successfully by Snowpipe Streaming.

SNOWPIPE_ERROR

Number of records reported as errors by Snowpipe Streaming.

SNOWPIPE_INSERT

Number of records inserted successfully by Snowpipe Streaming.

Error messages

Compilation time

This action warns of the following errors when the process is built:

Error type	Suggestions
Mandatory fields have not been filled out.	Make sure fields have not been left empty.
Action does not have Snowflake Metadata.	Add Snowflake metadata to action.
Action does not have a bind link from a source action.	Make sure to execute this action as a target of a direct bind link.
There is no bind link to a target action, but On Error Behavior is set to Continue Execution.	Set the error behavior to Abort Execution. Process the rejected data result set with an output bind link.

Error type

Suggestions

Mandatory fields have not been filled out.

Make sure fields have not been left empty.

Action does not have Snowflake Metadata.

Add Snowflake metadata to action.

Action does not have a bind link from a source action.

Make sure to execute this action as a target of a direct bind link.

There is no bind link to a target action, but On Error Behavior is set to Continue Execution.

Set the error behavior to Abort Execution.
Process the rejected data result set with an output bind link.

Execution

This action returns the following errors when running the process:

Error type Suggestions

Error type	Suggestions
Offset token type from source data is incompatible with last committed offset token returned by Snowflake.	Check if the offset token column has changed type.
At least one error present in the inserted result set.	Review rejected lines, and reinsert if necessary.
On data insertion, last offset token returned by Snowflake does not match last offset token you sent.	Check reject result set for details as to why the data was rejected. This may happen if all lines were rejected. The expected and last sent tokens are published in variables `SNOWPIPE_LAST_SENT_OFFSET_TOKEN` and `SNOWPIPE_LAST_COMMITTED_OFFSET_TOKEN`.
Chosen offset token column has an incorrect type	Set offset token column to Integer, Timestamp, or String type.

Offset token type from source data is incompatible with last committed offset token returned by Snowflake.

Check if the offset token column has changed type.

At least one error present in the inserted result set.

Review rejected lines, and reinsert if necessary.

On data insertion, last offset token returned by Snowflake does not match last offset token you sent.

Check reject result set for details as to why the data was rejected.
This may happen if all lines were rejected. The expected and last sent tokens are published in variables SNOWPIPE_LAST_SENT_OFFSET_TOKEN and SNOWPIPE_LAST_COMMITTED_OFFSET_TOKEN.

Chosen offset token column has an incorrect type

Set offset token column to Integer, Timestamp, or String type.