Heroku Connect - Trigger sync of failed records

About

Heroku Connect is an add-on from Salesforce to sync data into a PostgreSQL database. It can be used to connect a modern web system written in a language like Python, Ruby, JavaScript or PHP with a Salesforce Org. Through a web interface or an API a mapping can be configured, and suddenly the activated objects will appear magically in your database instance. It also allows for a bi-directional sync, so that a web application can sync data back into a Salesforce org.

I found that sooner or later one ends up with a few records in an error state, they did not sync up. Most of the time the reason is that a change was made in the connected Salesforce instance and suddenly the written data is not valid anymore. In this case the field _hc_lastop will be set to the value ERROR and the field _hc_err will contain further details about the error.

After the problem is understood and measures have been taken, the data needs to be synced again. For me the following two options worked out quite well so far.

Trigger an update in the affected row

This works by updating something in the affected row, so that the sync will be triggered again. It seems to be enough to just set an attribute to its current value, like in the following example:

UPDATE salesforce.custom_object__c
   SET name = name
 WHERE id = 123;

In many cases I got quite good results this way. Make sure that you have a recent backup of your database though.

Update the trigger log

Heroku Connect maintains a log of triggered items, it can be updated as well to achieve the same. I assume though that the sync will not happen as fast as the first way.

Here is how it works in principle:

UPDATE salesforce._trigger_log
   SET state = 'NEW'
 WHERE state = 'FAILED'
   AND table_name = 'custom_object__c';

It's best to inspect this table first with a few SELECT queries to avoid that you change more things than you want.

Conclusions

These two snippets are only two options to get your data into a flow again. I am currently thinking of how to use the failure information for building a small dashboard so that I could monitor easily my data and maybe trigger a sync from there again. This is not useful for all cases though, make sure to always understand what caused the issue and what is the right fix for it before just trying to trigger a sync of invalid data.