Copy data into lakehouse and warehouse - append or overwrite

[DuncanJ]

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

[DuncanJ]

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!

[DuncanJ]

Thank you Majid!

[Majid_]

You welcome, Duncan !

[Majid_]

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.

[DuncanJ]

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

[Majid_ · Accepted solution]

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.)