Study guide
2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC)
AWS Certified Data Engineer - Associate DEA-C01
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Streaming Data Ingestion
1.1 Kinesis Data Streams vs. Firehose Architecture & Partitioning 1.2 Amazon MSK (Managed Streaming for Apache Kafka) & Serverless Patterns 1.3 Event-Driven Streaming Ingestion with DynamoDB Streams & Lambda 1.4 Streaming Distribution, Replay & Processing State
2 Chapter 2: Batch Data Ingestion & Migration
2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC) 2.2 S3 Batch Operations & High-Throughput Bulk Data Ingestion 2.3 Hybrid Data Transfer with Snow Family & AWS DataSync 2.4 Application, API & Managed File Ingestion
3 Chapter 3: ETL Transformation with AWS Glue
3.1 AWS Glue Jobs, DynamicFrames & PySpark Transformations 3.2 Glue Crawlers, Data Catalog & Schema Evolution 3.3 Data Preparation & Quality Rules with AWS Glue DataBrew & Data Quality 3.4 Incremental Processing with AWS Glue Job Bookmarks & Partition Processing 3.5 Technical and Business Catalogs, Connections & Lineage
4 Chapter 4: Big Data Processing & Workflow Orchestration
4.1 Amazon EMR Cluster Architecture, Spark & Hadoop Ecosystem 4.2 EMR Serverless & Instance Selection (On-Demand vs. Spot Optimization) 4.3 Serverless Orchestration with AWS Step Functions 4.4 Airflow Pipeline Orchestration with Amazon MWAA 4.5 Containers, Lambda & Distributed Processing Performance 4.6 Data Engineering Code, IaC & CI/CD
5 Chapter 5: Object Storage & Data Lake Architecture
5.1 Amazon S3 Storage Classes, Lifecycle Policies & Cost Optimization 5.2 S3 Data Lake Partitioning, Bucketing & Layout Strategies 5.3 Efficient Columnar Formats: Parquet, ORC, and Avro Schema Design 5.4 S3 Performance Optimization, Multipart Uploads & Transfer Acceleration 5.5 Apache Iceberg, S3 Tables & Open Table Operations
6 Chapter 6: Data Warehousing with Amazon Redshift
6.1 Amazon Redshift Architecture, RA3 Nodes & Managed Storage 6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys 6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum
7 Chapter 7: NoSQL, Relational & Specialty Data Stores
7.1 Amazon DynamoDB Partition Keys, Global Secondary Indexes & Data Modeling 7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ 7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena 7.4 Vector Stores, Indexes & LLM Data Processing
8 Chapter 8: Pipeline Operations, Monitoring & Observability
8.1 Monitoring Pipeline Health with Amazon CloudWatch Metrics & Logs 8.2 Event-Driven Alerting & Automation with EventBridge & CloudWatch Alarms 8.3 Centralized Audit Logging with AWS CloudTrail
9 Chapter 9: Troubleshooting, Data Quality & Performance Tuning
9.1 Diagnosing & Resolving AWS Glue, EMR & Lambda Execution Failures 9.2 Redshift & Athena Query Performance Tuning & Memory Management 9.3 Implementing Data Quality Frameworks & Automated Validation Checks 9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew
10 Chapter 10: High Availability, Backup & Cost Optimization
10.1 Disaster Recovery, Cross-Region Replication & Backup Strategies 10.2 Designing High Availability Architectures Across AWS Data Services 10.3 Cost Optimization & Resource Utilization across Analytics Workloads
11 Chapter 11: Data Security, Encryption & Identity Management
11.1 Fine-Grained Access Control with AWS IAM Policies, Roles & Conditions 11.2 Data Encryption at Rest & in Transit with AWS KMS & S3 Bucket Policies 11.3 Secure Credentials & Secrets Management with AWS Secrets Manager & Parameter Store 11.4 Private Network Connectivity & Isolation with AWS PrivateLink & VPC Endpoints
12 Chapter 12: Data Governance, Privacy & Compliance
12.1 Centralized Data Lake Governance & Access Control with AWS Lake Formation 12.2 Automated Sensitive Data Discovery & PII Protection with Amazon Macie 12.3 Configuration Auditing & Compliance Enforcement with AWS Config 12.4 Data Privacy, Sovereignty, Sharing & Retention Guardrails 12.5 SageMaker Unified Studio Domains, Projects & Catalog Governance
2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC)
Key Takeaways Explain This Topic AI
AWS DMS supports homogeneous (e.g., MySQL to Aurora MySQL) and heterogeneous (e.g., Oracle to Amazon Redshift) database migrations with minimal downtime using Change Data Capture (CDC).
DMS CDC relies on reading engine-native transaction logs (e.g., PostgreSQL WAL, MySQL binlog, Oracle Redo logs, SQL Server transaction log) requiring proper log retention and permissions.
For heterogeneous migrations, use DMS Schema Conversion to assess and convert schemas and code objects; AWS documentation now recommends it over the legacy desktop AWS SCT workflow.
DMS LOB handling modes directly impact migration throughput: Limited LOB mode truncates LOBs at a user-defined size for maximum speed, Full LOB mode moves LOBs of any size slowly in a two-step query, and Inline LOB mode balances speed by combining small LOBs with tabular data.
Setting BatchApplyEnabled=true optimizes target writes by combining CDC changes into bulk operations using S3/staging tables instead of executing individual row-by-row SQL statements.
Last updated: August 2026
2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC)
Quick Answer: AWS Database Migration Service (AWS DMS) migrates databases to AWS quickly and securely while keeping the source database operational during migration. For homogeneous migrations (e.g., PostgreSQL to RDS PostgreSQL), DMS handles both schema and data replication. For heterogeneous migrations (for example, Oracle to PostgreSQL), assess and convert schema definitions with DMS Schema Conversion before DMS moves table data. The legacy desktop AWS SCT can still appear in older material, but current AWS guidance recommends the managed DMS capability.
AWS DMS Core Architecture
AWS DMS operates using three fundamental components: Replication Instances , Endpoints , and Replication Tasks . +-----------------------------------------------------------------------------------+
- | AWS DMS Architecture |
- | |
- | +-------------------+ +-----------------------+ +-----------------+ |
- | | Source Database | ----> | Replication Instance | ----> | Target Service | |
- | | (Oracle/Postgres) | | (Source/Target Endpts)| | (Redshift/S3) | |
- | +-------------------+ | - Full Load Task | +-----------------+ |
- | | - CDC Engine Task | |
- | +-----------------------+ |
- +-----------------------------------------------------------------------------------+
- 1. Replication Instance
A managed EC2 instance running within an AWS VPC that hosts one or more replication tasks. DMS offers compute-optimized ( c5 ), memory-optimized ( r5 ), and general-purpose ( dms.t3 ) instances.
Multi-AZ Deployment: Highly recommended for ongoing CDC tasks to ensure high availability and automatic failover across Availability Zones.
Storage Allocation: Local EBS storage holds task logs and cached change data during change processing.
2. Endpoints
Endpoints define connection details, authentication credentials, network settings, and engine configurations for source and target stores.
Source Endpoints: Relational databases (RDS, EC2, on-premises Oracle, SQL Server, MySQL, PostgreSQL, DB2), S3, Azure SQL DB.
Target Endpoints: Amazon RDS, Aurora, Amazon Redshift, Amazon S3, Amazon Kinesis Data Streams, Amazon MSK (Kafka), Amazon OpenSearch, DynamoDB.
3. Replication Tasks
Tasks define what data is migrated and how. AWS DMS supports three task types:
Full Load (Migrate existing data): Extracts all records from source tables and writes them to the target. Tables can be loaded in parallel.
Full Load + CDC (Migrate existing data and replicate ongoing changes): Captures changes while full load is executing, buffers them on the replication instance, and applies them after full load completes.
CDC Only (Replicate ongoing changes): Starts capturing data changes from a specified Log Sequence Number (LSN), System Change Number (SCN), or timestamp.
Change Data Capture (CDC) Mechanics
AWS DMS CDC does not poll tables using SELECT statements (which creates heavy source database overhead). Instead, it reads the engine-native transaction logs of the source database.
- Source Engine Log Mechanism Read by DMS Mandatory Source Configuration
- MySQL / MariaDB Binary Logs ( binlog ) binlog_format = ROW , binlog_checksum = NONE
- PostgreSQL Write-Ahead Log (WAL) wal_level = logical , Logical Replication Slots
- Oracle Redo Logs / Archive Logs ARCHIVELOG mode enabled, Supplemental Logging turned on
- Microsoft SQL Server Database Transaction Log Enable CDC ( sys.sp_cdc_enable_db ), MS-Replication enabled
- Transaction Log Retention Requirements
If DMS experiences network downtime or replication instance failover, it must resume reading from the exact LSN/SCN where it left off. If the source database purges transaction logs before DMS reads them, the CDC task fails with an unrecoverable log position error. You must adjust log retention policies:
PostgreSQL: Configure wal_keep_size or max_slot_wal_keep_size .
MySQL: Set binlog_expire_logs_seconds to at least 24–48 hours.
Oracle: Retain archived redo logs on disk for 24+ hours.
DMS Schema Conversion Workflow
While DMS converts basic data types during full load, it does not convert complex database objects such as stored procedures, triggers, views, secondary indexes, or foreign keys in heterogeneous migrations. Homogeneous Migration: Source DB ------ (DMS Task Handles Schema & Data) ------> Target DB
- Heterogeneous Migration: Source DB --> [DMS Schema Conversion] --> Target DB --> [DMS CDC Data Task]
- Heterogeneous Migration Pipeline
Run DMS schema assessment report: Evaluates the source schema and generates an executive report listing items that can be automatically converted versus items requiring manual refactoring.
Convert schema with DMS Schema Conversion: Converts schema objects (DDL) into the target dialect (e.g., converting Oracle PL/SQL to PostgreSQL PL/pgSQL) and applies them to the target database.
Drop Foreign Keys & Secondary Indexes: Temporarily drop target constraints/indexes to maximize DMS full load write performance.
Run DMS Full Load + CDC: Execute data ingestion via DMS.
Re-apply Indexes & Constraints: Re-create foreign keys and indexes after Full Load completes before final cutover.
Large Object (LOB) Optimization Modes
LOB columns (e.g., CLOB , BLOB , VARCHAR(MAX) , TEXT ) present major migration bottlenecks. DMS handles LOBs using three distinct modes:
1. Limited LOB Mode (Fastest)
Truncates LOB data at a specified MaxLOBSize (e.g., 32 KB).
Performance: Extremely high. All data is fetched in a single query.
Use Case: When LOB size is known and uniform across all tables.
2. Full LOB Mode (Slowest)
Does not truncate LOB data regardless of size.
Performance: Very slow. DMS extracts non-LOB columns first, then queries the source table row-by-row to fetch LOB data individually.
Use Case: Required when LOBs exceed hundreds of megabytes and cannot be truncated.
3. Inline LOB Mode (Recommended Compromise)
With Full LOB mode enabled, transfers small LOBs inline with tabular data up to InlineLobMaxSize (for example, 64 KB). Larger LOBs fall back to Full LOB mode instead of being truncated.
Performance: Speeds up small LOBs while preserving large values, but the target endpoint must support both Full LOB mode and Inline LOB mode.
DMS Performance & Advanced Configuration Settings
To optimize throughput on large tables or high-volume CDC streams, data engineers configure extra connection attributes and task JSON settings: {
- "TargetMetadata": {
- "SupportLobs": true,
- "FullLobMode": false,
- "LimitedSizeLobMode": true,
- "LobMaxSize": 64,
- "BatchApplyEnabled": true
- },
- "FullLoadSettings": {
- "TargetTablePrepMode": "DROP_AND_CREATE",
- "CreatePkAfterFullLoad": true,
- "MaxFullLoadSubTasks": 8,
- "CommitRate": 10000
- },
- "ValidationSettings": {
- "EnableValidation": true,
- "ValidationMode": "ROW_LEVEL"
- }
- }
Key Parameters Defined:
BatchApplyEnabled=true : Switches CDC from executing individual one-by-one SQL statements ( INSERT , UPDATE , DELETE ) to compiling changes into bulk staging tables and executing set-based bulk upserts. Drastically reduces target CPU and write I/O.
MaxFullLoadSubTasks : Controls how many tables DMS can load in parallel during full load; table-level parallel-load settings are endpoint-specific and should be verified for the selected target.
ValidationMode=ROW_LEVEL : Compares source and target records after Full Load and CDC to confirm data integrity, generating detailed CloudWatch validation failure metrics.