How does Heroku Connect sync null and empty values?

Resolution

In Salesforce, values that aren't set have null values. This is different than an empty string, which is also a possible value. There is currently a discrepancy in how Heroku Connect handles values that are defined as null in Salesforce.

When there are fewer than 20,000 changes to sync, Heroku Connect uses the SOAP API to retrieve data, where null values are handled correctly: null values from Salesforce are written as null values in Postgres.

However, when there are 20,000 or more changes to sync, Heroku Connect uses the Bulk API to retrieve data. Our current implementation cannot distinguish between null values and empty values in the Bulk API results. As a result, unless we know definitively that a value must be null, it's written as an empty value in Postgres. This is a discrepancy that we have on our roadmap to correct. We want the Bulk API behavior to match the SOAP API behavior.

Furthermore, you may have read some information in Salesforce's documentation on formulas that text fields can never be null. This is only true in the context of formulas. Text fields that aren't set in Salesforce have the value of null when retrieved by the API.