Azure Synapse Link for Dataverse FAQ

This article provides information on frequently asked questions about exporting Microsoft Dataverse table data to Azure Synapse Analytics and Azure Data Lake.

How can I access my table relationships?

To access many-to-many relationships, the relationship will be available as a table to select from the Add tables page for a new link and from the Manage tables for a preexisting link.

What happens when I add a column?

When you add a new column to a table in the source, it is also added at the end of the file in the destination in the corresponding file partition. While the rows that existed prior to the addition of the column won't show the new column, new or updated rows will show the newly added column.

What happens when I delete a column?

When you delete a column from a table in the source, the column is not dropped from the destination. Instead, the rows are no longer updated and are marked as null while preserving the previous rows.

What happens if I change the data type of a column?

Changing the data type of a column is a breaking change and you will need to unlink and relink.

What happens when I delete a row?

Deleting a row is handled differently based on which data write options you choose:

  • In-place update: This is the default mode and when you delete a table row in this mode, the row is also deleted from the corresponding data partition in the Azure Data Lake. In other words, data is hard deleted from the destination.
  • Append-only: In this mode, when a Dataverse table row is deleted, it is not hard deleted from the destination. Instead, a row is added and set as isDeleted=True to the file in the corresponding data partition in Azure Data Lake.

What date and time formats can be expected in exported Dataverse tables?

There are three date and time formats that can be expected in the exported Dataverse tables.

Column Name Format Data Type Example
SinkCreatedOn and SinkModifiedOn M/d/yyyy H:mm:ss tt datetime 6/28/2021 4:34:35 PM
CreatedOn yyyy-MM-dd'T'HH:mm:ss.sssssssXXX datetimeOffset 2018-05-25T16:21:09.0000000+00:00
All Other Columns yyyy-MM-dd'T'HH:mm:ss'Z' datetime 2021-06-25T16:21:12Z

Note

CreatedOn data type changed from datetime to datetimeOffset on 07/29/2022. To edit the data type format for a table created before the change, drop and re-add the table.

When should I use a yearly or monthly partition strategy?

For Dataverse tables where data volume is high within a year, we recommend you use monthly partitions. Doing so results in smaller files and better performance. Additionally, if the rows in Dataverse tables are updated frequently, splitting into multiple smaller files help improve performance in the case of in-place update scenarios.

When do I use Append only mode for a historical view of changes?

Append only mode is the recommended option for writing Dataverse table data to the lake, especially when the data volumes are high within a partition with frequently changing data. Again, this is a commonly used and highly recommended option for enterprise customers. Additionally, you can choose to use this mode for scenarios where the intent is to incrementally review changes from Dataverse and process the changes for ETL, AI, and ML scenarios. Append only mode provides a history of changes, instead of the latest change or in place update, and enables several time series from AI scenarios, such as prediction or forecasting analytics based on historical values.

Which Dataverse tables are not supported for export?

Any table that does not have change tracking enabled will not be supported in addition to following system tables:

  • Attachment
  • String Map
  • Calendar

Which Dataverse tables use Append only by default?

All tables that do not have a createdOn field will be synced using Append only mode by default. This includes relationship tables as well as the ActivityParty table.

To ensure high performance and low latency in addition to preventing egress charges, Synapse Link requires all resources to be located in the same region. If you have a cross-region scenario, you can:

  • Move the Azure resources to the same region as the environment.
  • Move the environment to the same region as the Azure resources by contacting Microsoft customer support.
  • Enable Read Access - Geo Redundant Storage (RA-GRS) on the Azure Data Lake to replicate Azure data to a near by region. More information: Read access to data in the secondary region
  • Use Azure Synapse pipelines or Azure Data Factory to copy data from Azure resources in one region to Azure resources in another.

Why do I see the error message - cannot bulk load because the file is incomplete or could not be read?

Dataverse data can continuously change through creating, updating, and deleting transactions. This error is caused by the underlying file being changed when you are reading data from it. So, for tables with continuous changes, you should change your consumption pipeline to use snapshot data (partitioned tables) to consume. More information: Create an Azure Synapse Link for Dataverse with your Azure Synapse Workspace