Build recoverable Azure Data Factory incremental copy pipelines

1. Choose a change contract that represents the source

Microsoft documents watermark, Change Tracking, and file-based incremental patterns. Select one based on the source’s consistency guarantees, deletion behavior, retention window, and the approved connector. A timestamp field is useful only if every relevant change updates it and the extraction can account for transaction timing and late-arriving data.

A simple increasing ID misses updates to existing rows. Timestamp watermarks miss deletes unless there are tombstones, and a row committed later with an earlier timestamp can fall behind a checkpoint. Use a source-consistent change sequence or snapshot where available; otherwise document an overlap window, deterministic upserts, periodic reconciliation, and the acceptable risk. A tie-breaker resolves pagination within a timestamp, not every late-commit problem.

For SQL Change Tracking or CDC, verify cloud support, enabled tables, version/LSN retention, delete semantics, and the source snapshot procedure. If the consumer falls behind the minimum valid version, perform a controlled full resynchronization instead of silently skipping data. Each runtime, store, and checkpoint database must remain within the approved Moderate boundary.

2. Store checkpoint and batch state outside pipeline variables

Use a protected control table with a unique pipeline/source key, committed cursor, source version, last successful batch ID, and commit time. Keep a separate batch record for the immutable lower and upper bounds, schema version, staging location, and publication status. Pipeline variables alone do not survive a failed run as a reliable recovery contract.

Set the pipeline’s concurrency to 1 for the simple single-source pattern and serialize batch creation in the control store. Pipeline-level concurrency does not prevent a second pipeline from modifying the same cursor. Use a lease or transactional lock and a compare-and-swap commit in the store. Each batch reuses its own bounds and identifier when retried.

3. Extract a bounded window using approved SQL

Capture the upper bound once for the batch. The illustrative SQL below assumes a reliable UpdatedUtc change field and a source-consistent read strategy. Use a prepared statement or reviewed stored procedure with typed parameters in the connector/API that supports it. This is SQL logic, not a claim that every ADF activity can bind these placeholders automatically.

-- @LowerUtc and @UpperUtc are typed datetime2 parameters.
SELECT RecordId, UpdatedUtc, ApprovedValue
FROM ingest.ApprovedExport
WHERE UpdatedUtc > @LowerUtc
  AND UpdatedUtc <= @UpperUtc;

Never interpolate arbitrary caller-provided table names, predicates, or SQL. For metadata-driven ingestion, map a reviewed source key to an allowlisted stored procedure or object; QUOTENAME can quote an identifier but does not authorize it. Freeze the source mapping, schema, bounds, and destination path with the batch record.

If you cannot obtain a source-consistent read across a large extraction, use a source snapshot or retention-backed change feed and document the transaction behavior. A MAX(UpdatedUtc) lookup followed by an unrelated read is not a transactionally consistent snapshot. Changes that move while the query is executing need an explicit reconciliation strategy.

4. Stage, validate, and make publication idempotent

Copy to a stable batch path such as staging/source-key/batch-id/; do not publish a new random path for each retry of the same logical batch. Keep a manifest of expected outputs and distinguish partial staging files from a committed batch. Use a unique batch/key constraint in the target and a deterministic update/insert procedure for mutable rows.

Validate schema, required fields, row counts, key uniqueness, and the chosen business reconciliation totals before publishing. Count-only comparisons can miss a dropped row balanced by a duplicate. Add key-level or digest-based reconciliation where the data contract requires it; keep digests and manifests protected and avoid putting sensitive fields into logs.

For a SQL sink, use a reviewed transactional procedure to merge the validated batch and mark it published. For a data lake, publish a committed manifest only after all expected objects are present; an arbitrary Copy operation across stores is not one distributed atomic transaction. An incomplete staging prefix remains uncommitted and subject to cleanup retention.

5. Advance the checkpoint only after verified publication

Use explicit ADF success dependencies: extraction and copy must succeed, validation must pass, and publication must succeed before the checkpoint activity runs. A failure branch records sanitized batch status and alerts, while preserving the old committed cursor. The following T-SQL is a commit-logic excerpt; the referenced schema, unique keys, and publication checks must be implemented separately.

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE ops.IngestionCursor WITH (UPDLOCK, HOLDLOCK)
SET WatermarkUtc = @UpperUtc,
    LastBatchId = @BatchId,
    CommittedUtc = SYSUTCDATETIME()
WHERE SourceKey = @SourceKey
  AND WatermarkUtc = @ExpectedLowerUtc;

IF @@ROWCOUNT <> 1
    THROW 50001, 'Checkpoint conflict; reconcile before replay', 1;

UPDATE ops.IngestionBatch
SET Status = 'Committed'
WHERE BatchId = @BatchId AND Status = 'Published';

IF @@ROWCOUNT <> 1
    THROW 50002, 'Batch publication has not been verified', 1;

COMMIT TRANSACTION;

Wrap this logic in a tested stored procedure with transaction error handling and unique constraints. After a timeout, query the committed batch state before retrying; the first attempt might already have committed. An already-committed matching batch should return a safe success, while mismatched bounds require investigation. Advancing the watermark in a completion/finally branch can skip data after failures.

6. Test late data, deletion, and interrupted commits

• Insert two rows sharing a timestamp, update an older row, and commit a delayed transaction; verify the chosen contract captures each change.
• Delete a source row and prove that its tombstone or change feed reaches the target.
• Fail after extraction, after partial sink write, after publication, and after checkpoint commit; replay the same batch and verify one logical target result.
• Start competing writers for the same cursor and verify the lease or compare-and-swap prevents unintended advancement.
• Simulate expired change retention and require full resynchronization.
• Restore control-state backups and reconcile their cursor against published data before re-enabling triggers.

Record bounded lag, reconciled keys, batch transitions, and replay outcomes without payloads. Use controlled deployment to release changes to this state contract, and monitoring and alerting to detect stalled cursors.

Sources and technical review

Technical review: October 7, 2026. Recheck cloud, regional, connector, and authorization scope before implementation.

• Incremental loading patterns and deletion-aware Change Tracking
• Watermark workflow with Lookup, Copy, and checkpoint update
• Pipeline concurrency and activity dependencies
• Copy activity behavior and supported sources/sinks

Public offering and service-scope checks are documented in the Data Factory hub. The protected provider authorization package and live Azure behavior have not been reviewed or tested by these guides.