AWS AWS DMS Redshift S3 CDC data warehouse

AWS DMS to Redshift/S3: apply errors in a data warehouse pipeline

Fix apply errors in a continuous AWS DMS task to Redshift or S3: diagnose stl_load_errors, VARCHAR overflow, IAM/S3 permissions, and instance sizing for an indefinitely-running CDC stream.

Jerzy Kopaczewski · 2026-08-20

A continuous AWS DMS task feeding a data warehouse (Redshift or S3) starts throwing apply errors - rows don't reach the target and the CDC stream backs up. This isn't a one-off migration, it's a permanent OLTP → warehouse pipeline, so a failure means your analytics data drifts from production. This runbook shows how to diagnose and fix errors on the target side.

This runbook covers apply errors in a continuous DMS task to a data warehouse. For the basics of the service and its replication modes, see AWS DMS - Database Migration Service Guide. For analytics pipeline architecture and cost optimisation, book a consulting session.

Symptoms

The DMS task to a Redshift or S3 target is in the “Running” state, but table statistics show apply errors and the warehouse data diverges from the source:

# Table statistics - look for apply errors (DdlErrors / DataErrors)
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:OLTP2REDSHIFT \
  --query 'TableStatistics[?TableState==`Table error` || Ddls>`0`].{schema:SchemaName, table:TableName, state:TableState, inserts:Inserts, updates:Updates, deletes:Deletes}' \
  --output table

-- On the Redshift side: load errors from the COPY layer DMS uses under the hood
SELECT starttime, filename, colname, type, col_length, err_reason
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 20;

-- Common err_reason values:
-- "String length exceeds DDL length"        -> VARCHAR overflow
-- "Invalid digit, Value '...', Pos 0"       -> type mismatch (text into a numeric column)
-- "Missing newline: Unexpected character"   -> wrong delimiter/escape settings

In the task logs (CloudWatch Logs), an S3 target also commonly shows access-denied messages: Access Denied when writing to the bucket, or Unable to assume role.

Cause

In a continuous warehouse pipeline, target-side errors usually come down to one of three causes:

VARCHAR overflow / type mismatch in Redshift: DMS maps source types to target types, but Redshift is strict about VARCHAR length (measured in bytes, not characters - multi-byte UTF-8 characters take up more). A column that fits on the source overflows a narrower target column, and the whole row is rejected by COPY. This is the most common cause of rising DataErrors.
IAM permissions / S3 access: DMS writes to the S3 target (and to a staging bucket for Redshift) via an IAM role. Missing s3:PutObject/s3:DeleteObject on the bucket, a broken role trust relationship, or a bucket policy blocking writes - all end in Access Denied and stalled apply.
Instance sizing for an indefinitely-running stream: this is a pipeline running for years, not a few-week migration. As the source transaction volume grows, a replication instance sized for the original traffic stops keeping up - latency rises, and for a Redshift target the cost of frequent small COPY operations adds up versus batched loading.

Solution

A) VARCHAR overflow - widen the target column or force a wider mapping:

-- In Redshift: find the too-narrow column and compare to actual data length
SELECT col_length, err_reason, count(*)
FROM stl_load_errors
WHERE colname = 'customer_note'
GROUP BY col_length, err_reason;

-- Widen the column (Redshift VARCHAR is measured in BYTES)
ALTER TABLE analytics.customers ALTER COLUMN customer_note TYPE VARCHAR(2000);

# Alternatively force wider types at the DMS layer - a transformation rule in table mapping:
# a "change-data-type" rule, or a global setting.
# In task settings: "TargetMetadata": { "SupportLobs": true } for long text columns.
# For Redshift, also consider BatchApplyEnabled for batched throughput.

B) Access Denied on S3 - fix the role and bucket policy:

# Check which role the DMS target endpoint uses
aws dms describe-endpoints \
  --filters Name=endpoint-arn,Values=arn:aws:dms:eu-west-1:123456789012:endpoint:S3TARGET \
  --query 'Endpoints[0].S3Settings.ServiceAccessRoleArn'

# Verify the role can write to the bucket
aws iam simulate-principal-policy \
  --policy-source-arn arn:aws:iam::123456789012:role/dms-s3-target-role \
  --action-names s3:PutObject s3:DeleteObject s3:ListBucket \
  --resource-arns arn:aws:s3:::my-warehouse-bucket/* arn:aws:s3:::my-warehouse-bucket \
  --query 'EvaluationResults[].{action:EvalActionName, decision:EvalDecision}' --output table
# Expected: decision = allowed for every action

# If permissions are missing - add a policy to the role (minimal example)
aws iam put-role-policy \
  --role-name dms-s3-target-role \
  --policy-name dms-s3-write \
  --policy-document '{
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Action": ["s3:PutObject","s3:DeleteObject","s3:ListBucket","s3:GetBucketLocation"],
      "Resource": ["arn:aws:s3:::my-warehouse-bucket","arn:aws:s3:::my-warehouse-bucket/*"]
    }]
  }'

# Make sure the role trust relationship allows dms.amazonaws.com to sts:AssumeRole

C) Sizing and batch mode for a long-running stream:

# Scale up the replication instance if latency rises with volume
aws dms modify-replication-instance \
  --replication-instance-arn arn:aws:dms:eu-west-1:123456789012:rep:DMSDW \
  --replication-instance-class dms.r5.xlarge \
  --apply-immediately

# For a Redshift target, enable batch apply (fewer, larger COPY operations):
# task settings JSON: "TargetMetadata": { "BatchApplyEnabled": true }
# plus "BatchApplyTimeoutMin"/"BatchApplyTimeoutMax" and "BatchApplyMemoryLimit" sized to volume.

Long-running stream cost: a replication instance feeding a warehouse runs non-stop for years, so its cost is steady and predictable - the textbook case for a Database Savings Plan, which covers DMS instances the same way it does RDS and Aurora. It makes no sense for a one-off migration, but for a permanent CDC stream to Redshift/S3 it can take a substantial share of the cost off the bill. Details in the AWS DMS guide.

Prevention: design the Redshift target schema with VARCHAR length headroom (remember bytes vs characters in UTF-8) and enable the DMS Data Validation task. Alarm on stl_load_errors and monitor CDCLatencyTarget in CloudWatch. For a permanent pipeline, assign the instance cost to a Database Savings Plan rather than paying on-demand for years.

Validation

-- 1. No new load errors in Redshift over the last hour
SELECT count(*) AS recent_errors
FROM stl_load_errors
WHERE starttime > GETDATE() - interval '1 hour';
-- Expected: 0

-- 2. Row count on the target matches the source for a control table
SELECT count(*) FROM analytics.customers;

# 3. No task table is in an error state, latency stable
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:OLTP2REDSHIFT \
  --query 'TableStatistics[?TableState==`Table error`].{schema:SchemaName, table:TableName}' \
  --output table
# Expected: empty list

# 4. For an S3 target - files appear in the bucket per the expected partitioning
aws s3 ls s3://my-warehouse-bucket/cdc/analytics/customers/ --recursive | tail -5
# Expected: fresh files with a current timestamp

If stl_load_errors isn’t growing, no table is in an error state, and CDC files land in S3 per the expected partitioning, the pipeline is applying changes correctly. Monitor for 24 hours to confirm the error doesn’t return under variable volume.

In a continuous warehouse pipeline, a target-side apply error doesn't stop production - it stops analytics. That's more dangerous than it sounds: reports and dashboards keep rendering, just on incomplete data, so the problem can stay invisible until someone notices the numbers don't add up. VARCHAR overflow silently rejects whole rows. That's why data validation and an `stl_load_errors` alarm go in when the pipeline launches, not after the first complaint from the BI team.

Is your CDC pipeline to the warehouse drifting?

Book a free 30-minute call. We'll review your DMS configuration, target schema, and sizing, and stabilise the pipeline - along with optimising the cost of a long-running stream.

Book a call