Upsert data in adf using dataflow or any other method

Question

Upsert data in adf using dataflow or any other method

Ahsan 20

In ADF, I want to upsert my data. I do not see upsert option in copy activity, so I am using dataflow and alterrow in order to do so, however I do not see the update method in the sink. this is currently what my sink settings look like: User's image

How do I upsert my data using dataflow. sink is writing to adls

Smaran Thoomu 33,920 Reputation points Microsoft External Staff Moderator

2023-12-11T09:21:29.9866667+00:00

Hi @Ahsan ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query.

The upsert operation is not available in the copy activity sink. However, you can use the AlterRow transformation in a data flow to achieve the upsert operation. To achieve this, you can follow the steps below, as I have reproduced the scenario.

If you are using the upsert method in the sink, add alter row transformation with upsert if and write the expression for the upsert condition.

If you are using insert and update as your update method in the sink, then in alter row transformation use both inserts if and update if conditions to insert and update data accordingly into the sink based on alter row conditions.

Reference: upserting data using data flows

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Ahsan 20 Reputation points

2023-12-11T14:24:57.5766667+00:00

I do not see the update method in sink setting. I have a screenshot of my sink settings attached:
Smaran Thoomu 33,920 Reputation points Microsoft External Staff Moderator

2023-12-12T13:23:55.0133333+00:00

Hi @Ahsan
Could you please provide some details about your data flow structure, sink options, and the type of storage account used?
Ahsan 20 Reputation points

2023-12-13T05:51:25.1233333+00:00

The data flow structure consists of four main components: Source1, Flatten1, AlterRow1, and Sink1. Data is initially retrieved from a REST API using Source1, then it undergoes flattening and data transformation in Flatten1 and AlterRow1, respectively. The processed data is ultimately stored in Azure Data Lake Storage (ADLS) in Parquet format using Sink1.

The Sink1 component uses a Blob storage account within Azure Data Lake Storage (ADLS) to store the Parquet files
Smaran Thoomu 33,920 Reputation points Microsoft External Staff Moderator

2023-12-13T07:04:16.7733333+00:00

Hi @Ahsan .

Thank you for providing the information. I think you're using the Azure Data Lake Storage Gen1 sink. Unfortunately, the Azure Data Lake Storage Gen1 sink does not support the upsert operation.

To achieve the upsert operation, you can use the Azure Data Lake Storage Gen2 sink instead. The Azure Data Lake Storage Gen2 sink supports the upsert operation and allows you to select the "Update" method in the sink settings.
Ahsan 20 Reputation points

2023-12-13T07:10:57.0866667+00:00

my sink is actually Azure Data Lake Storage Gen2...
Smaran Thoomu 33,920 Reputation points Microsoft External Staff Moderator

2023-12-14T09:32:04.0733333+00:00
Hi @Ahsan
Thank you for the clarification. I think the "Update method" option is not available in the sink settings of a data flow when using file-type data formats like Parquet, ORC, or Avro. This is because these file formats are optimized for read-heavy workloads and do not support random writes or updates.

In order to achieve the upsert operation with file-type data formats, you can use the following approach:

In the data flow, add the AlterRow transformation after the source transformation.

In the AlterRow transformation, select the key columns that you want to use for the upsert operation.

In the AlterRow transformation, select the columns that you want to update or insert.

In the sink settings, select the "Allow upsert" option.

Here's an example of how to use the AlterRow transformation in a data flow to upsert data into file-type data formats:

source(output( id as string, name as string, age as integer ), allowSchemaDrift: true) ~> AlterRow1 AlterRow1 derive( key as concat(id, name), name as iif(isNull(name), '', name), age as iif(isNull(age), 0, age) ) ~> sink sink(format: 'parquet', filePattern: 'output.parquet', allowSchemaDrift: true, allowUpsert: true)

In the sink settings, select the "Allow upsert" option. This will enable the upsert operation in the file-type data format sink.

For more information, you can refer to the official Azure documentation on upserting data using data flows and using file-type data formats in Azure Data Factory.
Ahsan 20 Reputation points

2023-12-15T07:24:23.8966667+00:00

How am I supposed to do 4. In the sink settings, select the "Allow upsert" option. when the option is not available? Here is a snippet of my dataflow:

and here is the setting of my sink again:

So once again, there is no options in sink settings to allow update or allow upsert.
Smaran Thoomu 33,920 Reputation points Microsoft External Staff Moderator

2023-12-15T14:16:33.35+00:00

Hi @Ahsan ,
Based on the information you provided, it appears that you are using the Parquet file format in your sink. The reason why the 'Update method' or 'Allow Upsert' option is not available in the sink settings of a data flow when using file-type data formats like Parquet is because these file formats are optimized for read-heavy workloads and do not support random writes or updates.