Salesforce Update Objects - Snowflake

The last step in this kit builds and/or updates a table in the Snowflake data warehouse that keeps track of all the updates that occur during a run. This is the same table that gets created/populated in the Salesforce Create Objects Kit so you can monitor all changes to the Salesforce platform that happens through Rivery.

This Kit includes…

• Logic river that orchestrates the update of objects within Salesforce with the following rivers:
  • Sample: SQL Transformation river that builds a list of structured records to update objects within Salesforce
  • Action river to retrieve API Access Token
  • Action river to update object records within Salesforce based on SQL Transformation
  • SQL Transformation river that builds and populates a metadata table for all information gathered on each record updated within Salesforce

Minimum RPU Consumption: 1

Configuring this Kit for use

Variables

In this Kit, there are three river variables and three global variables that are used to make the Kit’s configuration dynamic. 

• {domain} is used as the organization-specific subdomain in the Salesforce login and application URLs.
• {object_type} is used as the object table for which records will be updated from the Kit.
• {api_version} is used as the api version number for the REST request. In the kit, it is initialized to ‘v54.0’ but can be changed if needed.
• {Database_Salesforce} is used in all Target configurations and queries as the target database name.
• {Schema_Salesforce} is used in all Target configurations and queries as the target schema name.
• {Salesforce_Alert_Group} is a global variable used as the alert email address(es) for when a river fails (optional).

To update the river variables, navigate to the “Salesforce API – Update Records Logic” river and click on the top right “Variables” button. Add values to the Value column for the domain and object_type variables.

To create the global variables, go to the Variables menu on the left side navigation bar and create the Database_Salesforce, Schema_Salesforce, and Salesforce_Alert_Group variable and fill in the value as the Snowflake database and schema where you would like the metadata table to be created and the email address(es) to which you would like the failure emails sent.

Connections

Before downloading this kit, there are two connection-focused prerequisites that need to be completed. The first would be to create a connected app within the Salesforce platform that will allow for data to be passed through a REST API connection. Make sure to copy the setup from the screenshot below within the API (Enable OAuth Settings) section, as well as giving it any name and adding your email as the contact email. This only needs to be completed if there is not already a connected app with the Oauth settings enabled.

The second prerequisite is to create a custom Salesforce REST API connection in Rivery with the parameters listed below. These parameters will populate action river variables to retrieve an access token that will then be used in downstream REST requests. You can find the values within your Salesforce account.

• grant_type: The OAuth 2.0 grant type that the connected app requests. The value must be “password” for this Kit
• client_id: The connected app’s consumer key, which you can find on the connected app’s Manage Connected Apps page or from the connected app’s definition.
• client_secret: The connected app’s consumer secret, which you can find on the connected app’s Manage Connected Apps page or from the connected app’s definition.
• username: The username (email) of the user that the connected app is imitating.
• password: The password of the user that the connected app is imitating concatenated with the user’s security token (<password><security_token>). The security token can be reset within the Salesforce account settings.

Directions to Update Passed Data

Updating records in an object table using the Salesforce API requires the data to be in an array structure which is why we use the object_construct function in our SQL sample code. This produces a key-value pair for the new record. The below screenshot shows an example structure of the request body that should be sent with the REST API request. 

The SQL sample code shows an illustrative example of linking an internal table (CUSTOMER) to the Salesforce Lead table by IDs (C_CUSTKEY and LEADID) to update the phone field in the Salesforce Lead object. Updating any object in Salesforce will require a list of the object IDs within the data warehouse and the easiest way to achieve this is by selecting that object within the Salesforce multi-table river and finding a way to map your internal table to the pull data.

Have questions about this Kit?

Set up a meeting with a Rivery solutions engineer.