Near Real-Time Ingestion to Synapse Using CDC and ADF
You are designing a near real-time analytics solution for a retail company. The company has a transactional database in Azure SQL Database that records sales transactions. The data must be available in Azure Synapse Analytics dedicated SQL pool for reporting with less than 15 minutes of latency. The data volume is about 10 GB per day. You need to design the data ingestion pipeline. You also need to ensure that the pipeline can handle schema changes (e.g., new columns added to the source table) without manual intervention. Which approach should you use?
Quick Answer
This solution addresses the two distinct requirements in the scenario with two distinct features, rather than trying to solve both problems with one mechanism. Change Data Capture in a mapping data flow reads only the rows that have changed in the source since the last run, instead of re-extracting the whole table, which is what makes it realistic to meet a 15-minute latency target against a database that is still receiving new transactions continuously, and at only 10 GB per day, incremental CDC comfortably keeps up. Schema drift is a separate setting that solves a separate problem: it lets the data flow adapt automatically when the source table's structure changes, such as a new column being added, without requiring someone to go back and manually update the pipeline's schema mapping. Without schema drift enabled, a new column at the source would either be silently dropped or cause the pipeline to fail once its rigid mapping no longer matches reality. Recognize this pattern when a scenario names both a low-latency, incremental-load requirement and a need to tolerate source schema changes without manual updates; those are two separate capabilities to configure, not one, and Data Factory's CDC-enabled mapping data flow paired with schema drift is built to satisfy exactly that combination.
⚠ Common exam trap
Watch out — candidates often choose Azure Databricks with Auto Loader (Option A) because it is associated with handling schema evolution, but they overlook that Auto Loader is file-based and not designed for direct CDC from Azure SQL Database, making it unsuitable for this transactional source.
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
✓
Use Azure Data Factory with a change data capture (CDC) mapping data flow to read changes from SQL Database and write to Synapse dedicated SQL pool. Enable schema drift to handle new columns.
Azure Data Factory's mapping data flows support Change Data Capture (CDC) to incrementally load only changed rows from Azure SQL Database, meeting the <15-minute latency requirement for 10 GB/day. The 'Enable schema drift' option in mapping data flows automatically handles new columns added to the source table without manual intervention, which is essential for schema evolution.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use Azure Databricks with Auto Loader to read from SQL Database and write to Synapse using Delta Lake.
Why it's wrong here
Auto Loader is for files, not databases.
- ✓
Use Azure Data Factory with a change data capture (CDC) mapping data flow to read changes from SQL Database and write to Synapse dedicated SQL pool. Enable schema drift to handle new columns.
Why this is correct
CDC provides incremental changes; schema drift allows automatic handling of new columns.
- ✗
Use Azure Data Share to share the SQL Database tables with Synapse and schedule snapshots every 15 minutes.
Why it's wrong here
Data Share is for sharing, not for ingestion with schema drift.
- ✗
Use Azure Synapse Pipelines with a copy activity to perform a full load every 15 minutes.
Why it's wrong here
Full loads every 15 minutes are inefficient and may exceed SLA.
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
One of 760 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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 near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?
hard- A.Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.
- ✓ B.Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.
- C.Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.
- D.Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.
Why B: Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.
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.