Change Data Capture from SQL Server to Azure using Azure Data Factory
You are designing a change data capture (CDC) solution to incrementally load data from an on-premises SQL Server database to Azure Synapse Analytics. The source tables have no timestamp columns and you cannot modify the schema. Which Azure service should you use?
Quick Answer
The answer is Azure Data Factory with change tracking. This is the correct choice because it leverages SQL Server’s built-in change tracking feature to capture row-level inserts, updates, and deletes without requiring timestamp columns or any schema modifications to the source tables. For the Microsoft Azure Data Engineer Associate DP-203 exam, this scenario tests your understanding of incremental loading strategies when source constraints exist—a common trap is reaching for timestamp-based CDC or third-party tools, but the key insight is that SQL Server change tracking operates transparently at the table level. Remember the mnemonic “No Timestamp? Track Changes” to recall that Azure Data Factory’s change tracking is purpose-built for exactly this constraint. This approach reliably feeds incremental data into Azure Synapse Analytics, making it the ideal solution for the exam’s typical “on-prem SQL Server to Synapse” CDC question.
⚠ Common exam trap
A common mix-up: candidates assume a timestamp column is mandatory for incremental loads, but Azure Data Factory's change tracking connector bypasses this requirement by using SQL Server's built-in change tracking mechanism, which does not require any schema modifications.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Azure Data Factory with change tracking
Azure Data Factory's change tracking capability is the correct choice because it can capture row-level inserts, updates, and deletes from SQL Server without requiring timestamp columns or schema modifications. It uses SQL Server's built-in change tracking feature, which tracks changes at the table level and provides a reliable incremental load mechanism to Azure Synapse Analytics.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Azure Synapse Pipelines with mapping data flows
Why it's wrong here
Synapse Pipelines can do CDC but ADF is the more common and flexible choice.
- ✓
Azure Data Factory with change tracking
Why this is correct
ADF can enable change tracking on SQL Server or use custom watermark logic.
- ✗
Azure Databricks with Auto Loader
Why it's wrong here
Auto Loader requires file-based sources or change data feed.
- ✗
Azure Stream Analytics
Why it's wrong here
Stream Analytics is for real-time streaming, not batch CDC.
Visual reference
Go deeper
Related to this question
Learn chapter
Introduction to Azure Data Engineering
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
Key term
Azure Data Factory
Azure Data Factory is a cloud-based data integration service that lets you create, schedule, and orchestrate data pipelines to move and transform data from various sources to destinations.
About these practice questions
Courseiva writes every DP-203 question from scratch — 760 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-203
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are designing a change data capture (CDC) pipeline to ingest incremental changes from an on-premises SQL Server database into Azure Data Lake Storage Gen2. The pipeline must run every 5 minutes and handle high-volume DML changes. Which Azure service should you use to capture the changes with low latency?
medium- A.Azure Data Share to share the SQL Server data and capture changes.
- ✓ B.Azure Data Factory with a change data capture (CDC) source in the mapping data flow.
- C.Azure Synapse Pipelines with a copy activity that uses a query to capture changes.
- D.Azure Databricks with Auto Loader and Delta Live Tables to capture changes.
Why B: Azure Data Factory's mapping data flow includes a native CDC source that can connect to SQL Server and capture incremental DML changes (inserts, updates, deletes) with low latency. This approach uses change tracking or change data capture features in SQL Server to identify changes, and the pipeline can run every 5 minutes to meet the high-volume requirement without custom coding.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.