Deletion of records with Azure Synapse Link for Dataverse

[kavyakrishna]

Hi, Is data in sync using Synapse Link for Dataverse?

If the records in Microsoft dataverse database is deleted , does it reflect on Azure database?

Or is it possible to get the list of deleted records?

Thank you!

[dpoggemann]

Hi @kavyakrishna ,

I just set this up and yes the updates to the Synapse environment will handle add, update, deletes and it will also even support schema changes to Dataverse and reflect this to the Synapse environment.

There is also a way to integrate from the Synapse environment to Azure SQL as well following this approach:  https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-pipelines?tabs=s... 

[Balam1984]

Hi @dpoggemann -  Can you pls help me with any samples how to delete records with Azure Synapse Link for Dataverse.

[dpoggemann]

Hi  @Balam1984 

How do you have the integration from Dataverse to Synapse connected?  Are you utilizing the incremental updates?

 

If so then this will actually be "append only" data into the Synapse environment, not an upsert method.  You will see all records in the Synapse for create, every update, and delete and each will be independent records.  You need to configure the integration in the near real-time mode (not incremental updates) if you want to be able to update the records in place and delete like you are probably expecting.  https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-synapse#access-n...

If you did want to keep in append mode you can configure down to an Azure SQL environment from Synapse which will give you the type of data you are looking for (with some delay vs. near real time).  https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-pipelines?tabs=s...

[Balam1984]

@dpoggemann - How to delete unmatched records in Azure SQLDB with Lake Database (Dataverse)

[Balam1984]

Hi @dpoggemann  -  We are not using incremental method. How to identify the deleted rows in Dataverse CRM entity

[dpoggemann]

Hi @Balam1984 ,

If you are using the upsert method vs incremental then the Synapse environment will remove the records when you remove them in your Dataverse environment.  After the delete is processed into Synapse, if you query for the record in Synapse it will not exist.   See details here on in-place updates:  https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-advanced-configu... 

Is this what you are asking?  Sorry if I am misunderstanding.

Thanks,

Drew

[Balam1984]

@dpoggemann  - we are using only upset method and deleting records in CRM D365 and at the same time records removed from Dataverse environment. But the records are not removed in MY Local SQL DB(Azure Synapse). So I need your help for identifying the removed records in My SQL DB (Azure Synapse) vs Dataverse.

[dpoggemann]

Hi @Balam1984 ,

I might be misunderstanding what you are saying, sorry.  You are saying Azure Synapse and My local SQL DB as being the same thing.  These are different things with Azure Synapse supporting a large set of T-SQL commands but it is not a SQL database.  If the setup of your Dataverse to Synapse is setup and the tables are configured to be upsert method then when you query for the record in the Azure Synapse Workspace it should not exist if it was deleted in Dataverse and the update pushed successfully to Synapse.  

Have you connected a SQL database to the Azure Synapse environment through a Synapse or Azure Data Factory configuration?  If you then you need to make sure you follow the steps here to delete the records from the connected Azure SQL environment:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-incremental-updates 

which means the Synapse environment must be in Append Only vs Upsert mode.

[Balam1984]

@dpoggemann  -  How to create Pipeline job in Azure Synapse to Delete records ?