Time Delay between two different tables in DMS replication

When using AWS DMS for CDC replication from Oracle RDS to S3, you cannot directly configure different synchronization frequencies for different tables within the same task. DMS processes changes as they occur in the source database's redo logs, and by default, it applies these changes to the target as soon as they're captured.

However, you can achieve your requirement of having different synchronization patterns for different tables by creating multiple DMS tasks:

1. Create one task for the 80 tables that need immediate synchronization. This task would handle both full load and CDC with continuous replication.

2. Create a separate task for the 20 tables that only need daily updates. For this task, you can:

• Configure it as a full-load-only task that you schedule to run daily
• Or set it up as a CDC-only task and use task scheduling to stop and start it daily

For the second task, you should be aware that if you're using a CDC-only approach, AWS DMS caches changes that occur while the task is stopped. When you restart the task, it will apply all cached changes since the last run, which might cause a temporary increase in latency as it processes the backlog of changes.

Keep in mind that using LogMiner (the default for Oracle sources) allows DMS to capture changes from the Oracle redo logs. The changes are captured when they occur in the source database, but you can control when they're applied to the target by managing your task schedules.

If you're experiencing latency issues with your current setup, you might want to check your DMS configuration, ensure your replication instance is properly sized, and verify that your Oracle source is correctly configured for CDC with appropriate supplemental logging.
Sources
AWS DMS between Oracle - Sync Latency | AWS re:Post
Troubleshooting latency issues in AWS Database Migration Service - AWS Database Migration Service
DMS between two RDS instances, grown OldestReplicationSlotLag | AWS re:Post