You can use Snowflake Data Cloud 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.
When you configure a mapping, add the CDC sources and then run the associated mapping task to write the changed data to Snowflake. If you define a column as required in the Snowflake target table, map a column in the CDC source to the required column in the Snowflake target in the mapping before you run the task.
When the mapping task processes the changed data from a CDC source, Snowflake Data Cloud Connector creates a state table in Snowflake. When the changed data is received from the CDC source, Snowflake Data Cloud 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.
The connector then merges the stage table with the actual target table in Snowflake. Each time you run the mapping task, Snowflake Data Cloud Connector creates the state table, if it does not exist, to store the state information.
If you stop a CDC mapping task enabled with recovery strategy, or if it terminates unexpectedly, you can also restart it from the beginning of the task or resume it from where the task last ended from the job details page.
For more information about restarting and resuming a mapping task, see Tasks.
Snowflake Data Cloud Connector uses the following naming convention for the tables:
Consider the following restrictions when you capture changed data from CDC sources:
Source or lookup object in a CDC mapping
You can use a Snowflake target in a CDC mapping to write data from CDC sources. You can't use a Snowflake source or lookup in a CDC mapping.
Staging optimization property
The optimization property that you set for staging data in the DTM of the Secure Agent is not applicable. If you run a mapping with both CDC and staging property enabled, the mapping runs successfully. However, staging optimization is disabled and you can view a message logged in the session logs.
Recovery initialization error
When you run a CDC mapping to write data from a CDC source to a Snowflake target created at runtime, you might encounter the following error:
Error occured while initializing CCI State Handler com.informatica.cci.runtime.internal.utils.impl.CExceptionImpl: Internal error: Recovery Init failed
To avoid this error, you must have the grant permissions to create a table in Snowflake.
DTM error
When you run a CDC mapping that uses a Snowflake target connection configured with the ProcessConnDB and ProcessConnSchema parameters in the Additional JDBC URL Parameters field, the mapping might fail with the following DTM error:
Internal error. The DTM process terminated unexpectedly. Contact Informatica Global Customer Support.
To avoid this error, remove the ProcessConnDB and ProcessConnSchema parameters from the Additional JDBC URL Parameters field in the Snowflake Data Cloud connection. However, if you want to create temporary stage tables and recovery state tables using the ProcessConnDB and ProcessConnSchema parameters, verify that you have the necessary permissions in the required database and schema.
Configuring a mapping task to read from a CDC source
You can use Snowflake Data Cloud 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. You can also configure multiple pipelines in a single mapping to write the captured changed data to a Snowflake target.
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:
•Database
•Schema
•Warehouse
•Role
•Pre SQL
•Post SQL
•Truncate target table
•Table name
•Update override
Step 1. Configure the source
Configure a Source transformation to read from a CDC source such as Oracle CDC and Oracle CDC V2.
1In the Source transformation, specify a name and description in the general properties.
2In the Source tab, select any configured CDC connection and specify the required source properties.
It is recommended that the source object contains a primary key.
Step 2. Configure the target
Configure a Target transformation to write changed data from a CDC source to Snowflake.
You can only configure a single Snowflake Data Cloud target transformation in a mapping to write changed data from a CDC source.
1On the Target tab, perform the following steps to configure the target properties:
aIn the Connection field, select theSnowflake Data Cloud connection.
bIn the Target Type field, select the type of the target object.
cIn the Object field, select the required target object.
dIn the Operation field, select Insert or Data Driven.
Note: Update, upsert, and delete target operations are not applicable. Ensure that the target tables do not have a primary key defined in Snowflake.
eIf you select Data Driven Condition, specify the DD_INSERT condition.
Note: Ensure that the target tables do not have a primary key defined in Snowflake.
fConfigure the applicable advanced target properties for the CDC mode.
2On 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.
If you define a column as required in the Snowflake target table, map a column in the CDC source to the required column in the Snowflake target in the mapping.
Step 3. Configure the mapping task
After you create a mapping, add the mapping to a mapping task, and configure the advanced properties. Run the associated mapping task to write the changed data to Snowflake.
1From the Actions menu, click New Mapping Task.
The New Mapping Task page appears.
2In the Definition tab, enter the task name and select the configured mapping.
3In 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.
4In the Runtime Options tab, add the following properties in the Advanced Session Properties section:
aIn the Commit on End of File field, select the value of the property as No.
bIn the Commit Type field, select the value of the property as Source.
cIn the Recovery Strategy field, select the value of the property as Resume from last checkpoint.
5Click 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.
To improve performance, specify a higher commit interval for the Maximum Rows Per Commit property in the CDC Runtime page in the mapping task wizard. However, in case of failure, recovery takes more time for a higher commit interval.
Disabling the recovery mechanism
If you do not want to retrieve changes using the recovery state table when a CDC job encounters a failure, you can disable recovery in the target and mapping task.
To disable recovery, perform the following tasks:
1Set the disableSnowflakeRecoveryCDC=true property in the Additional Write Runtime Parameters field in the Snowflake Data Cloud Target transformation.
2In the Advanced Session Properties on the Runtime Options tab in the mapping task, remove the configured recovery strategy.
Note: Ensure that the source is not Snowflake in a CDC mapping. If the mapping contains a Snowflake source and target, and if you set the disableSnowflakeRecoveryCDC property for the target, the mapping job shows incorrect results.