(+351) 21 24 10006  ·  info@bconcepts.pt
Carnaxide, Lisbon

DP-900: how to understand and use Change Data Capture in Azure

João Barros 22 de November de 2026 4 min read

I will teach the skill: understanding and using Change Data Capture (CDC) in data solutions on Azure. CDC is important for the DP-900 exam because it demonstrates understanding of incremental ingestion and integration between systems — and, in practice, reduces latency and cost by capturing only the changes.

What you need to know

Change Data Capture (CDC) is a technique to record changes (inserts, updates, deletes) in a data source so other applications can process only those changes. Instead of reading the entire table, we read incremental changes. In the Azure ecosystem, CDC can be implemented with technologies such as Azure SQL Database (built-in CDC), SQL Server on virtual machines, Azure Data Factory / Synapse Mapping Data Flows for incremental ingestion, or tools like Azure Event Hubs and Azure Data Lake when using streams.

Conceptual example: you have an Orders table in an Azure SQL Database with thousands of rows. A daily full-load ETL process reads everything — it costs CPU, I/O and time. With CDC, each change to the table (INSERT/UPDATE/DELETE) is recorded in an accessible log. A pipeline reads that log and applies only the changes to the analytical warehouse, reducing time and cost.

How it works in practice

The typical implementation involves 3 components: the source with CDC enabled; a mechanism to read the changes; and the destination that applies the changes. Practical steps in a common scenario (Azure SQL Database → Azure Data Factory → Azure Synapse / Data Lake):

  1. Enable CDC on the database and the table (Azure SQL Database): this creates capture tables and records the LSN (log sequence number) for changes. In SQL Server/managed instance or Azure SQL, use the built-in functions and procedures to enable CDC.
  2. Detect the checkpoint: the processor stores the last LSN read to continue incrementally.
  3. Read the changes: an ADF pipeline or a custom process queries the CDC tables or uses functions that return the rows changed since a specific LSN.
  4. Apply to the destination: at the destination (for example, a table in Synapse), apply INSERT/UPDATE/DELETE according to the operation type; you can use MERGE to simplify.
  5. Store the new checkpoint: record the most recent processed LSN for the next run.

Conceptual query example to read changes (simplified):

-- Pseudocode conceptual; in a real environment use the built-in CDC functions
SELECT *
FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn, @to_lsn, 'all')

In Azure Data Factory you can use the "Incremental copy" feature or a Mapping Data Flow that supports reading by timestamp/incremental identifier. For real-time loads, solutions based on Change Feed (for example, Cosmos DB Change Feed) or Azure Event Hubs + consumers are suitable.

Common mistakes

  • Ignoring cleanup/retention: CDC tables or logs have retention policies; without management, storage grows indefinitely. Define an appropriate retention and purge/archival policies.
  • Not managing transactions and ordering: applications that read changes must preserve the order of operations by LSN and handle partially applied transactions to avoid inconsistencies at the destination.
  • Assuming CDC always solves real-time latency: CDC reduces volume, but the capture/propagation cycle can introduce latency; for very low latency requirements, using native streaming (Event Hubs, Kafka, Change Feed) may be preferable.

How to practice

Practice with a simple lab: create an Azure SQL Database with a test table, enable CDC for the database and the table, insert/update/delete rows and explore the CDC functions that return changes. Then, build a pipeline in Azure Data Factory that reads incrementally and applies MERGE to a destination table in Azure Synapse or to a table in Azure SQL.

For exam preparation, use the OFFICIAL and free Microsoft Practice Assessment and the Microsoft study guide (both free). These resources help guide study without resorting to unofficial materials or dumps.

In summary

  • CDC captures only the changes (inserts/updates/deletes), avoiding expensive full loads.
  • In Azure, CDC can be implemented in Azure SQL Database, combined with ADF/Synapse for incremental pipelines, or with Event Hubs/Change Feed for streaming.
  • Managing checkpoints (LSN), retention and ordering is essential for consistency and cost control.
  • Practice by enabling CDC in a test database and building an incremental pipeline; consult the Microsoft official Practice Assessment and study guide.