Fabric link to dataverse: tables in fabric contains more records than dataverse

[JVL2]

Hello Fabric colleagues!

I have setup a fabric link connection between dataverse and fabric, a new lakehouse was created in fabric containing shortcuts to the dataverse tables. 

For some tables, the number of records in a table in fabric is higher than those in dataverse.

For example for the ecoresproduct table, normally there is one recid for each displayproductnumber. in dataverse this is the case, in fabric; one specific displayproductnumber has 2 recids. these records already exist since 5/12/'24 (sinkmodifiedon). so the cause is not a syncdelay. Due to this issue, the table in fabric has 5000 rows and the same table in dataverse contains 4999. We expect that a person created an item in dataverse, deleted it and immediately after recreated it, but this is only a guess. The 'IsDelete' value is 'NULL' for the unexpected record.

More important; how can this issue be resolved? Is there for instance a way to - from within fabric - check which record is present in fabric but not in dataverse? Thanks for any update!

[V-yubandi-msft]

Hi @JVL2 ,

we wanted to check in as we haven't heard back from you. Did our solution work for you? If you need any more help, please don't hesitate to ask. Your feedback is very important to us. We hope to hear from you soon.

Thank You.

[V-yubandi-msft]

Hi @JVL2 ,

Thank you for bringing this issue to our attention. In addition to @nilendraFabric  answer, here are a few more points that could be beneficial.

• Utilize Power Query in Power BI to perform an inner join between the Dataverse and Fabric tables on the displayproductnumber field. This will help in identifying records that exist in Fabric but not in Dataverse.
• Establish an automated data synchronization process between Dataverse and Fabric to ensure any changes made in Dataverse are promptly reflected in Fabric, preventing future discrepancies.

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[nilendraFabric · Accepted solution]

Hello @JVL2 

The `IsDelete` column might not update correctly if the synchronization process is incomplete or improperly configured.
• If advanced settings for tracking deletions are not enabled in Azure Synapse Link, deletions might not be reflected accurately

 
please read this discussion:
https://community.fabric.microsoft.com/t5/Fabric-platform/Problems-with-data-history-and-deleted-row...

One effective way in Dataverse is to set up a recurring bulk delete job that targets rows where IsDelete is either `null` or otherwise indicates “orphaned” status.

This might prevent the duplicate records, but not guaranteed.

https://learn.microsoft.com/en-us/power-platform/admin/cleanup-asyncoperationbase-table

Thanks

[JVL2]

thanks @nilendraFabric for your response. in our case we use the append only mode and there are '1' values present in the IsDelete column altough fin some rare case, this is not the case. The same tables are currently exported to Datalake using the export to datalake functionality (which will phase out as of 15/2/'25); there we have no duplicates.

So I do not think we have to change any setting in the 'Azure Synapse link' properties? (correct me if I am wrong).

In the documentation you referred to, I read following info:

We believe that the duplicates recid are indeed caused by a user creating and immediately after deleting something. So it seems that usinf fabric link this delete action is registered (although the isDelete column is null) as a separate record but the export to datalake functionality did not see this delete action.

But still my question remains, how to make sure the IsDelete column is set to 1 for this specific record using fabric link?

[V-yubandi-msft]

Hi @JVL2 ,

Thank you for sharing the information. Please ensure that the advanced settings for tracking deletions are correctly configured in Azure Synapse Link. Verify that these settings are consistent with the append-only mode.

Thank You.