Configure Change Data Capture (CDC) for a SQL Source Collection
The Change Data Capture (CDC) feature in Data Fusion enables incremental data integration by automatically detecting and propagating changes from the source system to the Lakehouse.
Follow these steps to configure CDC on a SQL Source Collection in the Data Integration canvas.
Prerequisite: Source Table Must Include Orderable Fields
Before enabling Change Data Capture (CDC) for a source table, the table must include one or more fields whose values can be used to track the order of changes over time.
Typically, these fields contain timestamp or datetime values, but any monotonically increasing or otherwise orderable values, such as auto-incrementing integer columns, can also be used.
These fields are configured as Order By Fields in the Source Collection and are used to detect new or updated rows during pipeline execution. CDC compares the configured Order By Field values against the last processed checkpoint to determine which records have changed since the previous execution.
If suitable Order By Fields are not available, CDC may still execute, but the platform cannot guarantee the correctness or uniqueness of the incremental data captured in the target entity.
Configure CDC Without Staging in Data Fusion
To configure CDC without staging in Data Fusion:
- Open the
SqlSourceCollectionand click Edit Properties. - Open the Source Collection properties. In either Virtual Table or Load Data mode, configure the CDC Order By Fields. Use Virtual Table for CDC without intermediate staging; use Load Data when staged ingestion is required.
- In the Change Data Capture Configuration section, configure one or more Order By Fields using reliable timestamp or monotonically increasing fields.
- Configure the integration schedule:
- Manual — run processing on demand
- On Schedule — run processing automatically on a recurring schedule
- Click Save.
The Change Data Capture Configuration section is available in both Virtual Table and Load Data modes. Use Virtual Table mode when CDC records should be processed directly without intermediate staged file ingestion.
Example of a Valid Schema for CDC
| Column Name | Data Type | Description |
|---|---|---|
| ID | INTEGER | Primary key for the row |
| CUSTOMER_NAME | STRING | Name of the customer |
| LAST_UPDATED_TS | TIMESTAMP | Monotonically increasing field indicating when the row was last modified |
In this example, LAST_UPDATED_TS is configured as an Order By Field for CDC.
The source table must include at least one field whose values increase when new rows are inserted or existing rows are updated. If suitable Order By Fields are not available, CDC cannot reliably identify new or updated records, and incremental processing may produce incomplete or inconsistent results.
The selected fields must increase over time to ensure reliable incremental ingestion. If no suitable column is configured, CDC may result in missed updates, duplicates, or inconsistent ordering.
Run and Monitor the Pipeline
After configuring CDC:
- Trigger the pipeline using the Run (▶) button on the Source Collection node
- Use the options menu (⋯) to monitor execution:
- View run status
- View data integration status
- View run history
Result
Once configured, the pipeline retrieves only rows with ordering column values greater than the previous checkpoint during each run, enabling incremental ingestion.