Forum Discussion
Overwrite not working in Copy Data?
I created a Data Copy activity in a pipeline. I accidentally used the "append" and it was duplicating all the data. I had about 14 duplicates of 12 records. I changed it to "overwrite" and I expected it to go back to it's original 12 records since when I looked up what overwrite does it said "Drops existing table/data and creates new.", however it did NOT drop the existing data and create new. I had to completely delete the table to get only 12 records.
Hi alloowishus , Your expectation is understandable, but in Fabric Copy activity the write modes don’t manage existing data, they only control how the incoming batch is written. So once duplicates were created with Append, switching to Overwrite won’t clean up what’s already there because there’s no step that reconciles or removes prior rows.
If you want the table to always reset back to exactly what’s in the source, you need to make that explicit in the pipeline by clearing the target before the load, for example, a SQL/Notebook step to delete or truncate the table, then run the copy. That gives you a true full refresh every time.
If instead you want to prevent duplicates going forward without wiping the table, you should switch to an Upsert pattern with a defined key. That way incoming rows update existing ones instead of being added again, which is the only way to make the load idempotent.
8 Replies
Hi alloowishus , Thank you for reaching out to the Microsoft Community Forum.
In Fabric Data Factory, Overwrite in a Copy activity does not mean drop and recreate the table. It means replace the data being written in that operation, which for Lakehouse/Delta tables is typically file/partition level overwrite. So, when you switched from Append to Overwrite, it only overwrote the incoming write scope and did not remove the previously appended duplicate rows. That’s why the duplicates remained until you manually deleted the table.
If you need a clean reload every time, you should explicitly clear the table first, don’t rely on Overwrite to reset the entire table.
Hi alloowishus ,
I ADF we had option to write script along with write options where we could truncate table and then overwrite, however in fabric, the 'Overwrite' is not Truncating, it is doing the 'OverwriteSchema' which is cleary visible in Json of the copy activity.-tableActionOption.
The Overwrite function in the copy activity also describes its functionality of not overwriting the table but overwriting the schema based on the input columns received.
The soltuion for this is to add a pre-script before this copy activity to Truncate the table and then load it. TRUNCATE TABLE dbo.YourTable;
Hope this helps!
Thanks
Ati Puri
Well then what is the difference between overwrite and upsert? Having to truncate all these tables before hand is something I would expect to be baked in, as it is a very common function when doing ETLs. When I googled the difference between Overwite and Upsert, this is what I get (which I guess is wrong?)
Hi,
The definitions stated above for Overwrite and Upsert is absolutely correct. However, lets try to analyze more for your situation. As per MSFT, the available connectors are as below:
Can you let me know what is your sink for Overwrite? Is it a lakehouse or warehouse table.
Also, for the solution, there are workarounds for the bug in Overwrite function in copy activity.
1. We can remove tableActionOption= 'OverwriteSchema' to 'Overwrite' and it works.
microsoft fabric - Azure Data Factory Copy Activity appends table in sink - Stack Overflow
2. Can refer the previous community post for the same idea which was put across for copy activity issue. Solved: Re: Copy Job OVERWRITE - Microsoft Fabric Community
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
Thanks
Ati Puri
Hi Everyone,
When using Overwrite mode in Copy Activity for a Lakehouse Delta table, Fabric doesn't simply truncate and reload, it issues a DELETE on the existing partition or full table before writing. If the schema or partition hasn't changed, the Overwrite sometimes behaves like Append on the first run after switching modes.
Try these steps:
- In the Copy Activity sink settings, set Write behavior = Overwrite
- Add a Delete activity before the Copy Activity targeting the same table (TRUNCATE TABLE via a Warehouse, or a Notebook step with spark.sql("DROP TABLE IF EXISTS...") + recreate)
- Alternatively, use a Notebook with df.write.format("delta").mode("overwrite").save(...) for full control
If you're targeting a specific partition, make sure partitionBy is consistent mixing partition schemas between runs can cause Overwrite to only affect the matched partition.
Hi alloowishus , Your expectation is understandable, but in Fabric Copy activity the write modes don’t manage existing data, they only control how the incoming batch is written. So once duplicates were created with Append, switching to Overwrite won’t clean up what’s already there because there’s no step that reconciles or removes prior rows.
If you want the table to always reset back to exactly what’s in the source, you need to make that explicit in the pipeline by clearing the target before the load, for example, a SQL/Notebook step to delete or truncate the table, then run the copy. That gives you a true full refresh every time.
If instead you want to prevent duplicates going forward without wiping the table, you should switch to an Upsert pattern with a defined key. That way incoming rows update existing ones instead of being added again, which is the only way to make the load idempotent.
Hi alloowishus ,
hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Hi alloowishus , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.
