Checkpoint Key on Azure Data Factory (ADF)

Question

Checkpoint Key on Azure Data Factory (ADF)

Anil Kestur 0

Hi,

We have created Azure Data Flow with Azure SQL Database Managed Instance as source and Azure Data Lake Storage Gen 2 (ADLS) as a sink (parquet format).

Change Data Capture is enabled at the source with SQL Server CDC type and "Full on the first run, then incremental".

There is a pipeline which use this Data Flow with the Check Point Key.

Data is extracting (all data) into parquet file, but not as incremental. Could you please suggest if I am missing

CDC is enabled at the database and the table as well. There is no incremental/watermark column ex: modified date etc at the source.

Thanks

Anil

SIVASANKAR YEDDULA 150 Reputation points Microsoft External Staff Moderator

2026-09-03T04:07:24.83+00:00

Hi @Anil Kestur ,

Thank you for reaching out to the Microsoft Q&A forum.

Based on the information provided, the expected behavior of "Full on the first run, then incremental" is that ADF performs a one-time full load and subsequently processes only the changes captured by SQL Server CDC.

Since the entire dataset is being extracted during every execution, the issue is likely related to CDC state tracking or checkpoint persistence rather than the absence of a watermark column. When using SQL Server CDC as the source, a separate incremental column (such as ModifiedDate) is not required because ADF relies on CDC metadata and the checkpoint state to identify new changes.

I recommend validating the following:

Ensure the Checkpoint Key remains consistent across all pipeline executions.

Verify that the checkpoint state is being successfully maintained between runs. If the checkpoint cannot be read or updated, ADF may treat each execution as a first run and perform a full load.

Confirm that new CDC records are being generated in the source change tables after the initial execution.

Review the Data Flow source configuration to confirm that SQL Server CDC mode is selected and functioning as expected.

To help narrow down the root cause, could you please share:

A screenshot of the Mapping Data Flow source CDC configuration.

The Checkpoint Key configuration from the pipeline activity.

Monitoring details from two consecutive pipeline runs.

Confirmation that inserts, updates, or deletes occurred in the source table between those runs.

With these details, we can determine whether the behavior is related to checkpoint management, CDC change tracking, or a configuration issue within the Data Flow.

I hope this helps. Please share the requested information, and we will be happy to investigate further.

If this answer helps, please consider clicking Accept Answer and Upvote, as it may help other community members facing a similar issue.

Based on the information provided, the expected behavior of "Full on the first run, then incremental" is that ADF performs a one-time full load and subsequently processes only the changes captured by SQL Server CDC.

Since the entire dataset is being extracted during every execution, the issue is likely related to CDC state tracking or checkpoint persistence rather than the absence of a watermark column. When using SQL Server CDC as the source, a separate incremental column (such as ModifiedDate) is not required because ADF relies on CDC metadata and the checkpoint state to identify new changes.

Ensure the Checkpoint Key remains consistent across all pipeline executions.

Verify that the checkpoint state is being successfully maintained between runs. If the checkpoint cannot be read or updated, ADF may treat each execution as a first run and perform a full load.

Confirm that new CDC records are being generated in the source change tables after the initial execution.

Review the Data Flow source configuration to confirm that SQL Server CDC mode is selected and functioning as expected.

To help narrow down the root cause, could you please share:

A screenshot of the Mapping Data Flow source CDC configuration.

The Checkpoint Key configuration from the pipeline activity.

Monitoring details from two consecutive pipeline runs.

Confirmation that inserts, updates, or deletes occurred in the source table between those runs.

With these details, we can determine whether the behavior is related to checkpoint management, CDC change tracking, or a configuration issue within the Data Flow.
SIVASANKAR YEDDULA 150 Reputation points Microsoft External Staff Moderator

2026-09-03T11:08:05.2733333+00:00

Hi @Anil Kestur ,

I hope the information provided has been helpful. If you have any further questions or need additional assistance, please let us know in the comments, and we will be glad to help. If this answer helps, please consider clicking Accept Answer so that other community members with similar questions can benefit as well.

3 answers

Your answer

SIVASANKAR YEDDULA 150 Reputation points Microsoft External Staff Moderator

2026-09-03T11:08:05.2733333+00:00

Hi @Anil Kestur ,

I hope the information provided has been helpful. If you have any further questions or need additional assistance, please let us know in the comments, and we will be glad to help. If this answer helps, please consider clicking Accept Answer so that other community members with similar questions can benefit as well.

Answer 1

kagiyama yutaka 5,165

I think ADF native CDC goes incremental when a published pipeline runs again with the same checkpoint key and the SQL MI CDC table has new rows. publish it once, keep one key, change a row, run Trigger again. debug never keeps the checkpoint. only triggered runs reuse it.

0 comments

Answer 2

Hi @Anil Kestur

First, some reassurance: you're not missing a watermark column. Native CDC doesn't need one; that's exactly what it replaces. ADF reads the changes straight from the SQL CDC change tables and manages a checkpoint for you, so each run only picks up what changed since the previous run. When you get a full extract every time, the problem is almost always in how that checkpoint is being handled, not in your CDC setup. Check these in order:

How are you running the pipeline? This is the most common cause. Debug runs keep their own checkpoint, and it resets whenever you refresh the browser during the debug session. And when you first trigger the published pipeline, it starts from the beginning again regardless of what your debug runs did. So to test properly: publish, use Trigger now, let the first run do the full load (that part is by design with "Full on the first run, then incremental"), then insert or update a few rows in the source, trigger again. That second triggered run should output only the changed rows.
Check the Checkpoint Key value on the data flow activity. It has to be a fixed, unique string. If you've put dynamic content in it (run ID, timestamp, anything that changes per run), ADF treats every run as a first run and does a full load each time. Hardcode it once and never change it.
Don't rename things between runs. The checkpoint is tied to the pipeline name and activity name by default, so renaming either resets it and you start from scratch. A custom checkpoint key protects you from this.
Confirm CDC is actually recording changes between your runs. On the Managed Instance, run a quick check against the change table (select from cdc.<schema>_<table>_CT) after modifying rows. If it's empty, the capture job isn't picking up changes and ADF has nothing incremental to read.

In most cases I've seen with this exact symptom, it's number 1: testing through debug or comparing a debug run with the first triggered run. Two consecutive triggered runs with a data change in between are the real test.

Answer 3

For ADF native CDC, incremental behavior depends on the CDC checkpoint state, not on a watermark column. A watermark column is not required when Enable native change data capture is used for Azure SQL MI/SQL DB/SQL Server sources.

Based on the described behavior, the most likely causes are:

The checkpoint is being reset
- By default, the checkpoint is tied to the pipeline name and activity name.
- If either name changes, ADF resets the checkpoint and the next run starts from the beginning or from current changes.
- If a custom Checkpoint key is used, it must stay consistent across runs.
The first published trigger behavior is being misunderstood
- After debugging, when the published pipeline is triggered for the first time, it restarts from the beginning or gets changes from now on.
- Debug runs do not preserve the checkpoint for later published runs.
The pipeline is being rerun from a run whose checkpoint does not represent the expected state
- In Monitor, rerun uses the previous checkpoint of the selected pipeline run.
For ADLS Gen2 sink, native SQL CDC does not automatically imply merge/upsert semantics in files
- The documented SQL native CDC examples describe direct database-to-database replication and ETL in mapping data flow.
- For file sinks such as Parquet in ADLS Gen2, ADF can still read SQL CDC delta rows, but the sink output is just written files. If the expectation is “only changed rows written each run,” the source checkpoint must be preserved correctly.

What to check:

In the mapping data flow source, confirm Enable native change data capture (Preview) is enabled for the Azure SQL MI source.
In the Data Flow activity, confirm the Checkpoint key value is exactly the same on every run.
Do not rename the pipeline, data flow activity, or change the checkpoint key after the first successful run.
Do not rely on debug runs to establish the production checkpoint.
Trigger the published pipeline, then run it again without changing names or checkpoint settings.

Important limitation:

For SQL CDC, ADF loads only net changes from SQL CDC.

If the pipeline still behaves like a full load on every run, the practical fix is to reset to a valid checkpoint strategy:

Keep one stable published pipeline and activity name.
Keep one stable custom checkpoint key.
If the current checkpoint state is invalid or mismatched, update the checkpoint value and test again. A documented Q&A case reports the issue was resolved after correcting the checkpoint value.

References: