Day 3 - CDC and Data Replication: Database Migration Service (DMS)

The need to move data from running databases to data warehouses or data lakes arrives endlessly. But you can't stop the operational database. Nightly full dumps scale explosively as data grows, and changes during the dump get missed. And "last night's snapshot" is useless for real-time analysis.

CDC (Change Data Capture) solves this. CDC says "don't re-read everything, capture only what changed since last time." In AWS, AWS Database Migration Service (DMS) handles this. Despite the name "Migration," it performs both one-time migration and ongoing replication via CDC.

DMS: Three Components

DMS works as a combination of three parts. Understanding this decomposition solves half the exam questions.

[Source Endpoint] → [Replication Instance] → [Target Endpoint]
   (e.g., RDS MySQL)   (executes migration)   (e.g., S3 / Redshift)
                            │
                       [Migration Task]
                   (Full Load / CDC / Full+CDC)

DMS's strength is heterogeneous replication. Oracle → PostgreSQL, SQL Server → S3 across different engines. But DMS doesn't handle schema/code (stored procedures) conversion — that's AWS SCT (Schema Conversion Tool) or DMS Schema Conversion. "Data = DMS, schema/code = SCT" is the split.

💡 Related theory: CDC is a classic data integration pattern. The alternative "snapshot diff" compares full data at two points to find changes, but cost explodes with size. CDC leverages transaction logs the DB already writes, extracting only changes with low source impact. "Don't ask data again; read the traces data already left" is CDC's philosophy.

Three Migration Modes

A Migration Task operates in one of three modes.

Full Load + CDC is most important. Process: (1) Full Load copies all existing data. (2) Changes occurring during Full Load are cached by DMS. (3) When Full Load finishes, cached changes are applied, then switch to real-time CDC. This moves data consistently without stopping the operational DB.

// Migration Task config (conceptual excerpt)
{
  "MigrationType": "full-load-and-cdc",
  "TableMappings": {
    "rules": [{
      "rule-type": "selection",
      "object-locator": { "schema-name": "sales", "table-name": "orders" },
      "rule-action": "include"
    }]
  }
}

⚠️ Pitfall: For CDC to work, source DB's change log access must be enabled. MySQL needs binary log (binlog) in ROW format, PostgreSQL needs logical replication (wal_level=logical), Oracle needs supplemental logging. Skipping this prerequisite causes Full Load to succeed but CDC to fail — a textbook exam trap.

How CDC Captures Changes: Transaction Logs

CDC's core: reading transaction logs that DBs already write for durability. All relational DBs write changes to log before commit (Write-Ahead Logging). DMS follows this log and extracts INSERT/UPDATE/DELETE.

Big advantage: no triggers on source tables, no extra queries. Just reading existing logs means minimal impact to source workload (low-impact).

🔍 Going deeper: Each change record extracted by CDC carries metadata indicating operation type. DMS can mark this as an Op column on S3 (I=insert, U=update, D=delete) with settings like includeOpForFullLoad. Downstream uses this Op to implement merge/upsert logic in data lakes. CDC transmits "flow of change," not "current state" — that's the core insight.

DMS Serverless and Validation

Traditional DMS required choosing Replication Instance size manually. Too small and CDC lags; too large and waste. DMS Serverless auto-scales capacity (DCU, DMS Capacity Unit) by workload, eliminating sizing worries.

DMS also offers data validation — comparing source and target rows to confirm accuracy. Even if migration "completes," data might not match perfectly, so validation auto-finds mismatches.

Key CloudWatch metrics when operating DMS:
- CDCLatencySource : Delay reading changes from source log
- CDCLatencyTarget : Delay applying changes to target
- FullLoadThroughputRowsTarget : Full Load ingestion rate

Rising CDCLatencyTarget means target writes bottleneck (e.g., Redshift commit overhead). Rising CDCLatencySource means source log read speed is the issue.

🎯 Scenario: Migrate on-premises Oracle DB to Aurora PostgreSQL zero-downtime. Process: (1) SCT converts schema/stored procedures to PostgreSQL → (2) Enable supplemental logging on Oracle → (3) Start DMS Full Load + CDC → (4) After Full Load, real-time CDC keeps both DBs in sync → (5) Data validation confirms integrity → (6) Cutover application to PostgreSQL, stop CDC. SCT and DMS role split is critical.

CDC to Data Lake/Warehouse: Patterns

Common patterns when DMS targets analytics systems.

Pattern 1: Operational DB → DMS(CDC) → S3 → (Glue/Spark) → Data Lake
  - Load changes as Parquet to S3, merge via Op column
  - Use Apache Hudi / Iceberg for upsertable lake tables

Pattern 2: Operational DB → DMS(CDC) → Kinesis Data Streams → Flink/Firehose
  - Stream changes as events; multiple consumers ingest

Pattern 3: Operational DB → DMS(Full+CDC) → Amazon Redshift
  - Keep analytics warehouse near-real-time in sync with operational DB

DMS can target S3, Kinesis Data Streams, Redshift, OpenSearch, DynamoDB — all analytics-friendly. "Reflect operational DB changes to data lake near-real-time" is a DEA exam core scenario.

💡 Related theory: Streaming CDC patterns connect to "turning the database inside-out" (Jay Kreps, Kafka founder). Current table state is merely the materialized view of all changes accumulated. CDC exposes this change log externally, letting multiple downstreams (lake, search, cache) each reconstruct independently.

Summary: Move Without Stopping

CDC's essence: "follow changes, don't re-read everything." DMS provides this in managed heterogeneous environments. Full Load + CDC is zero-downtime standard; enabling CDC requires source transaction log config. SCT handles schema/code; DMS handles data. This division, mode choice, and log setup are exam critical. Tomorrow we move to synthesis — Lambda Architecture and event-driven ingestion patterns — tying collections into big pictures.