Forum Discussion
Duplicate rows via a staging table
Hi ToddChitt,
Thank you for reaching out to Microsoft Fabric Community Forum.
Verify whether the FULL and INCREMENTAL loads are overlapping or interfering with each other. If the FULL load takes longer than expected, an INCREMENTAL load might begin while the table is still being truncated and reloaded, potentially causing duplicates.
If there is no overlap and the issue persists, I recommend raising a Microsoft Fabric support ticket for further investigation.
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
Regards,
Vinay Pabbu
Hello FabianSchut and thank you for your prompt reply. To answer your questions:
There are no parallel processes taking place. We have been careful to space out the triggers and schedules so that there are no overlaps. For sure there is nothing else WRITING or INSERTING into this table at that time.
The key field is named [rowpointer] and is a GUID. If I do a GROUP BY [rowpointer] HAVING COUNT(*) > 1 on the staging table, I get zero instances. If I do the same on the destination table, I get 6K+ pairs. The stored procedure that does the UPDATE/INSERT process has JOIN ON that [rowpointer] field. There are no other joins to any other tables during the INSERT that could potentially cause duplicated records.
I check only the key field, not the entire row. Everying keys off that key field. It is UNIQUE in the source SQL database.
The ONLY thing I can see, and this is a BIG stretch, is that the Azure Data Factory task that does the MERGE procedure has a Retry of 1 iteration, set to 30 seconds intervals. I have set that to 0 for now.
And what about the possibility that a record is inserted that has a NULL value as key column? Is there any check on the key column that is should not contain NULL values? Looking at your upsert script, that could potentially create duplicates too.