How to Check Whether null Salesforce Values Were Caused by a Connection or Permissions Issue

Environment: Salesforce connections syncing to a Snowflake destination

You may find that some columns that previously contained data now contain null values. To determine whether a change in Salesforce permissions or an issue with the Fivetran connection caused this issue, follow the steps below:

Check when the records were last modified

In Snowflake, use the following query to get a daily summary of the number of records containing null values, sorted by when they were most recently modified. Replace <object_name> and <field_name> with the applicable values.

SELECT
  DATE_TRUNC('day', LAST_MODIFIED_DATE) AS mostRecentSystemModstamp,
  COUNT(DISTINCT(id)) AS numberOfRecords
FROM schema_name.<object_name>
WHERE <field_name> is NULL
group by 1 
order by 1 desc
limit 1000;

Check when Fivetran last synced the records

In Snowflake, use the following query to get a daily summary of the number of records containing null values, sorted by when we most recently synced them. Replace <object_name> and <field_name> with the applicable values.

SELECT
  DATE_TRUNC('day', "_FIVETRAN_SYNCED") AS mostRecentFivetranSynced,
  COUNT(DISTINCT(id)) AS numberOfRecords
FROM schema_name.<object_name>
WHERE <field_name> is NULL
group by 1 
order by 1 desc
limit 1000;

Interpret the results

An increase in the number of null values for records based on when they were most recently modified indicates that a Salesforce permissions change may have caused the issue. However, an increase based on when we synced the records suggests an issue with your Fivetran connection caused the issue.