topic Copy data into lakehouse and warehouse - append or overwrite in Copy job

Copy data into lakehouse and warehouse - append or overwrite

DuncanJ — Mon, 14 Jul 2025 15:48:46 GMT

When creating a data pipeline that copies data into a lakehouse, I can choose whether to Append or Overwrite data. If I choose to Overwrite, repeated runs of the pipeline do not create duplicate records.

However, there is no option to Append or Overwrite when creating a data pipeline that copies data into a warehouse. As a result, repeated runs of the pipeline create duplicate records in the warehouse.

Shouldn't the option to Append or Overwrite be present in the warehouse experience too?

Or have I misunderstood the intent? Any wisdom appreciated.

Thank you

Duncan

Re: Copy data into lakehouse and warehouse - append or overwrite

Majid_ — Fri, 14 Jul 2023 11:50:20 GMT

Hi Duncan !

Yes you absolutely right. There is no option to overwrite data like in Data Lakehouse.

In alternative way you can use staging tables to do all transformations.

Re: Copy data into lakehouse and warehouse - append or overwrite

DuncanJ — Sat, 15 Jul 2023 05:40:47 GMT

Thank you Majid.

I don't understand why there is no option to overwrite data though. If it makes sense to have the option with a lakehouse, doesn't it makse sense to have the option with a warehouse? Then it would be trivial to keep the data in sync between e.g. Azure SQL Databases and a warehouse.

In other words, I think it would greaet to have the copy data activity be consistent irrespective of destination ... lakehouse or warehouse ... or am I completely missing the basic concept here? 🙂

Thanks

Duncan

Re: Copy data into lakehouse and warehouse - append or overwrite

Majid_ — Sat, 15 Jul 2023 05:48:46 GMT

Duncan,

I agree with you ) That's why it is in preview mode for now.

I hope the Microsoft team sees your post and fixes this small but very important issue! !

To be honest, I didn't notice it before.)

Re: Copy data into lakehouse and warehouse - append or overwrite

DuncanJ — Sat, 15 Jul 2023 07:44:36 GMT

Thank you Majid!

Re: Copy data into lakehouse and warehouse - append or overwrite

DuncanJ — Sat, 15 Jul 2023 08:59:52 GMT

Continuing to explore this issue by reading

Tables in data warehousing in Microsoft Fabric

Primary keys, foreign keys, and unique keys in Warehouse in Microsoft Fabric

I thought it may be that the copy data into warehouse activity would automatically upsert based on keys in the target, but multiple runs of the pipeline result in duplicated data. May be because the constraints have to be declared as NOT ENFORCED. Makes sense, becaue the Limitations say unique indexes are not currently supported.

Perhaps when the need to declare a primary key as NOT ENFORCED is removed in due course, upsert will happen automatically.

In the meantime, using the copy activity into a lakehouse works as expected. And I can PBI off that.

So much to learn!

Re: Copy data into lakehouse and warehouse - append or overwrite

Majid_ — Sat, 15 Jul 2023 10:13:42 GMT

You welcome, Duncan !

Re: Copy data into lakehouse and warehouse - append or overwrite

NR85 — Fri, 18 Oct 2024 10:01:42 GMT

You could add a TRUNCATE TABLE (TRUNCATE TABLE (Transact-SQL) - SQL Server | Microsoft Learn) statement to the Pre-copy script in the Advanced drop-down of the Destination tab on the Copy data activity. This would remove all rows from the table before writing the new data which would be functionally similar to using an overwrite operation.

Re: Copy data into lakehouse and warehouse - append or overwrite

Anonymous — Fri, 10 Jan 2025 02:21:56 GMT

This worked perfectly for me, had the same problem, I even thought i'd have to create a dataflow for a simple copy and replace job from an excel file to a warehouse table

Re: Copy data into lakehouse and warehouse - append or overwrite

WLY168 — Thu, 13 Mar 2025 10:47:17 GMT

My work around is to use the "pre-copy script". Since my pipeline activity copies multiple tables at the same time, the following works nicely. A truncate might also work.

@concat('delete from dbo.', item().destination.table )