Snowflake Cloud Data Warehouse V2 Connector > Additional runtime configurations > Capturing changed data from CDC sources
  

Capturing changed data from CDC sources

You can use Snowflake Cloud Data Warehouse V2 Connector to write changed data from a CDC source such as Oracle CDC and Oracle CDC V2 and write the changed data to a Snowflake target.
You must have the appropriate license to configure this functionality.
When you configure a mapping, add the CDC sources and then run the associated mapping task to write the changed data to Snowflake. When you write changed data from a CDC source, you can only configure a single Snowflake Cloud Data Warehouse V2 target transformation in a mapping.
When the mapping task processes the changed data from a CDC source, Snowflake Cloud Data Warehouse V2 Connector creates a state table in Snowflake. When the changed data is received from the CDC source, Snowflake Cloud Data Warehouse V2 Connector uploads the changed data to the staging table. Then, it generates a Job_Id and writes the Job_Id to the state table along with the restart information.
Snowflake Cloud Data Warehouse V2 Connector then merges the stage table with the actual target table in Snowflake. Each time you run the mapping task, Snowflake Cloud Data Warehouse V2 Connector creates the state table, if it does not exist, to store the state information.
Snowflake Cloud Data Warehouse V2 Connector uses the following naming convention for the tables:

Configurable Snowflake advanced properties for CDC

When you configure a mapping to write changed data from a CDC source to Snowflake, you can configure the following advanced properties in the Snowflake target transformation:

Configuring a mapping task to read from a CDC source

Your organization needs to replicate real-time changed data from a production system to an analytical operations system. You can use Snowflake Cloud Data Warehouse V2 Connector to capture changed data from a CDC source and write the changed data to a Snowflake target. Add the CDC source in the mapping, and then run the associated mapping task to write the changed data to the Snowflake target.
    1. In Data Integration, click New > Mapping > Create.
    The New Mapping dialog box appears.
    2. Enter a name and description for the mapping.
    3. In the Source transformation, specify a name and description in the general properties.
    4. In the Source tab, select any configured CDC connection and specify the required source properties.
    5. In the Target transformation, specify a name and description in the general properties.
    6. On the Target tab, perform the following steps to configure the target properties:
    1. a. In the Connection field, select the Snowflake Cloud Data Warehouse V2 connection.
    2. b. In the Target Type field, select the type of the target object.
    3. c. In the Object field, select the required target object.
    4. d. In the Operation field, select Data Driven to handle insert, update, and delete records from the source.
    5. e. In the Data Driven Condition field, leave the field empty.
    6. f. In the Update Column field, select the key columns to upsert, update, or delete data in Snowflake.
    7. It is recommended that the source object contains a primary key.
    8. g. Configure the applicable advanced target properties for the CDC mode:
    7. On the Field Mapping tab, map the incoming fields to the target fields. You can manually map an incoming field to a target field or automatically map fields based on the field names.
    8. In the Actions menu, click New Mapping Task.
    The New Mapping Task page appears.
    9. In the Definition tab, enter the task name and select the configured mapping.
    10. In the CDC Runtime tab, specify the required properties for the selected CDC source.
    For more information about the CDC Runtime properties, see the source properties for the selected CDC source.
    11. In the Schedule tab, add the following properties in the Advanced Session Properties section:
    1. a. In the Commit on End of File field, select the value of the property as No.
    2. b. In the Commit Type field, select the value of the property as Source.
    3. c. In the Recovery Strategy field, select the value of the property as Resume from last checkpoint.
    12. Click Save > Run the mapping task.
    Alternatively, you can create a schedule that runs the mapping task on a recurring basis without manual intervention. You can define the schedule to minimize the time between mapping task runs.
In Monitor, you can monitor the status of the logs after you run the task.

Rules and guidelines for capturing changed data

Consider the following guidelines when you configure a task to capture changed data from a CDC source: