CDC Data Pipeline on AWS: S3, Glue, and Redshift Integration Example

Change Data Capture (CDC) pipelines on AWS have become the backbone of modern data warehousing strategies, enabling organizations to maintain near real-time analytics capabilities without overwhelming source databases. By combining Amazon S3 as a data lake, AWS Glue for transformation and cataloging, and Amazon Redshift for analytics, you can build a scalable CDC pipeline that handles everything from initial full loads to incremental updates. This article walks through a complete implementation, focusing on the architecture decisions and configuration details that make these pipelines production-ready.

Understanding the AWS CDC Architecture

The AWS-native CDC pipeline follows a medallion-style architecture where data flows through distinct layers, each serving a specific purpose. Source database changes are captured and written to S3 as the raw landing zone. AWS Glue processes these changes, applying transformations and maintaining a catalog of the data structure. Finally, Redshift serves as the analytics layer where business users query the processed data.

This architecture leverages S3 as the central hub, which provides several advantages over direct database-to-warehouse replication. S3’s virtually unlimited storage capacity allows you to retain complete change history at low cost. The separation of storage and compute means you can scale processing independently of storage. Most importantly, S3 acts as a buffer that decouples your source systems from your analytics workloads, preventing analytics queries from impacting operational databases.

The data flow typically begins with AWS Database Migration Service (DMS) capturing changes from your source database. DMS supports full load plus CDC for databases like MySQL, PostgreSQL, Oracle, and SQL Server. It writes both initial snapshots and ongoing changes to S3 in either CSV or Parquet format. From there, Glue crawlers discover the data structure, Glue ETL jobs transform the data, and Redshift loads it for analysis.

Configuring AWS DMS for CDC

Setting up DMS for CDC requires careful attention to both the source endpoint configuration and the replication task settings. Start by enabling CDC on your source database. For RDS MySQL or Aurora MySQL, ensure binary logging is enabled with the binlog_format set to ROW. For PostgreSQL, you’ll need to set the rds.logical_replication parameter to 1 and create a replication slot.

Create a DMS replication instance sized appropriately for your workload. The instance class determines how much data throughput DMS can handle. For production workloads with high transaction volumes, start with at least a dms.c5.xlarge instance. DMS charges by instance runtime, not by data volume, so choosing the right size impacts both performance and cost.

Your S3 target endpoint configuration controls how DMS writes CDC data to S3. Here’s an example configuration that writes Parquet files partitioned by date:

{
  "ServiceAccessRoleArn": "arn:aws:iam::123456789012:role/dms-s3-role",
  "BucketName": "my-cdc-data-lake",
  "BucketFolder": "cdc-raw",
  "DataFormat": "parquet",
  "CompressionType": "gzip",
  "TimestampColumnName": "cdc_timestamp",
  "CdcPath": "cdc-raw/CDC_DATE=YYYY-MM-DD",
  "ParquetVersion": "parquet-2-0",
  "EnableStatistics": true
}

The CdcPath parameter is particularly important—it creates date-based partitions that make downstream processing more efficient. Instead of scanning all CDC files, your Glue jobs can process only the partitions containing new data. The TimestampColumnName adds a timestamp to each row, which you’ll use for tracking when changes occurred.

In your DMS task configuration, enable full load plus CDC to capture the initial snapshot followed by ongoing changes. The task settings should include table mappings that define which tables to replicate and any transformation rules. Here’s a simple table mapping that captures all tables from a specific schema:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "include-sales-schema",
      "object-locator": {
        "schema-name": "sales",
        "table-name": "%"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "add-prefix",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "sales",
        "table-name": "%"
      },
      "rule-action": "add-prefix",
      "value": "cdc_"
    }
  ]
}

Organizing S3 Data for Optimal Processing

The way you structure data in S3 significantly impacts both processing efficiency and cost. A well-designed S3 structure separates raw CDC data from processed data and uses partitioning to enable incremental processing. Consider a three-tier structure:

Raw Layer – This is where DMS writes data directly. Organize by database, schema, table, and date:

s3://my-data-lake/raw/database=sales_db/schema=public/table=orders/cdc_date=2024-10-20/

Staged Layer – After initial Glue processing but before final transformations:

s3://my-data-lake/staged/sales/orders/year=2024/month=10/day=20/

Curated Layer – Fully transformed and ready for Redshift:

s3://my-data-lake/curated/sales/orders/year=2024/month=10/day=20/

This layered approach provides checkpoints in your pipeline. If transformation logic changes, you can reprocess from the staged layer without re-extracting from the source database. Each layer can also have different retention policies—keeping raw data for compliance while purging staged data after successful processing.

DMS writes metadata columns to track CDC operations. The most important is the Op column, which indicates operation type: ‘I’ for insert, ‘U’ for update, and ‘D’ for delete. Your Glue jobs will use this column to determine how to apply changes in Redshift. DMS also includes before-image data for updates and deletes when properly configured, allowing you to see what values changed.

Building Glue ETL Jobs for CDC Processing

AWS Glue jobs transform raw CDC data into analytics-ready formats. The key challenge in CDC processing is maintaining data freshness while handling the complexity of inserts, updates, and deletes. A robust Glue job must merge CDC changes into existing data, handling scenarios where multiple changes affect the same record.

Here’s a PySpark script that demonstrates core CDC processing logic:

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql.functions import col, max as spark_max, row_number
from pyspark.sql.window import Window

args = getResolvedOptions(sys.argv, ['JOB_NAME', 'source_path', 'target_path'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# Read CDC data from S3
cdc_df = spark.read.parquet(args['source_path'])

# Filter out deletes temporarily - we'll handle them separately
active_changes = cdc_df.filter(col("Op").isin("I", "U"))

# For records with multiple changes, keep only the latest
window_spec = Window.partitionBy("id").orderBy(col("cdc_timestamp").desc())
deduplicated = active_changes.withColumn("row_num", row_number().over(window_spec)) \
    .filter(col("row_num") == 1) \
    .drop("row_num", "Op", "cdc_timestamp")

# Read existing data from curated layer if it exists
try:
    existing_df = spark.read.parquet(args['target_path'])
    
    # Merge: update existing records and add new ones
    merged_df = existing_df.alias("existing") \
        .join(deduplicated.alias("new"), "id", "full_outer") \
        .select([
            coalesce(col("new." + c), col("existing." + c)).alias(c)
            for c in existing_df.columns
        ])
except:
    # First load - no existing data
    merged_df = deduplicated

# Handle deletes by filtering them out
deletes = cdc_df.filter(col("Op") == "D").select("id")
final_df = merged_df.join(deletes, "id", "left_anti")

# Write back to curated layer
final_df.write.mode("overwrite").parquet(args['target_path'])

job.commit()

This script handles the complete CDC merge pattern: deduplicating changes, merging with existing data, and removing deleted records. The deduplication step is crucial because CDC pipelines often capture multiple changes to the same row within a processing window. You need the final state, not every intermediate change.

For large tables, consider using Glue’s bookmark feature to track which CDC files have been processed. This enables incremental processing where each job run only processes new CDC data rather than reprocessing everything. You can also partition the output by date or other dimensions to improve query performance in Redshift.

Loading Data into Redshift

Once Glue has prepared your curated data, loading it into Redshift requires consideration of both initial bulk loads and ongoing incremental updates. Redshift’s COPY command provides the most efficient way to load data from S3, leveraging Redshift’s massively parallel processing architecture.

For the initial full load, use a straightforward COPY command:

COPY sales.orders
FROM 's3://my-data-lake/curated/sales/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s3-role'
FORMAT AS PARQUET;

For incremental CDC loads, you have two main strategies. The first approach uses a staging table pattern where you load CDC changes into a temporary table, then merge them into your target table:

-- Create staging table
CREATE TEMP TABLE orders_staging (LIKE sales.orders);

-- Load CDC changes
COPY orders_staging
FROM 's3://my-data-lake/curated/sales/orders/year=2024/month=10/day=23/'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-s3-role'
FORMAT AS PARQUET;

-- Merge changes using DELETE + INSERT pattern
BEGIN TRANSACTION;

-- Remove records that will be updated
DELETE FROM sales.orders
WHERE order_id IN (SELECT order_id FROM orders_staging);

-- Insert all new and updated records
INSERT INTO sales.orders
SELECT * FROM orders_staging;

END TRANSACTION;

The second approach leverages Redshift’s MERGE command (if using a recent Redshift version that supports it), which handles the upsert logic in a single statement:

MERGE INTO sales.orders
USING orders_staging
ON sales.orders.order_id = orders_staging.order_id
WHEN MATCHED THEN UPDATE SET
    customer_id = orders_staging.customer_id,
    order_date = orders_staging.order_date,
    total_amount = orders_staging.total_amount,
    status = orders_staging.status
WHEN NOT MATCHED THEN INSERT VALUES (
    orders_staging.order_id,
    orders_staging.customer_id,
    orders_staging.order_date,
    orders_staging.total_amount,
    orders_staging.status
);

Orchestrating the Pipeline with Step Functions

While you could trigger Glue jobs and Redshift loads manually, production pipelines require orchestration to handle dependencies, retries, and error handling. AWS Step Functions provides a serverless orchestration service well-suited for CDC pipelines. You can create a state machine that coordinates the entire flow from DMS completion through Glue processing to Redshift loading.

A typical Step Functions workflow includes these states:

• Wait for DMS completion – Poll DMS task status or trigger on S3 event
• Run Glue crawler – Update catalog with new CDC data structure
• Execute Glue ETL job – Transform raw CDC to curated format
• Check for errors – Validate Glue job completion and data quality
• Load to Redshift – Execute COPY and MERGE commands
• Send notifications – Alert on success or failure via SNS

You can trigger this state machine on a schedule using EventBridge, or configure S3 event notifications to trigger it whenever DMS writes new CDC files. The event-driven approach provides the lowest latency but requires careful handling of concurrent executions if CDC files arrive rapidly.

Error handling is particularly important for CDC pipelines. If a Glue job fails mid-processing, you need to ensure subsequent runs don’t skip data or create duplicates. Step Functions’ built-in retry and catch mechanisms help, but your Glue jobs should also implement idempotency by tracking which CDC files have been successfully processed.

Monitoring and Maintaining Pipeline Health

Production CDC pipelines require comprehensive monitoring to catch issues before they impact analytics. Key metrics to monitor include:

• DMS replication lag – Time between source database commits and CDC file arrival in S3
• Glue job duration and failures – Track processing time trends and error rates
• Redshift load latency – Measure time from curated data availability to Redshift visibility
• Data quality metrics – Row counts, null rates, and business-specific validations

CloudWatch dashboards can aggregate these metrics, while CloudWatch Alarms notify you when thresholds are breached. For DMS, monitor the CDCLatencySource and CDCLatencyTarget metrics. For Glue, track glue.driver.aggregate.numFailedTasks and execution times. Redshift’s system tables provide detailed load statistics through STL_LOAD_ERRORS and STL_LOAD_COMMITS.

Regular maintenance tasks include vacuuming and analyzing Redshift tables after CDC loads, reviewing and optimizing Glue job logic as data volumes grow, and periodically validating that your CDC pipeline maintains data consistency with source systems. Consider implementing a reconciliation process that periodically compares row counts and checksums between source and target systems.

Conclusion

Building a CDC pipeline on AWS using S3, Glue, and Redshift creates a flexible, scalable foundation for modern analytics. The combination of DMS for reliable change capture, S3’s cost-effective storage, Glue’s serverless transformation capabilities, and Redshift’s analytics performance provides an end-to-end solution that can handle everything from small departmental databases to enterprise-wide data integration.

The layered architecture—with raw, staged, and curated zones—provides both operational flexibility and governance capabilities. As your data needs evolve, this pipeline can scale by adding more DMS tasks, parallelizing Glue jobs, or expanding Redshift cluster size. The key to success lies in thoughtful initial design of your S3 structure, robust CDC merge logic in Glue, and careful orchestration of the complete pipeline.