Azure Data Factory: Upsert using Data Flows

Today we will learn on how to perform upsert in Azure data factory (ADF) using data flows

Scenario:

• We will be ingesting a csv stored in Azure Storage (ADLS V2) into Azure SQL by using Upsert method

Steps:

1. Create two connections (linked Services) in the ADF:

       1. One for the csv stored in ADLS

       2. One for the target Azure SQL

                                                 

2. Create two datasets in the ADF:

      1. One for the csv file

             1. Specify the ADLS linked service you created above

             2. Specify the location of the csv stored in ADLS

      2. One for the SQL table 

              1. Specify the SQL linked service you created above

              2. Select the SQL table name where you want to insert/upsert data

              3. In Schema tab, click import schema

3. Create a data flow in ADF:

       1. Drag the source task

             1. Source dataset: select the source data set you created above (Source ADLS)

             2. In source options tab, click After Completion: Move

             3. From: Select the current file location

             4. To: Select dynamic parameter to move the file in the Archive directory

                        by assigning a timestamp to the file (after successful data load)

       2. Drag Sink task

             1. Sink dataset name: select the target dataset name (Target Azure SQL)

             2. In Sink settings tab:

                      1. Select update method: Allow upsert 

                      2. Untick Allow Insert from the update method

                      3. Key column: Primary key of your target table

                       Note: As soon as you select "Allow Upsert", you will notice a new

                                 task "AlterRowCondition" added in between source and sink.

                                 Here you will define the upsert condition.

          3. Click the AlterRowCondition task

                 1. Alter row conditions: Upsertif(): true()

4. Now your data flow is ready and should look like below

5. Create a pipeline and drag the data flow task.

       1. Select the data flow you created above.

6. You are now done. Trigger the pipeline to test the upsert.