3.2 Change Data Capture (CDC) Ingestion Patterns

3.2 Change Data Capture (CDC) Ingestion Patterns

Change Data Capture (CDC) is a critical pattern in modern data engineering, allowing organizations to track and propagate row-level inserts, updates, and deletes from operational databases to downstream analytical systems. Instead of performing full table extracts—which are incredibly resource-intensive, slow, and disruptive to source systems—CDC ensures that only the modified data is transferred. Databricks provides multiple robust mechanisms for handling CDC, ranging from traditional MERGE INTO SQL syntax to the highly abstracted and automated Delta Live Tables (DLT) APPLY CHANGES INTO functionality. Understanding both approaches is essential for designing performant and accurate data lakes and lakehouses.

CDC Processing Patterns in Databricks

In a typical CDC architecture, an operational database (like PostgreSQL, SQL Server, Oracle, or MySQL) generates a stream of change events via transaction log replication tools like Debezium, Qlik Replicate (Attunity), Fivetran, or AWS DMS. These events typically land in a highly available message broker (like Apache Kafka) or are dumped into cloud object storage as micro-batches of JSON, Parquet, or Avro files. Each event contains the payload (the actual data row) and crucial metadata (the operation type: insert, update, or delete, and a monotonic timestamp or sequence number).

When ingesting this data into a Delta Lake table, engineers must apply these changes accurately to maintain a synchronized replica of the source system. This requires handling updates to existing rows, inserting new rows, and deleting rows when necessary. This is where Delta Lake's powerful DML (Data Manipulation Language) capabilities come into play, providing ACID transaction guarantees over massive datasets.

MERGE INTO SQL Syntax

The standard, explicit approach to applying CDC events in Delta Lake is using the MERGE INTO command. This syntax allows you to perform an "upsert" (update or insert) based on a matching condition, usually a primary key or a combination of composite keys. It provides fine-grained control over exactly how the data is applied.

MERGE INTO target_table AS t
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp DESC) as rn
    FROM cdc_updates
  ) WHERE rn = 1
) AS s
ON t.id = s.id
WHEN MATCHED AND s.operation = 'DELETE' THEN 
  DELETE
WHEN MATCHED AND s.operation = 'UPDATE' THEN 
  UPDATE SET *
WHEN NOT MATCHED AND s.operation != 'DELETE' THEN 
  INSERT *
WHEN NOT MATCHED BY SOURCE THEN 
  DELETE -- Useful for full syncs

The MERGE INTO statement requires careful handling:

• WHEN MATCHED THEN UPDATE: Updates the existing record in the target table if the keys match.
• WHEN NOT MATCHED THEN INSERT: Inserts a new record if the key doesn't exist in the target table.
• WHEN NOT MATCHED BY SOURCE THEN DELETE: A newer addition to Delta Lake, this clause evaluates the source dataset. If a key exists in the target but is missing from the source, the target record is deleted. This is exceptionally useful for full-sync scenarios or reconciling state after an outage where deletes may have been missed.

While MERGE INTO is incredibly powerful, it requires developers to manually deduplicate the incoming micro-batch. Notice the ROW_NUMBER() window function in the USING clause of the example above. If a single micro-batch contains multiple updates for the same primary key (e.g., a user updates their profile twice in one second), the MERGE operation will fail with a non-deterministic error. Engineers must use window functions to extract only the most recent event per key before applying the merge, increasing the complexity and computational overhead of the pipeline.

Delta Live Tables APPLY CHANGES INTO

To dramatically simplify CDC and eliminate the brittle boilerplate code associated with manual MERGE operations, Databricks introduced the APPLY CHANGES INTO syntax within Delta Live Tables (DLT). This declarative API abstracts away the complexity of deduplication, event ordering, and Slowly Changing Dimension (SCD) management. It allows engineers to focus on the business logic rather than the underlying state management.

Instead of writing complex MERGE logic and manual windowing, you simply define the source dataset, the target table, the primary keys, and the sequencing logic using Python or SQL DLT APIs:

import dlt

dlt.create_streaming_table("target_table")

dlt.apply_changes(
  target = "target_table",
  source = "cdc_updates",
  keys = ["id"],
  sequence_by = dlt.expr("timestamp"),
  apply_as_deletes = dlt.expr("operation = 'DELETE'"),
  apply_as_truncates = None,
  except_column_list = ["operation", "_rescued_data", "timestamp"],
  stored_as_scd_type = 2
)

Core Properties of APPLY CHANGES INTO

• KEYS: Specifies the primary key(s) used to identify unique records. DLT automatically handles intra-batch deduplication based on these keys, resolving the non-deterministic merge error natively.
• SEQUENCE BY: This is arguably the most critical parameter for data accuracy. It defines the logical order of events. In distributed systems, events frequently arrive out of order. sequence_by ensures that an older update doesn't accidentally overwrite a newer one. This replaces complex manual windowing logic entirely.
• STORED AS SCD TYPE 1 / TYPE 2:
  • Type 1 overwrites the existing record, maintaining only the latest state (a true mirror of the source).
  • Type 2 maintains a full historical record of changes. By merely setting this property, DLT automatically generates effective start timestamps, end timestamps, and an active flag boolean. DLT manages this history entirely behind the scenes, allowing analysts to query the state of a record at any point in time.
• COLUMNS / EXCEPT: Allows you to explicitly include (COLUMNS) or exclude (EXCEPT) specific columns from the source when writing to the target. For instance, you typically want to exclude the CDC operation type column or internal Debezium metadata columns from the final analytical table to keep the schema clean.

Handling Out-of-Order CDC Events and Exactly-Once Processing

In distributed systems, CDC events frequently arrive out of order due to network latency, retries, or partition imbalances in message buses like Kafka. If an 'UPDATE' event with timestamp 10:05 arrives before an 'INSERT' event with timestamp 10:04, a naive pipeline might process the update (which fails or does nothing because the record doesn't exist), and then process the insert, leaving the final table with stale data.

DLT natively solves this via the SEQUENCE BY clause combined with Delta Lake's ACID transactions. When DLT encounters events for the same key, it evaluates the sequence value. It guarantees that the event with the highest sequence value represents the final state, effectively ignoring delayed older events and completely mitigating the out-of-order problem. Under the hood, DLT maintains an internal state table to track sequence numbers, ensuring that even across cluster restarts or job failures, the pipeline maintains exactly-once processing semantics for the CDC stream.

Practical Production CDC Architecture Scenario

Consider an e-commerce platform migrating its user dimension table from a daily full-load batch process to real-time CDC to power real-time recommendation engines. The database is PostgreSQL, using AWS DMS to write CDC events to an S3 bucket in Parquet format. The team initially writes a Spark Streaming job using foreachBatch and MERGE INTO, but they struggle with deduplicating rapid address changes and handling out-of-order events caused by DMS task restarts. The custom code is hundreds of lines long and prone to bugs.

By rewriting the pipeline using Auto Loader to read the DMS files and Delta Live Tables with APPLY CHANGES INTO and stored_as_scd_type = 2, they eliminate all the custom state management code. The pipeline becomes declarative. Furthermore, data scientists immediately gain access to historical address changes via the auto-generated SCD Type 2 tracking columns. They can now train models based on the temporal sequence of a user's location, unlocking new geospatial analytics capabilities with minimal engineering effort. The operational burden is drastically reduced, allowing the team to focus on scaling to more tables rather than debugging merge conflicts.