Upsert in copy activity

[Mathan1787]

Hi,

could you let me know how can i do upsert  in copy activity of Data pipeline so i could load the data from Azure sql table to lakehouse and store the information as delta table or if there are ways to do similar activity with dataflow gen2.

[carloseparra]

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

[Mathan1787]

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.

[DennesTorres]

Hi,

If you manage to identify an "updatedDate" and/or "InsertedDate" and/or an ascending field, such as Date or ascending numeric key, you can manage to build an incremental load based on them and still keep an image of the records you load every day.

Keeping this image is important, if you really can't find other solution than the upsert, you miss this image. But yes, I understand for this amount, if the incremental load is not possible, at least the upsert maybe needed.

Kind Regards,

Dennes

[DennesTorres]

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