How can I use the Incremental Column property in the Sink activity in Azure Synapse Analytics Data Flow?

Question

How can I use the Incremental Column property in the Sink activity in Azure Synapse Analytics Data Flow?

Burak Yoleri 0

I am trying to transfer data from my source table to my destination table within a Data Flow. Since my table contains a large volume of data, I attempted to use the Incremental Column feature shown in the screenshot below by selecting a suitable column from the table.

However, after debugging and reviewing the process, I noticed that instead of reading only a specific part of the data as expected, the Data Flow reads the entire table every time. This behavior is not suitable for my use case.

My intention is to select a date column as the incremental column and use the watermark feature so that only newly added records are read and processed in the pipeline.

I would appreciate your assistance regarding this issue.

Thanks,

Burak Yoleri

Burak Yoleri 0 Reputation points

2025-04-17T17:06:48.6366667+00:00

1 answer

Your answer

Burak Yoleri 0 Reputation points

2025-04-17T17:06:48.6366667+00:00

Answer 1

J N S S Kasyap 1,620 Microsoft External Staff

Hi @Burak Yoleri

The screenshot shows the "Incremental column" set to LastModifyDate and "Run mode" to "Full on the first run, then incremental," which is correct. However, the watermark logic (e.g., a filter like WHERE LastModifyDate > '@{pipeline().parameters.watermarkValue}') must be explicitly defined in the "Query" tab of the Source. If this filter is missing or not parameterized with the watermark value, the Data Flow will read all records.

The "Schema name" (DWH) and "Table name" (testcity3) are specified, which seems correct. However, if the LastModifyDate column doesn’t exist or contains inconsistent data (e.g., NULL values or non-date formats), the incremental logic might fail, causing a full table read.

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.

Burak Yoleri 0 Reputation points

2025-04-18T06:20:11.29+00:00

Hi @J N S S Kasyap ,

I want to proceed directly using incremental column in the pipeline. I know that I don't have to use Watermark. When I checked the LastModifyDate values, I tried the column type as datetime or timestamp. There is no null value in the data in the column and all of them are in accordance with the datetime type. However, despite these, the process continues by reading all the data during execution.

Thanks.
J N S S Kasyap 1,620 Reputation points Microsoft External Staff

2025-04-19T00:42:24.6433333+00:00
@Burak Yoleri

1.How are you triggering the Data Flow?

Debug mode?

Manual trigger from a published pipeline?

Scheduled trigger?

2.Is "Enable staging" turned on in the sink?

Azure Synapse Analytics does not automatically persist the state of the last processed value for incremental columns between pipeline runs.
When using the Incremental Column feature without explicit filtering logic, the Data Flow may rely on runtime memory to track the maximum value of the incremental column.
However, this state is not stored persistently across executions. Therefore, to achieve consistent incremental loading, you should manage the state externally by storing the last processed value in a control table or pipeline parameter and applying it through a custom query.
Alternatively, if managing state externally is not feasible, you may need to accept a full load on every new deployment and then use the "Incremental only" run mode for subsequent runs.

I hope this info helps. Please help me answer the above questions and Try with "Incremental only" run mode once please check if you are facing the issue.
J N S S Kasyap 1,620 Reputation points Microsoft External Staff

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

@Burak Yoleri
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Burak YOLERİ 20 Reputation points

2025-04-21T19:23:12.78+00:00

Sorry for latency.

1.How are you triggering the Data Flow?

Answer: I performed my tests by running more than 3 methods in debug mode.

2.Is "Enable staging" turned on in the sink?

Answer: Yes, as you can see in the screenshot I shared, I am running my tests with the enable staging feature active.

I will explain it again very simply. There are 10 rows in my table. Let's say these are EtlDateTime(datetime), Id(int), Name(nvarchar) columns. I run my source table to my target table with the upsert method, first time with the Full on the first run, then Incremental (I select the EtlDate column which is the incremental column) feature selected (as seen in the screenshot) in debug mode. As expected, I can write 10 rows of data to my target table (the column structure is the same as my source table) without any problems. Then I manually insert 1 row of data into the source table. In this row, I set the value in my EtlDate column to be 1 day later than the date value in the other 10 rows. Then I run the pipeline again. My expectation is that it will read only 1 row of data from the source, but for some reason it reads all 11 rows in the source. This is a situation I do not want.
Burak YOLERİ 20 Reputation points

2025-04-21T19:32:34.82+00:00

By the way, i also tried with incremental changes only option but it is same result on the screen.
J N S S Kasyap 1,620 Reputation points Microsoft External Staff

2025-04-23T00:01:21.9+00:00
@Burak YOLERİ

Debug mode in Azure Synapse Analytics Data Flows does not persist state for the incremental column (EtlDateTime in your case). This means that every time you run the Data Flow in debug mode, it cannot track the last processed EtlDateTime value from the previous run. As a result, the Data Flow behaves as if it's the first run, reading all rows (11 rows in your case) instead of just the new row (1 row with a later EtlDateTime).

Please follow the below steps:

1.Publish your Data Flow.

2.Create a pipeline to call the Data Flow.

Run the pipeline outside debug mode (via a manual or scheduled trigger).

I hope this information helps. Please do let us know if you have any further queries.
J N S S Kasyap 1,620 Reputation points Microsoft External Staff

2025-04-23T16:31:01.08+00:00

@Burak Yoleri
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

How can I use the Incremental Column property in the Sink activity in Azure Synapse Analytics Data Flow?

1 answer

Your answer