Product: StreamSets Data Collector with the Snowflake Connector
Issue:
Upon reading data from a CDC stream and attempting to write it out to a Snowflake destination, the pipeline fails. Errors similar to the following are seen in the sdc.log:
SNOWFLAKE_30 - Incorrect next CDC operation INSERT/UNDELETE for current CDC operation INSERT/UPSERT/UNDELETE
Symptoms:
1. The incoming data is a CDC event stream.
2. The "CDC Data" option is enabled in the Snowflake Destination stage.
3. The "Connection Pool" configuration is set to a value greater than zero in the Snowflake Destination stage.
Solution:
By default, configuring the Snowflake Destination stage's "Connection Pool" to a value higher than zero enables multi-threading within the stage itself. As a result, the records being read from a batch can be read out of order since multi-threading makes no guarantees about the order in which records are processed.
This, in turn, can cause records with the same rowkey to be treated as if they are out of order when the are actually in the appropriate order within the batch.
In order to resolve the issue, the value for the "Connection Pool" within the Snowflake Destination stage must be configured to zero. This will prevent any multi-threading within the destination, and will prevent records from the same batch being processed out of order.
