Upsert in copy activity

Hi!

Some connectors allow the upsert, as you can notice on the image below. I don't remember if it's available for Delta on pipelines.

However, it makes sense to do it in dataflows (although, of course, every situation is unique).

Usually, on the pipeline, you are getting external (or "external") data to your lake. I believe it's a good practice to keep each load as an individual set, in a specific folder on the initial layers. For example, every day you will load a set of records. On the first layer of the lake, you keep them together, the original set you loaded. Leave the upsert to do later.

In this way, if you need to find out "where/when this was loaded from?" or "When this value became this?" it's possible to track down to a specific date when that information was loaded into the lake. 

In this way, you can just use copy in the pipeline, creating daily new folders, and make the upsert later, from these daily new folders to the table itself, using a dataflow.

Kind Regards,

Dennes

I am planning to sort the upsert with notebook as copying the entire 2 billion records would be very costly. it would be nice if there is a incremental loading of data or cdc.

That screenshot is very misleading Dennes. That is Azure Data Factory. We need Fabric.