How to extract incremental data from delta files without having any filter condition in data in dataflow adf

Question

How to extract incremental data from delta files without having any filter condition in data in dataflow adf

SaiSekhar, MahasivaRavi (Philadelphia) 140

Hi Team,

Could you please help us to find the solution for below requirement.
we have delta files folder it have 40million records, There is no last modified date column to extract latest data, in dataflow how to extract the latest delta file data and we have to load into azure sql database as incremental load(it should have to handle insert/update/delete),
Could you please share the steps and logic in dataflow and please share the screenshot to implement in dataflow

Thanks in Advance

Vinodh247 33,316 Reputation points MVP Moderator

2025-04-18T16:22:15.27+00:00

Hi ,

Thanks for reaching out to Microsoft Q&A.

To extract incremental data from delta files in ADF Data Flows without having a filter condition like a last modified date, you need to rely on comparing the current snapshot of data in the Delta files with the existing data in the Azure SQL Db. Since Delta Lake maintains versioned files, and assuming change data feed is not enabled, the approach involves reading the latest available data from the Delta files as a full snapshot. This snapshot is then compared with the data that already exists in the Azure SQL db to identify inserts, updates, and deletes. A full outer join is performed on a natural key to detect differences between the two datasets. Based on this comparison, the data is classified into new records to insert, existing records to update, or removed records to delete. These classified records are then loaded into the destination using separate sink transformations in the data flow, with each sink configured for the respective operation. This logic allows you to simulate incremental loading behavior even when there are no filter columns available in the source data.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-18T17:09:21.83+00:00

Hi @Vinodh247,

Thanks for the update,

Could you please share the step and screenshot to implement this logic in dataflow that would helps a lot, to find the latest delta files to load into azure sql, please share the what are components we have to add in dataflow and the logic of it.

Thanks in Advance
Vinodh247 33,316 Reputation points MVP Moderator

2025-04-18T17:32:00.7+00:00
Create a New Data Flow

In the ADF Studio, navigate to the Author tab.

Click on + and select Data Flow to create a new data flow

Add Source Transformation for Delta Files

Click on Add Source.

In the Source settings, choose the dataset pointing to your Delta files stored in adls Gen2.

Ensure the dataset is configured to read the latest snapshot of the Delta table.

Add Source Transformation for Azure SQL db

Click on the + sign after the Delta source and add another Source transformation.

Configure this source to connect to your Azure SQL Database and select the table that holds the existing data.

Add Join Transformation

Click on the + sign after the Delta source and select Join.

Connect the Delta source to the left input of the Join, and the azure SQL source to the right input.

In the Join settings, choose full outer Join.

Specify the join conditions based on the primary key/s that uniquely identify records.

Add Derived Column Transformation

Click on the + sign after the Join and select Derived Column.

Create a new column, for example, OperationType, with logic to determine the type of operation:

If the record exists in Delta but not in SQL, it's an Insert.

If the record exists in both but has differences, it iss an update.

If the record exists in SQL but not in Delta, it's a Delete.

Add Conditional Split Transformation

Click on the + sign after the Derived Column and select Conditional Split.

Configure conditions to split data based on the OperationType column:

Route Insert records to one output stream.

Route Update records to another. Route Delete records to a third.

Add Sink Transformations for Each Operation

For each output stream from the Conditional Split:

Insert Sink:

Connect to the Azure SQL Database. Set the Write behavior to Insert. Update Sink: Connect to the Azure SQL Database. Set the Write behavior to Update. Define the key columns for matching records. Delete Sink: Connect to the Azure SQL Database. Set the Write behavior to Delete. Define the key columns for matching records.

Debug and Test the Data Flow

Turn on the Data Flow Debug mode to test the data flow with sample data.

Ensure that records are correctly classified and routed to the appropriate sinks.

Add Data Flow to a Pipeline

Create a new pipeline or open an existing one.

Drag the Data Flow activity onto the pipeline canvas.

Configure the activity to use the data flow you've created
J N S S Kasyap 2,125 Reputation points Microsoft External Staff Moderator

2025-04-18T19:23:26.0966667+00:00

Just checking in to see if the above answer provided by @Vinodh247 helped.

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.
Vinodh247 33,316 Reputation points MVP Moderator

2025-04-19T10:34:38.2433333+00:00

SaiSekhar, MahasivaRavi (Philadelphia): If this answers your query (including the comments and followup clarification provided for this answer), do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-21T09:32:46.55+00:00

Hi @Vinodh247,
Thanks for the inputs, I'm facing with the below scenario's.
To extract incremental latest delta files in dataflow,
The incremental logic to derive for insert, update and delete, I'm not clear on it, could you please provide the example or screenshot or can you please write peace of code to share with us, it really helps a lot.

Thanks in Advance
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-21T17:00:34.22+00:00

Hi @Vinodh247,

Could you please help with with the above scenario, how to handle it.

Thanks in Advance
J N S S Kasyap 2,125 Reputation points Microsoft External Staff Moderator

2025-04-21T20:48:02.6+00:00

@SaiSekhar, MahasivaRavi (Philadelphia)

Please check below link it will help your scenario's
https://www.mssqltips.com/sqlservertip/8170/incremental-load-using-data-factory/

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 link will helps. Otherwise, will respond with more details and we will try to help.
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator

2025-04-22T06:34:07.47+00:00

@SaiSekhar, MahasivaRavi (Philadelphia)
I just wanted to follow up and see if you had a chance to review above response from J N S S Kasyap to your question. Please let us know if it was helpful, and feel free to reach out if you have any further queries.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-22T11:02:58.57+00:00
Hi @J N S S Kasyap,
Thanks for the suggestion,
Can you please explain me the below points.

Delta files if we are extracting the data as incremental without last modified column filed in data, is it possible to do incremental to azure sql(will the delta log can support this).

Can you provide the logic for incremental update like handling (insert/update/delete) in one dataflow, please provide the logic and code in dataflow to use(The above suggested blog don't have the information).

Thanks in Advance
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-22T13:14:42.6233333+00:00

Hi @J N S S Kasyap,

Could you please provide update on the above request.
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator

2025-04-23T09:28:46.18+00:00

Hello @SaiSekhar, MahasivaRavi (Philadelphia)
Could you please provide the sample Delta file you have been working with, along with a screenshot of the error and details of the exact point where you are facing the issue? I will attempt to reproduce the scenario on my end and get back to you with the findings.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-23T10:39:40.7833333+00:00
Hi Singireddi JagadishKumar,

The folder we have is delta files were saving there, I have created a new dataset with parquet file and in wild card path passing value as *.parquet.
First, can you please confirm this, so that we will proceed further to develop.

an you please explain me the below points.

Delta files if we are extracting the data as incremental without last modified column filed in data, is it possible to do incremental to azure sql(will the delta log can support this).

Can you provide the logic for incremental update like handling (insert/update(without passing all columns in the logic) /delete) in one dataflow, please provide the logic and code in dataflow to use(The above suggested blog don't have the information).
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-23T11:55:03.8133333+00:00

Hi Singireddi JagadishKumar,

can you please provide an update on this please.
Dileep Raj Narayan Thumula 15 Reputation points Microsoft External Staff Moderator

2025-04-24T12:14:48.01+00:00

Hello @SaiSekhar, MahasivaRavi (Philadelphia),

Could you check if the below solution helps you for the incremntal logic you have asked for?

https://stackoverflow.com/questions/69551006/adf-to-snowflake-incremental-load-and-streams

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.

1 answer

Your answer

Vinodh247 33,316 Reputation points MVP Moderator

2025-04-18T16:22:15.27+00:00

Hi ,

Thanks for reaching out to Microsoft Q&A.

To extract incremental data from delta files in ADF Data Flows without having a filter condition like a last modified date, you need to rely on comparing the current snapshot of data in the Delta files with the existing data in the Azure SQL Db. Since Delta Lake maintains versioned files, and assuming change data feed is not enabled, the approach involves reading the latest available data from the Delta files as a full snapshot. This snapshot is then compared with the data that already exists in the Azure SQL db to identify inserts, updates, and deletes. A full outer join is performed on a natural key to detect differences between the two datasets. Based on this comparison, the data is classified into new records to insert, existing records to update, or removed records to delete. These classified records are then loaded into the destination using separate sink transformations in the data flow, with each sink configured for the respective operation. This logic allows you to simulate incremental loading behavior even when there are no filter columns available in the source data.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-18T17:09:21.83+00:00

Hi @Vinodh247,

Thanks for the update,

Could you please share the step and screenshot to implement this logic in dataflow that would helps a lot, to find the latest delta files to load into azure sql, please share the what are components we have to add in dataflow and the logic of it.

Thanks in Advance
J N S S Kasyap 2,125 Reputation points Microsoft External Staff Moderator

2025-04-18T19:23:26.0966667+00:00

Just checking in to see if the above answer provided by @Vinodh247 helped.

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.
Vinodh247 33,316 Reputation points MVP Moderator

2025-04-19T10:34:38.2433333+00:00

SaiSekhar, MahasivaRavi (Philadelphia): If this answers your query (including the comments and followup clarification provided for this answer), do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-21T09:32:46.55+00:00

Hi @Vinodh247,
Thanks for the inputs, I'm facing with the below scenario's.
To extract incremental latest delta files in dataflow,
The incremental logic to derive for insert, update and delete, I'm not clear on it, could you please provide the example or screenshot or can you please write peace of code to share with us, it really helps a lot.

Thanks in Advance
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-21T17:00:34.22+00:00

Hi @Vinodh247,

Could you please help with with the above scenario, how to handle it.

Thanks in Advance
J N S S Kasyap 2,125 Reputation points Microsoft External Staff Moderator

2025-04-21T20:48:02.6+00:00

@SaiSekhar, MahasivaRavi (Philadelphia)

Please check below link it will help your scenario's
https://www.mssqltips.com/sqlservertip/8170/incremental-load-using-data-factory/

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 link will helps. Otherwise, will respond with more details and we will try to help.
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator

2025-04-22T06:34:07.47+00:00

@SaiSekhar, MahasivaRavi (Philadelphia)
I just wanted to follow up and see if you had a chance to review above response from J N S S Kasyap to your question. Please let us know if it was helpful, and feel free to reach out if you have any further queries.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-22T11:02:58.57+00:00

Hi @J N S S Kasyap,
Thanks for the suggestion,
Can you please explain me the below points.

Delta files if we are extracting the data as incremental without last modified column filed in data, is it possible to do incremental to azure sql(will the delta log can support this).

Can you provide the logic for incremental update like handling (insert/update/delete) in one dataflow, please provide the logic and code in dataflow to use(The above suggested blog don't have the information).

Thanks in Advance
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-22T13:14:42.6233333+00:00

Hi @J N S S Kasyap,

Could you please provide update on the above request.
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator

2025-04-23T09:28:46.18+00:00

Hello @SaiSekhar, MahasivaRavi (Philadelphia)
Could you please provide the sample Delta file you have been working with, along with a screenshot of the error and details of the exact point where you are facing the issue? I will attempt to reproduce the scenario on my end and get back to you with the findings.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-23T10:39:40.7833333+00:00

Hi Singireddi JagadishKumar,

The folder we have is delta files were saving there, I have created a new dataset with parquet file and in wild card path passing value as *.parquet.
First, can you please confirm this, so that we will proceed further to develop.

an you please explain me the below points.

Delta files if we are extracting the data as incremental without last modified column filed in data, is it possible to do incremental to azure sql(will the delta log can support this).

Can you provide the logic for incremental update like handling (insert/update(without passing all columns in the logic) /delete) in one dataflow, please provide the logic and code in dataflow to use(The above suggested blog don't have the information).
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-23T11:55:03.8133333+00:00

Hi Singireddi JagadishKumar,

can you please provide an update on this please.
Dileep Raj Narayan Thumula 15 Reputation points Microsoft External Staff Moderator

2025-04-24T12:14:48.01+00:00

Hello @SaiSekhar, MahasivaRavi (Philadelphia),

Could you check if the below solution helps you for the incremntal logic you have asked for?

https://stackoverflow.com/questions/69551006/adf-to-snowflake-incremental-load-and-streams

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.

Answer 1

Aswin 472

Hi @SaiSekhar, MahasivaRavi (Philadelphia)

If you don't have any column to identify the incremental records, the best practice is to use change data capture in mapping data flow.

In source transformation of mapping data flow, click Source options tab.
Then click Enable change data capture check box.
Then select the incremental extract option.

Refer this document https://learn.microsoft.com/en-us/azure/data-factory/concepts-change-data-capture#auto-incremental-extraction-in-mapping-data-flow.

Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator

2025-04-28T09:38:56.4433333+00:00

@SaiSekhar, MahasivaRavi (Philadelphia)
I just wanted to follow up and see if you had a chance to review above response from @Dileep Raj Narayan Thumula and @Aswin to your question. Please let us know if it was helpful, and feel free to reach out if you have any further queries.
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator

2025-04-29T11:24:33.3066667+00:00

Hi SaiSekhar, MahasivaRavi (Philadelphia) I wanted to follow up to see if you had a chance to review my response to your earlier query. Please let me know if it was helpful or if there's anything further I can assist you with.

Share via

How to extract incremental data from delta files without having any filter condition in data in dataflow adf

1 answer

Your answer