Forum Discussion
QUESTION::PIPELINE::COPY DATA::DUPLICATE DATA COPY
Here is a workaround originally shared by Mark Pryce-Maher, the
Microsoft PM responsible for the synchronisation between lakehouse and SQL endpoint. As he says: "the following solution is unofficial, unsupported, undocumented, and frankly unwise!"
However, I would not call this a solution since it is unofficial, unsupported, undocumented, and unwise. Anything that is unofficial, unsupported, undocumented, is precisely unwise because it could change at any time without any notice to the user, who will be much bewildered when stuff suddenly breaks for no apparent reason.
Mark's original Python code: Workaround for delays in the automatically generated schema in the SQL analytics endpoint of the Lakehouse in Fabric | by Mark Pryce-Maher | Medium
An improved version of this code by Andre Fomin: Fix SQL Analytics Endpoint Sync Issues in Microsoft Fabric – Data Not Showing? Here's the Solution! — obvience
For now, this Python script seems to work and the problem I had due to the sync issue has not manifested itself... yet.
Hi Element115,
Thank you for reaching out through the Microsoft Fabric Community Forum.
We sincerely appreciate you reporting this issue and sharing your detailed observations. We understand how frustrating it can be to encounter unexpected duplicate records in your pipeline, especially when it is designed to be deterministic. Your expectation of consistent results from incremental loads is absolutely valid.
Based on our understanding, this behaviour could be occurring due to factors such as:
• Parallel writes in the Copy Data activity,
• Improper use of the Append mode without handling duplicates,
• Inconsistent incremental load filters (for example, missing watermarks), or
• Absence of constraints (such as primary keys) on the destination.
As you have already raised a support ticket, the engineering team will analyse the backend logs and provide specific insights related to your account and possible resolutions.
Kindly keep us informed of any findings, as they may prove helpful to other members of the community.
If you find our response helpful, we request you to mark it as the accepted solution and consider giving kudos. This will assist other community members who may have similar queries.
Thank you once again.
1. What parallel writes? I didn't change the default setting, which is Auto, as shown below in the screenshot.
But are you saying, that with the default Auto setting, Copy Data is free to start writing to the lakehouse table with more than 1 thread AND mistakenly DOES NOT keep track of the rows one thread already wrote or is in the process of writing to the table?
2. What do you mean by "Improper use of the Append mode without handling duplicates"? How could there be an improper use of Append or Overwrite modes when the UI only gives you the choice of selecting one or the other and your pipeline logic depends on a T-SQL script using a column of unique IDs to SELECT rows coming after the last ingested ID (greater than operation), thus the result set should be a set of sequential rows according to the ID identity column imported from the source.
3. What does that even mean? " Inconsistent incremental load filters (for example, missing watermarks)." Watermarks? What watermarks? And again: the incremental filter is based on an identity column of unique IDs. Nothing inconsistent here.
4. There are no primary key constraints on a lakehouse delta table. Or did I miss something and this is also a feature like in a SQL DB?
Furthermore, I let the pipeline run on an hourly schedule since last Friday. I did a check today for duplicate rows in the destination table. Here is the screenshot showing both the T-SQL script used and the result. Please note the count is 8 but should be 4. Also note the times and the interval between times for each occurrence. There is no fixed pattern.