Writing Data to Salesforce with Heroku Connect
Last updated August 12, 2021
Table of Contents
- Polling for Changes
- How Connect Writes to Salesforce
- SOAP vs Bulk API
- Write Algorithms
- Required Heroku Postgres Connections
- Handling Object Relationships
- Understanding the Trigger Log
- Preventing Write Sync Issues
- Write Errors
- Importing Large Quantities of Data Into Read/Write Mappings
- Using Custom Triggers with Heroku Connect
- Why Does My Postgres Data Not Match Salesforce?
- Diagnosing Perceived Performance Problems
Heroku Connect automatically chooses the most efficient method to transfer data between your database and your Salesforce org. It employs best practices that take data change volume and the details of Salesforce API operation into account. This article covers how Heroku Connect writes data from Postgres to Salesforce.
Polling for Changes
Heroku Connect polls your Heroku Postgres database for updates every two minutes, unless it detects an update from a
pg_notify trigger. These polls are limited to at most every 10 seconds. When Connect polls the database and finds changes it begins to write changes back to Salesforce. You can’t configure these intervals. When a set of writes completes, a new poll cycle starts and wait either two minutes or for a new change event from
Heroku Connect doesn’t poll for additional database updates while a write operation to Salesforce is underway. Additionally, it executes write operations across all mappings in order in serial. There’s no mechanism to prioritize a set of writes over any other set.
How Connect Writes to Salesforce
Heroku Connect captures changes made in Heroku Postgres on read-write mappings and sends them to Salesforce.
When a read-write table updates, a Postgres trigger fires and captures the change by inserting it into the
_trigger_log (trigger log) table. All read-write data tables share a single
_trigger_log table per Heroku Connect add-on. See The Heroku Connect Trigger Log for more info.
Heroku Connect processes all captured changes in a single process per add-on. It sends changes to Salesforce using the SOAP or Bulk API, depending on your write algorithm and other requirements. See the SOAP vs. BULK API section for details. This single process for writes runs independently and concurrently with reads from Salesforce for mapped objects.
TRUNCATE operations don’t get captured in the
_trigger_log table. Delete the records in Salesforce separately.
Connect isn’t able to observe change within transactional bounds and can’t enforce a strict ordering of writes as they occurred in the DB.
Not all columns are writeable. See System Fields: Read-Only Columns for more info.
By default, when Heroku Connect writes data to Salesforce, assignment rules aren’t run. If you’d like to enable your default assignment rules for Leads and Cases, create a support ticket. If you have multiple connections, be sure to mention them so that they can enable the setting for each one.
SOAP vs Bulk API
When bi-directional (“read-write”) sync is configured, Heroku Connect writes changes to Salesforce using SOAP or Bulk API. These API calls don’t count towards your Salesforce API limit.
The Bulk API is optimized for operations that apply to larger datasets. It’s faster than using SOAP API in these scenarios. Connect automatically attempts to use the Bulk API when all the following conditions are met:
- The connection is configured to use the Ordered Writes algorithm.
- A unique identifier is specified for the mapping.
- Between 2,000 and 10,000 contiguous changes of the same type (
DELETE) are made to a given object. For example, 5000 INSERTS into ‘Lead’ object.
- The Salesforce API version for the connection is set to v39 or higher.
When writing changes for more than 10,000 records using the Bulk API, the records are processed in batches of 10,000.
Heroku Connect uses SOAP API for writing changes to your Salesforce organization when:
- There are fewer than 2,000 records to be processed.
- The conditions to use the Bulk API aren’t met.
Heroku Connect attempts to pack as many records as possible into a SOAP message, up to 200 records.
The threshold for the number of records and batch sizes are different for read-only mappingsl.
Heroku Connect supports two algorithms when writing to Salesforce: Ordered Writes and Merged Writes. Select the algorithm that writes to Salesforce most efficiently for your use case. You can view and select the write algorithm for your connection via the Manage Connection page.
The Ordered Writes algorithm is a prerequisite for using the Bulk API. It’s not possible to perform bulk writes to Salesforce when using the Merged Writes algorithm.
Write algorithm selection happens at the connection level. All read-write mappings use the same write algorithm.
Ordered Writes Algorithm
Heroku Connect captures all record changes to the trigger log. The Ordered Writes algorithm is the default algorithm used. It always preserves the order of the changes from the trigger log, even if it’s less efficient at packing records into a given message. The Ordered Writes algorithm has the advantage of capturing each change as a distinct operation. The history of your Salesforce object reflects these changes.
Potential Pitfalls for the Ordered Writes Algorithm
Rapid updates to the same record can result in slower sync speeds as each individual change processes independently.
Writing to many objects in quick succession, or alternating back and forth between objects often results in the use of SOAP API instead of Bulk API. Heroku Connect attempts to pack each message sent to Salesforce as densely as possible. However, it must follow the writing rules of the Salesforce SOAP API. There’s a limit to the number of chunks of changes that can be packed in one message. API calls for syncing data to Salesforce are grouped by their mapping. The more changes that are made to different mappings in quick succession, the more API calls must be made, resulting to slower syncing. Many simultaneous database writers can also make this situation worse.
Additionally, when using Ordered Writes, a failed INSERT causes all subsequent updates to fail until corrective action is taken to resubmit that record. All changes are processed in order, so one failure holds up the queue until resolved. See Heroku Connect Write Errors for more info.
Merged Writes Algorithm
The Merged Writes algorithm condenses and reorders changes from the trigger log to maximize the number of records in each SOAP message. While Ordered Writes preserves the order of changes, if you make many rapid changes in succession, Merged Writes generally has better performance.
For example, the value of
Account.firstname was set to
Nathaniel and updated shortly after to
Nate. With Merged Writes, Heroku Connect drops the initial update of
Account.firstname = Nathaniel since the following update would overwrite it. Compare this behavior to Ordered Writes, which would process both changes to
Potential Pitfalls for the Merged Writes Algorithm
The Merged Writes algorithm attempts to reorder updates so that they resolve dependencies for relationships. However, it doesn’t pick a correct order in all cases. Circular dependencies are known to cause issues. Additionally, merged changes can make it impossible to reliably establish some relationship types.
Use this algorithm with caution if you have Apex triggers and process rules in Salesforce. They aren’t guaranteed to see all changes made in the database as this algorithm merges some sets of changes.
The Merged Writes algorithm attempts to resend failed inserts on a subsequent update, which simplifies some error recovery cases.
It’s not possible to perform bulk writes to Salesforce when using the Merged Writes algorithm. Use the Ordered Writes algorithm for Bulk API use.
Required Heroku Postgres Connections
Each Heroku Postgres plan comes with a connection limit.
Heroku Connect uses 1 connection per mapping to read records. It uses 1 additional connection if any of the mappings are read-write to process all writes to Salesforce. Only 1 connection is used for writing, regardless of the number of read-write mappings. For example, if you have 20 mappings, 5 of which are read-write, then Heroku Connect uses 21 connections to your Postgres database.
Handling Object Relationships
There are several types of relationships that can be set between objects. See Handling Object Relationships in Heroku Connect for info on how to set them up.
Understanding the Trigger Log
The trigger log captures changes made to records in Heroku Postgres. Heroku Connect uses the trigger log to send data to Salesforce. See The Heroku Connect Trigger Log for details.
Preventing Write Sync Issues
Sync issues can occur when making multiple rapid updates to the same field. Heroku Connect can also create duplicate records in some cases. See Heroku Connect Duplicate Record Issues and Heroku Connect Rapid Multiple Update Issues for tips on preventing these issues.
Writes to Salesforce can fail for a number of reasons, including planned maintenance windows. In cases where Salesforce is unavailable, Connect queues writes until it’s available again. In all other cases, resolving errors requires user intervention. See Heroku Connect Write Errors for more info.
Importing Large Quantities of Data Into Read/Write Mappings
Connect uses the Salesforce Bulk API to write changes from Heroku Postgres to Salesforce when they meet a small set of requirements. To learn more about when Connect uses the Bulk API, review the SOAP vs. Bulk API section. You can also import data directly into Salesforce using a tool such as Data Loader.
Using Custom Triggers with Heroku Connect
Custom triggers aren’t officially supported and Heroku Support can’t provide help with them. Proceed at your own risk.
Heroku Connect uses Postgres triggers to observe changes in your Connect tables and write your changes to Salesforce. Implementing your own custom triggers on Connect tables can create additional load and Connect can miss your updates. See Using Custom Triggers with Heroku Connect for more info.
Why Does My Postgres Data Not Match Salesforce?
There are a few scenarios where data in Postgres doesn’t exactly match what was written to Salesforce:
- Salesforce trims extra spaces from the start and end of any strings.
- If a string value is empty, the Salesforce API sends it as NULL. Therefore that’s how it’s stored in Postgres when it syncs back from Salesforce. This behavior is part of the Salesforce API, which Heroku Connect has no control over.
- Postgres stores numbers as a
doublecolumn, which has 15-digit precision. Salesforce values that exceed this limitation still sync via Heroku Connect, but with reduced precision. This difference can be noticeable with large values.
Diagnosing Perceived Performance Problems
A number of factors can affect sync performance with Heroku Connect. Follow the steps in Diagnosing Heroku Connect Performance Issues to diagnose perceived write performance issues.