You are seeing discrepancies in how Heroku Connect syncs
null and empty values from Salesforce.
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.