Copy Job deceivingly says "Overwrite" when some tables are "Append"

[jisaac]

Hi,

My data analysts noticed some inflated numbers on tables and found sets of duplicated data in their Lakehouse. I use a Copy Job daily to fully copy all data from my GoldLakehouse into their AnalystLakehouse in a separate workspace. The Copy Job is set to Overwrite in the Advanced Settings tab in the ribbon at the top.

Yet, I dive into the JSON for the Copy Job, and it reveals that some of the tables are actually set to Append individually.

There is no setting in the UI that lets you set Overwrite/Append on individual tables, so why does the field exist for individual tables in the JSON? Moreso, how did some of them get set to Append when the only available setting in the Copy Job is set to Overwrite? I believe the ones that got set to Append were the new ones I selected recently to copy. They were inserted as "Append" by default, even though the Copy Job is set to Overwrite. This has to be fixed.

Microsoft, this has caused our reports for C-Suite to be off by potentially millions of dollars for a reason out of our control, this is unacceptable. Please look into this and see who else this is effecting without their notice. Update Copy Job to be without confusing and misleading settings, and please do not push updates to the backend logic before providing UI to manage it. Please provide a stable product.

To the community, has anyone else run into this?

[apturlov]

@jisaac It's unfortunate that you stumbled upon this issue. In the past the Copy Job did have bugs that could have caused such behavior but the known and reported bugs are presumed to be fixed. Since you are working with Lakehouses, the Copy Job would not have been my tool of choice considering that it's not a full-featured orchestration. I would use a data pipeline instead where I can make sure that I perfrom additional pre- and post- steps when updating Lakehouse tables with a Copy activity. It may be beneficial for you to migrate your Copy Job to a data pipeline with a a Copy activity instead and make sure that you also include activities for pre-copy activity to delete/truncate the target tables and/or post activity for the tables optimizations if necessary. If you want to be absolutely in the resulted outcome you may want to switch to ahndling the operations with Notebooks that can also be orchestrated in the data pipeline. Hope this helps and wish you luck.

[Lozovskyi · Accepted solution]

Hello @jisaac ,

Thank you for sharing this issue.

While I haven't encountered this exact behavior myself, I can provide some insight. Behind the scenes, the Copy Job wraps standard copy activities into a user-friendly format, which is why you see individual options per table in the JSON. Theoretically, you can modify these directly, but I strongly advise doing so only for testing purposes.

Additionally, I noticed a potential anti-pattern in your approach: using a copy activity simply to duplicate data into another Lakehouse or workspace. For this scenario, I highly recommend using Shortcuts. Shortcuts will give you immediate access to the exact same dataset effortlessly, without the storage redundancy or compute overhead of copying data.

here you can find more about the shorcuts in OneLake

https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts

Hope this helps!

BR, Taras

[jisaac]

Hi @Lozovskyi ,

We have a ticket on our board to migrate to shortcuts, yes. About a year ago when I set this up, Shortcuts didn't have the full functionality we were needing so I couldn't use them. Since then I believe those issues have been addressed, though, so we'll probably switch soon. Thanks for the insight. 

[v-dineshya]

Hi @jisaac ,

As you mentioned in your previous response, you are migrating to use shortcuts. Please provide ETA(Estimated time for Arrival ) for this thread. Once you have done migration please do let us know,

Regards,

Dinesh