Change Data Capture (CDC) is a job type in Sync where supported data sources use a log file to record events (Insert, Update, or Delete) that alter the database. Instead of querying the source table for changes, CData Sync reads the log file for any change events. The application then extracts these changes in near real-time for replication and stores the current log position for the next replication.

Sync only supports the Soft Deletes when using Change Data Capture (CDC). This means that whenever a row is deleted from the source and the CDC job runs, the row is marked as deleted (by setting a flag to 1 or true) in the destination database rather than removing it.

However, many users have use cases where they need to perform a hard delete in CDC job types, meaning they want the row to be completely removed from the destination database when it is deleted from the source.

To address this need, there is a workaround that allows you to perform a hard delete when using Change Data Capture. This can be achieved by implementing a post-job transformation. Here’s how you can do it in detail:

Step 1: Create a New Transformation

1. Go to the CData Sync Portal and navigate to the Transformations section.

2. Select "Add Transformation" > "Add New Transformation."

3. Provide a name for the transformation and select the destination.

4. Click "Add Transformation."

Step 2: Add a Query to the Transformation

1. Select the transformation you just created.

2. Navigate to the Queries tab and select "Add Query."

3. In the Add Query dialog box, enter the following SQL statement (replacing <table_name> with the name of your destination table) to remove the deleted records:

DELETE FROM <tableName> WHERE _cdatasync_deleted = 1 OR _cdatasync_deleted = true

4. Click "Add Query."

Step 3: Schedule the Transformation to Run After the Job Using the Trigger Option

1. Select the transformation you created.

2. In the Overview tab, navigate to the Trigger category on the right side of the page and select "Configure."

3. In the Trigger dialog box, select the "After Job" option from the drop-down menu so that the transformation will trigger after running your CDC job and can perform the operations.

4. Select the job you want the transformation to run after.

5. If the job has multiple tasks and you only want the transformation to run after certain tasks, click "Select Specific Tasks" and choose those tasks.

6. Click "Save."

Now, whenever you run your CDC job, the rows that are deleted from the source and marked with `1` or `true` in the destination will be removed by the transformation that is triggered after the job.