This is the situation:

My startup has a transactional platform that uses Redshift as its main database (before you say this was an error, it was not—we have multiple products in our suite that are primarily analytical, so we need an OLAP database). Now we are facing scaling challenges, mostly due to some Redshift characteristics that are optimal for OLAP but not ideal for OLTP.

We need to establish a Change Data Capture (CDC) between a primary database (likely Aurora) and a secondary database (Redshift). We've previously attempted this using AWS Database Migration Service (DMS) but encountered difficulties.

I'm seeking recommendations on how to implement this CDC, particularly focusing on preventing blocking. Should I continue trying with DMS? Would Kafka be a better solution? Additionally, what realistic replication latency can I expect? Is a 5-second or less replication time a little too optimistic?