I've had to spend sometime in the last couple of days deep diving into Heroku connect. If you don't know Heroku connect is a service that will sync selected data between Salesforce and a Heroku hosted Postgress database, and has a lot of very easy to use features to map specific fields, and will update schemes automatically as you update fields that you select. However and I've learned a few things about Heroku connect because of some issues we have been observing. Then I found this from the documentation.
Because Heroku Connect is an eventually consistent system with Salesforce, this behavior is expected. However, because Postgres is generally trusted as an immediately consistent record store the behavior can be surprising. This is usually observed in a few different ways. 1. A field seems to “flip-flop” as the value in Postgres is updated, set to a previous value, then a few seconds or minutes later is updated back to its new value. 2. A record is deleted in Postgres, updated in Salesforce (causing the record to be revived temporarily in Postgres), then the sync to delete the record occurs and the record is deleted in both SFDC and Postgres. 3. A record is updated in Postgres, then deleted in Postgres. Salesforce syncs the updates separately and the record is temporaily resurrected until the deletion event syncs to Salesforce.
They list some other issues that arise because of this. I've noticed these similar effects.
It is important to remember that Heroku Connect is eventually consistent which means it should not be relied on as a realtime store of info from Salesforce. This is problematic since typical sync scenarios, depending on the speed of your integration, might rely on your transactional database as a reliable datastore.
This is their advice to mitigate:
- Accelerated Polling makes it more likely that a temporary state will be pushed into Postgres. Disabling Accelerated Polling on a mapping can make these scenarios less likely.
- Long polling intervals can likewise make it less likely to occur.
- If you know that a record will have rapid updates, you can make those into a staging table and move the record to the mapped table once it is no longer changing quickly.
We enabled accelerated polling and are still running into issues and as far as their third piece of advice goes, this would be especially problematic to implement if you had a human making changes to records, which in the case may times, since its hard to predict behavior.
Heroku connect is a good tool overall, but given this limitation you have to give long thought to how exactly your integration should work.