Identifying the Primary Key output while inserting a row in Snowflake

The simplest workaround to this problem is to maintain a foreign key relationship in the table where you want to offload your data. In the example below, you may find that in the Snowflake Insert Record activity, even though you have an Output SNOWFLAKE_ID, there is no real output during execution when the row gets added. This is a Snowflake limitation. Therefore, we maintain a Foreign Key Column (SFDC_OPPORTUNITY_ID) for this table.

Once the row is added, we can use the Snowflake Native search activity to query the Primary Key (SNOWFLAKE_ID) in exchange for the Foreign Key (SFDC_OPPORTUNITY_ID) and using a subsequent Get Row Item system activity.

The returned SNOWFLAKE_ID (Primary Key) can also be stored in the upstream application in a custom field (Salesforce in this case), so that each time an update needs to be performed in a row, the ID does not need to be queried with Foreign Key.