Migrating Oracle to Amazon Redshift with Change Data Capture using AWS DMS
A data engineer is designing a data ingestion pipeline to load data from an on-premises Oracle database into Amazon Redshift. The pipeline must capture changes (inserts, updates, deletes) with low latency and minimal impact on the source database. Which combination of AWS services should the engineer use?
Quick Answer
The correct answer is AWS Database Migration Service (DMS) to Amazon S3, then COPY into Redshift. This combination works because AWS DMS supports ongoing replication using change data capture (CDC), which captures inserts, updates, and deletes from Oracle with low latency and minimal source impact, staging the data as files in S3. The COPY command then efficiently bulk-loads those files into Redshift, making it the ideal pipeline for near-real-time ingestion. On the AWS Certified Data Engineer Associate DEA-C01 exam, this scenario tests your understanding of DMS’s CDC capability versus batch-only tools like Glue or event-driven services like Lambda, which cannot continuously stream database changes. A common trap is choosing Kinesis Data Streams, but remember that DMS handles Oracle’s native CDC (LogMiner or XStream) without custom producers. Memory tip: “DMS to S3, then COPY” — think of DMS as the continuous pipe, S3 as the staging bucket, and COPY as the final loader.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
AWS Database Migration Service (DMS) to Amazon S3, then COPY into Redshift
AWS DMS with ongoing replication (change data capture) can capture changes from Oracle with minimal impact and replicate to S3, then COPY into Redshift. Option B is wrong because Kinesis Data Streams requires a custom producer to capture Oracle changes and does not provide native CDC from Oracle databases. Option C is wrong because AWS Glue with JDBC is batch-oriented and does not support real-time change data capture natively. Option D is wrong because AWS Lambda can process events but is not designed for continuous, low-latency CDC from a database and would require polling or triggers.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
AWS Database Migration Service (DMS) to Amazon S3, then COPY into Redshift
Why this is correct
AWS DMS reads Oracle redo logs via change data capture, so inserts, updates and deletes replicate continuously without querying source tables, satisfying the low-latency and minimal-impact constraints. Landing changes in Amazon S3 lets Redshift COPY them in, though deletes require merge handling.
- ✗
Amazon Kinesis Data Streams with a custom producer on Oracle
Why it's wrong here
A custom producer must query Oracle itself to detect changes, adding load and requiring bespoke CDC logic, violating the minimal-impact requirement. Kinesis Data Streams suits high-throughput custom event ingestion, but not log-based change capture from a relational source.
- ✗
AWS Glue with JDBC connection to Oracle, writing to Redshift
Why it's wrong here
Glue JDBC jobs perform batch SELECTs, which cannot capture deletes and repeatedly scan the source, breaching the low-latency and minimal-impact requirements. Glue suits scheduled ETL transformations, not continuous change data capture from an operational database.
- ✗
AWS Lambda reading from Oracle logs and writing to Redshift
Why it's wrong here
Lambda cannot read Oracle redo logs directly; it has no native access to on-premises log files, so change capture would need custom agents and polling. Lambda suits event-driven processing of streams or S3 objects, not log-based CDC from a database.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
Go deeper
Related to this question
About these practice questions
One of 1,321 original DEA-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DEA-C01
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A data engineer needs to ingest data from a relational database (MySQL) into Amazon S3 for analytics. The database is 500 GB and the job must run daily with incremental updates. Which AWS service is BEST suited for this task?
easy- A.Amazon EMR with Apache Sqoop.
- B.Amazon Kinesis Data Firehose with a database source.
- ✓ C.AWS Database Migration Service (DMS) with a replication task.
- D.AWS Glue ETL job with a JDBC connection.
Why C: AWS DMS with a replication task is the best choice because it is specifically designed for continuous, incremental data replication from relational databases like MySQL to Amazon S3. DMS supports ongoing replication (change data capture) to capture incremental changes without custom scripting, and it can handle the initial 500 GB load efficiently. Other services either lack native incremental support or require additional configuration for this use case.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DEA-C01 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C01 exam.