DMS CDC task to Redshift

Question

HI

I am trying to set up a DMS task responsible for migrating only the CDC from My SQL to Redshift . 
The target DB is a new DB on Redshift with no tables .

When I start the job , I see the schema getting generated on the target DB , but the change data entries are absent . Instead , I see a awsdms_apply_exceptions and awsdms_changes.. tables getting created in the public DB , which states an error ODBC return code : 100

I also see an update statement from the error logs that probably failed because there are no rows in the parent table

Question : 
Can the CDC changes be captured alone from DMS to Redshift or does it always have to be a full load task followed by a CDC task ?

MilindOke · Answer

To answer your question - YES - the CDC changes can be captured alone from DMS to Redshift.

However, if you have not run the initial load then the CDC operation for `update` or `delete` will result in error on Redshift since the original record does not exist in Redshift so there is nothing to apply this CDC to.

This is why generally there is an initial load followed by CDC to keep the target updated incrementally.

Didier_Durand · Answer

Hi, I use DMS CDC more with relational databases and not with Redshift. With rdbms, you can only use CDC after the initial full load.

It is confirmed by documentation:  https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.CDC.html
```
You can create an AWS DMS task that captures ongoing changes from the source data 
store. You can do this capture while you are migrating your data. You can also create 
a task that captures ongoing changes after you complete your initial (full-load) migration 
to a supported target data store. This process is called ongoing replication or change data capture (CDC). 
AWS DMS uses this process when replicating ongoing changes from a source data store. This process 
works by collecting changes to the database logs using the database engine's native API.
```

The rationale in rdbms is that if you receive a CDC record for an update of an existing row and it doesn't exist (as you didn't make the initial laod), DMS triggers an error

To achieve what you want, you would need to transform such actions (update -> insert in rdbms context. skip deletes, etc.) . I don't thik that DMS transformation rules allow for that: see https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html

DMS CDC task to Redshift

Relevant content