Product: Streamsets Data Collector
Issue:
Oracle CDC pipeline writing to Snowflake destination we see these SNOWFLAKE_28 error as shown below sporadically.
Versions affected:
All SDC Versions that support Oracle CDC and Snowflake Destination
Solution:
NOTE: This is just one of the conditions that run into SNOWFLAKE_28.
Here is a series of steps that produces this exception
The screenshot shows the pipeline is started with Initial Change in oracle CDC set to From Latest Change.
Now load about 9 sample records into the Oracle table
We will see that 9 records are processed by Oracle CDC pipeline and loaded into Snowflake table as we would expect.
Now we stop the Oracle CDC pipeline and delete the records in the original table that has a OBJECT_ID values of 2,3,4
Now Reset Origin and start the CDC pipeline which means it will disregard what it was doing and wait for new changes to happen. The effect of this is the 4 records that we deleted in oracle with OBJECT_ID 2,3,4,5 are not captured so the Snowflake still has and will continue to possess these records in their original state.
Now let's insert the records with OBJECT_ID 2,3,4,5 and insert 3 new records with OBJECT_ID,112,113,114 into the table and observe what happens.
We see that this sequence basically triggers a SNOWFLAKE_28 exception.
This is mainly due to Snowflake being unable to merge an incoming record with a PK value that already exists in the snowflake table.