Hi @Matthew Samyn
Thanks for the question and using MS Q&A platform.
In Azure Synapse, the ‘upsert’ operation typically refers to an operation that either inserts new rows based on a specified key or updates existing ones. However, it’s important to note that the ‘upsert’ operation in Azure Synapse modifies all records irrespective of the changes in data. This means that even if no changes were made to the data, the ‘upsert’ operation will still overwrite the existing data in the target table.
The upsert
behavior is designed to ensure data consistency and integrity. By updating the entire row, it ensures that the data in Dynamics 365 is always in sync with the source data. However, this behavior can lead to performance issues and data overload, especially if you're dealing with large datasets.
Here's what happens behind the scenes:
- The copy activity reads the data from the source (ADLS Gen2 storage).
- It then compares the source data with the existing data in Dataverse based on the key columns specified in the Dynamics 365 Linked service.
- If a matching row is found, the entire row is updated in Dataverse, even if only one column has changed. This means that all columns, not just the changed ones, are overwritten with the new values from the source data.
- If no matching row is found, a new row is inserted into Dataverse.
This behavior can lead to the issue you're experiencing, where Dataverse gets overloaded with new data every night, even if no changes were made since the last run. To achieve the desired behavior of only updating rows where a column value has changed, you might need to consider using a different approach, such as:
- Using a merge statement in a data flow activity to update only the changed columns.
- Implementing a change data capture (CDC) mechanism to track changes in your source data and only update the changed rows in Dataverse.
- Using a staging table in Dataverse to store the updated data and then use a stored procedure or a data flow activity to update the target table only where changes have occurred.
I hope this information helps, please do let us know if you have any further queries.