Use Case/Scenario:
You want to analyse objects at certain points in time using Fivetran’s history mode for Salesforce.
Fivetran Recommendation:
- Use the ‘_fivetran_start’ and ‘_fivetran_end’ dates in your query
For example:
- select * from object where _fivetran_start >= x and _fivetran_end < y
Context:
Fivetran’s Salesforce connector provides a number of columns that appear to be queryable, but don’t offer you the ability to analyse rows at points-in-time. For example:
- The ‘isDeleted’ flag always becomes the inverse of the ‘isActive’ flag for non-latest records, so even if a historic row wasn’t deleted at the point it was written the ‘isDeleted’ flag will default to ‘TRUE’
- The ‘isActive flag’ will always only ever be ‘TRUE’ for the latest record, so even if a historic row was active at the point it was written it will always default to ‘FALSE’ when it becomes the non-latest row
However, the following is also true:
- For any non-deleted row, the ‘_fivetran_end’ date will be a true timestamp (such as 2021-03-30 11:08:59.999)
- For any non-latest row, the ‘_fivetran_end’ date will be a true timestamp (such as 2021-03-30 11:08:59.999)
- For any deleted row, this will always be the latest row and so the ‘_fivetran_end’ date will be an untrue timestamp (such as 9999-12-31 23:59:59.999)
Therefore, we can assume that if you query between points-in-time where both timestamp values are true timestamps your query will always meet the following criteria:
- The rows returned were not deleted at that point-in-time
- The rows returned were active at that point-in-time
Considerations:
If objects have been deleted and then undeleted, then this deletion will not be apparent in the rows written to your warehouse.