Working with copy job and creating duplicates

[stiggrr87]

Hi!

I am trying to use copy job and have setting to overwrite data, but when data sinks to lakehouse, it is still being duplicated like it was append setting.

What to do? Not to use copy job and use dataflow gen 2?

[tayloramy]

Hi @stiggrr87, 

Is this a new copy job or an older one? 

Previously only appending records was supprted, but now truncating is also supported, so if you create a new copy job it should be able to truncate the target table. 

https://learn.microsoft.com/en-us/fabric/data-factory/what-is-copy-job#automatic-table-creation-and-...

WHat I've done as a quick workaround for this is to run the copy job from a pipeline, and then before the copy job executes run either a notebook (for a lakehouse) or a script (SQL) to truncate the table first.  

[v-aatheeque · Accepted solution]

Hi @stiggrr87 
Thanks for raising this query in Microsoft Fabric Community Forum.

The overwrite option in Copy Job with Lakehouse may not fully truncate the existing Delta table and can behave like append in some scenarios.

• As a workaround, you can use a pre-copy script (TRUNCATE/DELETE) to clear the table before ingestion. This ensures no duplication. Alternatively you can load into a staging table and use INSERT OVERWRITE logic.
• Dataflow Gen2 is only recommended if you need transformation logic otherwise Copy Job with explicit truncate works best.

Reference : What is Copy job in Data Factory - Microsoft Fabric | Microsoft Learn

Hope this helps !!

Thank You.