Understanding Change Data Capture (CDC) in Data Fusion
Change Data Capture (CDC) in C3 AI Studio enables incremental processing of changes from SQL source tables. Instead of reprocessing the entire dataset during each pipeline run, CDC retrieves only rows whose ordering field values have changed since the previous execution.
CDC is supported for SQL-based data sources and requires one or more monotonically increasing ordering fields, typically timestamp columns, to identify new or updated records.
CDC operates during pipeline execution. When the pipeline runs—either manually or on a configured schedule—the platform retrieves rows whose ordering field values are greater than the values processed during the previous execution.
Data Fusion supports both staging-based and non-staging CDC workflows:
- In staging-based workflows, captured records are written to intermediate staged files before downstream processing occurs.
- In non-staging workflows, CDC records are processed directly through the Virtual Table workflow without intermediate staged file ingestion.
Both approaches process data incrementally using CDC checkpoints to track previously processed records.
Enabling CDC
To enable CDC, configure the following:
- One or more Order By Fields used to detect new or updated records. These fields should contain monotonically increasing values, typically timestamp columns.
- An optional integration schedule to automate pipeline execution.
Change Behavior
CDC retrieves rows by comparing configured Order By Field values against the previous checkpoint value and does not distinguish between database operation types.
- Inserted and updated rows are captured when their Order By Field values increase beyond the previous checkpoint.
- Delete operations are not captured because deleted rows do not generate newer Order By Field values.
Notes on Execution and Data Handling
- CDC processing occurs only when the pipeline is executed, either manually or on a configured schedule.
- Data is processed incrementally based on the last recorded checkpoint.
- Depending on the CDC workflow configuration, extracted records may either be staged for downstream processing or processed directly without intermediate staging.
Supported Capabilities
Source Types
CDC is supported only for SQL-based connectors, including:
- Snowflake
- PostgreSQL
- SQL Server
- MySQL
Change Detection
CDC identifies changes using one or more monotonically increasing Order By Fields configured for the Source Collection. Typically, this includes:
- A timestamp column, such as
updated_atorlast_modified - An optional ID column to ensure deterministic ordering when timestamp values are identical
During pipeline execution, rows are retrieved when their Order By Field values are greater than the last processed checkpoint.
Change Behavior
CDC does not track database operation types (INSERT, UPDATE, DELETE). Instead, it detects rows with newer Order By Field values.
- Newly inserted rows are captured when they contain newer Order By Field values.
- Updated rows are captured when the Order By Field values are updated.
- Delete operations are not captured because deleted rows do not generate newer Order By Field values.
Data Handling
When CDC is enabled, the pipeline processes data incrementally based on the configured Order By Fields and the previous checkpoint value. Depending on the CDC workflow configuration, extracted records may either:
- be staged for downstream processing
- or be processed directly without intermediate staging
Pipeline Execution
CDC processing occurs when the Data Integration pipeline runs:
- Manual execution — triggered by the user
- Scheduled execution — runs automatically based on a configured integration schedule
Each execution processes only rows whose Order By Field values are greater than the previous checkpoint.
UI Support
CDC is configured through the Data Fusion interface. The UI supports:
- configuring Order By Fields for change detection
- configuring execution schedules
- configuring CDC through the Virtual Table workflow
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.
Delete Operations Not Propagated Through CDC
In Data Fusion, CDC identifies changes by comparing configured Order By Field values against the last processed checkpoint. During each pipeline execution, CDC retrieves rows whose Order By Field values are greater than the previous checkpoint value—typically newly inserted rows and updated rows where the Order By Field values have changed.
CDC does not distinguish between database operation types (INSERT, UPDATE, DELETE). As a result, delete operations are not captured. If a row is deleted in the source table, the corresponding downstream record remains unchanged because deleted rows do not generate newer Order By Field values.
This behavior is expected because CDC in Data Fusion is based on incremental comparison of Order By Field values rather than full state synchronization.
If your use case requires deletion handling, consider one of the following approaches:
- Use periodic full-table reconciliation workflows
- Implement downstream cleanup or reconciliation logic to remove stale records
Checkpoint Reset and Historical Data Recovery
CDC uses a checkpoint to track the most recent Order By Field values processed during the previous pipeline execution. During normal operation, only rows whose Order By Field values are greater than the stored checkpoint are retrieved. Records missed during earlier executions may not be retrieved in later executions if their Order By Field values are less than or equal to the stored checkpoint.
When switching between Virtual Table and Load Data modes, the CDC checkpoint is preserved so records are not processed twice or missed because of the mode change.
To recover historical records, users can reset the checkpoint and rerun the pipeline to reprocess data from an earlier point in time. Depending on the CDC workflow configuration, recovery behavior may differ:
- In staging-based CDC workflows, staged records may support replay or reprocessing scenarios.
- In CDC workflows without staging, intermediate staged records are not retained, so manual checkpoint reset may be required before rerunning the pipeline.
Transformations and CDC
In Data Fusion, transformations are part of the Data Integration pipeline and are not exclusive to CDC.
When CDC is configured through staging-based ingestion workflows, captured records are processed incrementally and written to downstream target entities. In these workflows, transformations can be used to:
- map source fields to target fields
- rename or reshape data
- prepare records for downstream ingestion
When CDC is configured through the Virtual Table workflow without staging:
- source data is accessed directly from the source system
- intermediate staged files are not created
- CDC records are processed incrementally using configured Order By Fields
Depending on the pipeline design and target workflow requirements, transformations may still be configured for downstream processing.
How Incremental Processing Works in CDC
Once CDC is enabled on a SQL Source Collection, the pipeline processes data incrementally by comparing configured Order By Field values against the last processed checkpoint. During each pipeline execution, only rows whose Order By Field values are greater than the previous checkpoint are retrieved and processed.
Example
- A source SQL table contains 10,000 rows.
- CDC is configured using a monotonically increasing Order By Field, such as
LAST_UPDATED_TS. - At the time of the next pipeline execution, 15 rows contain newer Order By Field values.
Result with CDC: Instead of reprocessing the full dataset, the pipeline retrieves and processes only those 15 rows. Depending on the CDC workflow configuration, the retrieved records may either:
- be staged for downstream processing
- or be processed directly without intermediate staging
This approach reduces unnecessary data movement and improves pipeline efficiency by avoiding full-table reprocessing during each execution.