By default Synapse for Dataverse exports records as in-place updates so if the record already exists it gets updated and if not it gets created. This can be configured so that record changes are appended as new records rather than updated. This can be done from the advanced options under Manage tables by selecting show advanced configuration settings and selecting the append-only option. If you are already synching a table you need to unsynch it before changing how it synchs. Note: unsynching a table will delete all its existing data in Azure.
When the Append Only option is configured on for a table every time synchronization occurs the latest modified version of the record is added as a new record to the Synapse table.
I was chatting to somebody recently and they were under the impression that Append acts as an audit trail synchronizing every update but this is not the case. Azure Synapse for Dataverse synchronizes records that have been modified since the last time the synchronization ran. It relies on change tracking to work and this is why you need to enable Track Changes for any tables you want to be synchronized.
I ran some code to update an account record multiple times which you can see in the audit log.
However, when you query the records in synapse you can see that we don’t have every version of the updates. We only have the version of the record at the time the synchronization ran.
How Synapse Link for Dataverse handles deletions
When you delete a record in Dataverse and the table is configured for in-place updates then the corresponding record gets deleted from Synapse. When a table is configured as append-only then deleting a record appends a new record to the Synapse table with the column isdelete populated with the value True.
Couple of things to note about append-only deletes. The first is that the isdelete column is a text column rather than a bool. The second is that none of the data columns on the deleted records are populated so you need to use the id to search for deleted records.
