Changed Data Capture (CDC) is a technique used to extract only the rows that have changed in a source system since the last data load. By avoiding a full table reload, this method significantly reduces processing time and network traffic.
In source-based CDC, the source database may not provide a timestamp for easy filtering. In these cases, the system queries a change log—a record of every INSERT, UPDATE, and DELETE operation on a tracked table. When SAP Data Services reads from a CDC-enabled datastore (such as Oracle or SQL Server), it receives raw change events as individual rows rather than the final state of each row.
The Map_CDC_Operation transform is required to determine how to process these individual change rows when writing to the target.
Note
The Input Schema
When you import a table from a CDC-enabled datastore, SAP Data Services automatically adds two metadata columns to the schema to track changes:
| Column | Data Type | Description |
|---|---|---|
| DI_SEQUENCE_NUMBER | Integer | The order in which the change occurred. It starts at 0 and increments by 1 for each change event. |
| DI_OPERATION_TYPE | VARCHAR(1) | The type of operation: I, B, U, or D. |
These columns are generated by SAP Data Services specifically for CDC-enabled source objects and do not exist in the source database itself.
Operation Codes
The following four operation codes identify the type of change in the source data:
- I (INSERT): A new row was added.
- B (Before-image): The state of a row before it was updated.
- U (Update or After-image): The state of a row after it was updated.
- D (DELETE): A row was removed.
An UPDATE operation at the source generates two rows in the CDC log: a Before-image (B) containing the old values and an After-image (U) containing the new values. The Map_CDC_Operation transform resolves this pair into a single UPDATE operation on the target.
The Transform Process
The Map_CDC_Operation transform processes incoming rows in three steps:
- Sort
- Resolve Before/After Image Pairs
- Map Operation
Watch the following video to learn about the Map_CDC_Operation process.
The Data Flow Architecture
This transform is typically placed as the final object before the target in a data flow because it outputs specific INSERT, UPDATE, and DELETE operation codes.
Note
You can use a Query transform to filter data or add calculated columns. However, you must ensure that the two CDC metadata columns (DI_SEQUENCE_NUMBER and DI_OPERATION_TYPE) are passed through to the Map_CDC_Operation transform. If these columns are dropped, the transform cannot function.
Configuring the Map_CDC_Operation Transform
To use the Map_CDC_Operation transform, you must first enable CDC in the source datastore.
You must configure two primary parameters in the Map_CDC_Operation transform dialog:
- Sequencing column: Select the DI_SEQUENCE_NUMBER column.
- Row operation column: Select the DI_OPERATION_TYPE column.
Optionally, you can add primary key columns to Additional Grouping Columns for secondary sort ordering.
Summary
In this lesson, you learned how to manage Change Data Capture using the Map_CDC_Operation transform.
- The Map_CDC_Operation transform is the central component of any source-based CDC data flow in SAP Data Services.
- It relies on two metadata columns: DI_SEQUENCE_NUMBER for ordering and DI_OPERATION_TYPE for the operation code.
- The transform performs three main functions: sorting, resolving Before/After image pairs, and mapping operations.
- The correct position for this transform is after the Query transform and directly before the target table.
- Do not truncate the target table when using this transform, as changes must be applied incrementally to maintain data integrity.