We have a pipeline on Fivetran that connects Microsoft Dynamics 365 as source and Snowflake as destination. When we synced the data, we found that certain columns are missing on the destination. We reached out to Fivetran to resolve the issue

Fivetran:

Tried to investigate if the missing columns are of any specific type of field? If they're all the same type, it's possible something could be happening within Fivetran to cause the issue.

Missing columns are of type “lookup”. Lookup columns have GUIDs which refer to attributes in other entities. On SSIS, the lookup columns are matched with the columns from the referenced tables and a join is automatically done in the backend before migrating the data to sqlserver. Fivetran does not have the feature. 

We reached out to Fivetran again for a resolution:

Fivetran: 

For now, data in lookup fields can accessed by joining on the underlying data table. For example, we can join the ID in the _createdby_value field on the account table with the ownerid field on the systemuser table. That's because the underlying structure within Dynamics 365 has the _createdby_value field referencing the createdby field, which is associated with a systemuser: 

We would like to request for a feature to get both lookup columns and their referred columns instead of manually making 100s of joins.