How Can I Check Whether null Values Were Caused by a Connection or Permissions Issue?

Question

Some columns that previously contained data now contain null values. How can I determine whether a change in Salesforce permissions or an issue with the Fivetran connection caused this issue?

Environment

• Connector: Salesforce
• Destination: Snowflake

Answer

To determine whether a change in Salesforce permissions or a Fivetran connection issue caused the increase in null values, do the following:

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.