Checkpoint Key on Azure Data Factory (ADF)

Anil Kestur 5 Reputation points
2026-09-02T16:03:08.1133333+00:00

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

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-09-02T16:03:28.5066667+00:00

    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:

    1. 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.
    2. 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.
    3. 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.
    4. 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:

    1. In the mapping data flow source, confirm Enable native change data capture (Preview) is enabled for the Azure SQL MI source.
    2. In the Data Flow activity, confirm the Checkpoint key value is exactly the same on every run.
    3. Do not rename the pipeline, data flow activity, or change the checkpoint key after the first successful run.
    4. Do not rely on debug runs to establish the production checkpoint.
    5. 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:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Rukshan edirisinghe 165 Reputation points
    2026-09-03T02:16:28.57+00:00

    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:

    1. 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.
    2. 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.
    3. 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.
    4. 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.

    Was this answer helpful?

  3. kagiyama yutaka 5,330 Reputation points
    2026-09-03T03:16:51.1666667+00:00

    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.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.