How to create a incremental pipeline in dataflow need to delete records if that is not present in source

Hi @SaiSekhar, MahasivaRavi (Philadelphia)

You can absolutely implement Insert, Update, and Delete logic using Change Data Capture (CDC) in Azure Synapse Analytics. CDC is a very efficient way to handle incremental data loads, especially with large datasets like your 40 million records.
Using CDC simplifies the process of detecting changes (inserts, updates, deletes) compared to a watermark-based approach, as it leverages metadata about changes in the Delta file.

Performance Optimization for 40 Million Records:

Use Hash partitioning on ID in the Delta CDC source and partition the Azure SQL table by ID or LastModifiedDate
Configure Azure Data Lake Storage Gen2 as a staging area in the Azure SQL sink to minimize direct database load.
Increase Azure SQL DTUs/vCores (e.g., Premium tier) and Data Flow compute size (e.g., Medium/Large) with Enhanced compute.
Set the sink Write batch size to 10,000–100,000 rows for efficient processing.
Create a clustered index on ID; disable indexes during loading and rebuild afterward using pre/post-SQL scripts.
Run OPTIMIZE and apply Z-Order indexing on ID in the Delta table to enhance read performance.

Please refer below resources to implement the Insert, Update, and Delete logic using Change Data Capture (CDC) in Azure Synapse Analytics.
https://www.youtube.com/watch?v=YTqu4Ppg66c
https://www.youtube.com/watch?v=02omAy7q2Zw&list=PL6Y5avAYJUaTBaBL0em6qyz6mTVLCiX6P&index=33
Disclaimer: This response contains a reference to a third-party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

 I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.

Hi @J N S S Kasyap,
Thanks for the update,

The above videos they were doing for Azure SQL to SQL database, but my requirement is to delta file to Azure SQL, can you please provide is there any solution to do full load in more faster way or can you please provide the steps to do for incremental load that would be more helpful.
we have around 40million rows in delta file, the count will increase on daily basis, if we want to do full load it is taking more than 4 hours(can you please tell is there any techniques to increase the performance)
or can you please tell is there any way to do incremental load for 40million rows which will handle insert, update and delete and please let us know will it increase the performance faster comparative to full load, please help with the solution.

Thanks in Advance

Hello @SaiSekhar, MahasivaRavi (Philadelphia)

To perform an incremental data load from a Delta file to Azure SQL using Synapse Data Flows, a recommended approach involves using a Full Outer Join between the source (Delta) and the target (Azure SQL).

This allows efficient handling of insert, update, and delete operations, even with large datasets exceeding 40 million records. After the join, a Derived Column or Conditional Split is used to identify row types—whether they are inserts, updates, or deletes.

The Alter Row transformation is then applied to route each row type accordingly. The data is finally written to the Azure SQL sink with upsert and delete operations enabled using a key column.

For performance optimization, you can apply partitioning on your preferred partitioning column both the source and target, filter the source data using the ModifiedDate column to minimize load volume, and use a compute-optimized Integration Runtime.

Additionally, tuning the sink settings such as batch size and degree of parallelism is crucial for handling high-volume loads effectively. For further guidance, refer to resources such as this tip on incremental upserts, this article on incremental loads, and this Synapse-focused guide.
Disclaimer: This response contains a reference to a third-party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Hi @Singireddi JagadishKumar,

Thanks for the update,

Can you please provide code for the insert, update and delete logic in alter row what we need to write after full outer join,
s there any way in dataflow to extract incremental data.
Eg: correct me if I'm wrong in the below screenshot there is delta file path for April 20th there are three files, can we process the three files as incremental, those are having latest data, the table having 40million rows but April 20th process data in those 3 files it won't have 40 million records
it will have latest data in those can we process those data as incremental into azure sql.
Can you please provide code to extract latest file data from delta file and load into azure sql using (dataflow or any other way), it need to handle insert, update and delete, is this scenario is achievable or not, can you please confirm.

Filter by File Timestamp (in Data Flow Source) If the Delta files have a Last Modified Timestamp In your Data Flow source, select wildcards Use a Filter transformation after the source to get only files from April 20:

toDate(toTimestamp(lastModified)) == toDate('2025-04-20')

lastModified is an implicit metadata field in Synapse Data Flow sources if you enable metadata columns. Full Outer Join and Alter Row Code Join Type: Full Outer Join on ContactId Add Derived Column rowAction

Use a Derived Column transformation after the join with the following expression:

  iif(isNull(src.ContactId), 'Delete',
        iif(isNull(tgt.ContactId), 'Insert',
            iif(src.Name != tgt.Name || src.Email != tgt.Email, 'Update', 'NoChange')
        )
    )

Call this column: rowAction Alter Row Expression (in Alter Row Transformation) Operation

Expression Insert rowAction == 'Insert' Update rowAction == 'Update' Delete rowAction == 'Delete' You configure this directly in the Alter Row transformation’s settings. Sink Configuration (Azure SQL) Sink Type: Azure SQL Enable: Upsert (for insert/update) Delete Row (for delete) Set Key column(s) = ContactId

Alternative Way to Load Incremental Files Only Instead of filtering in Data Flow, you can: Use a Get Metadata activity in pipeline to get file timestamps Filter file paths with LastModified >= pipeline(variables.lastRunDate) Pass filtered file paths to Data Flow via parameters