Identifying the Primary Key output while inserting a row in Snowflake

If you have a table with an auto-incrementing ID column, a common use case is wanting to insert a row in that table and output the ID of the created row.

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 case of the example below, you may find that in 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. So, 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.