ADF Change Data Capture table sync order

Question

We are looking to use the Change Data Capture feature in Azure Data Factory to sync data between 2 Azure SQL databases. However we have hit an issue due to tables having foreign key constraints on the target database.

Sometimes the changes are synced in a different order and we get an error of type 'The INSERT statement conflicted with the FOREIGN KEY constraint' as the parent table entry has not been created before a child entry (for example it may try to enter OrderDetails before the main Order entry has been synced)

Is there a way to control the sync order of the tables using the Change Data Capture feature?

If not can you suggest how we can resolve this issue?

We have worked out all the table precedence's, so just need to be able to tell the Data Factory which order to sync them in.

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

ADF CDC feature does not natively support controlling table sync order, especially in scenarios where foreign key constraints exist between tables. CDC in ADF typically captures changes per table independently, and syncs changes as they arrive, without regard to referential integrity constraints.

This leads to the issue you are facing child rows arriving before parent rows, violating foreign key constraints.

Resolution Strategies

Since you already have the table precedence hierarchy, you can use that to enforce ordering outside the native CDC process.

Option 1: Use Dataflows or Pipelines with Dependency Control

Break down the CDC sync into multiple pipeline activities or orchestrate through parent-child pipelines.

For each table, create a mapping dataflow or copy activity.

Use the ‘Depends On’ feature in ADF to control the execution sequence.

• Apply explicit order according to your foreign key hierarchy (sync Orders before OrderDetails).

Downside: This works best for small to medium scale CDC workloads and batch syncs, not real-time high-throughput CDC.

Option 2: Staging Area with Deferred Constraints

Sync all changes first into a staging schema or table in the target database without FK constraints.

• After all changes are synced, use SQL stored procedures or pipelines to:

Apply transformations

• Load into the final tables in the correct order (based on fk hierarchy)

Do inserts/updates within a single transaction per batch (if feasible)

Benefit: This isolates ordering issues and maintains integrity.

Option 3: Disable and Re-enable Constraints (not recommended for prod)

Temporarily disable FK constraints during sync

Re-enable and validate after sync

Use with caution: this can lead to integrity issues if not carefully managed.

Option 4: Use Azure Databricks or Synapse Pipelines for Custom CDC Logic

If ADF proves too rigid, use Databricks or Synapse with control over ingestion logic.

• You can batch and order CDC events before applying them and use SQL MERGE logic to handle parent-child relationships.

Since you already know the table sync order, I recommend breaking CDC processing into sequential pipeline steps per table, ordered explicitly based on FK dependencies. This gives you full control while still using ADF.

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.