Logo
AWS AWS DMS CDC database migration data validation

AWS DMS: data validation reports mismatches after full load + CDC

Fix DMS data validation mismatches after full load + CDC: diagnose ValidationState and ValidationFailedRecords, type mismatches, truncated LOBs, collation, and row ordering before cutover.

Jerzy Kopaczewski ·
Full load finished, CDC latency is near zero, the task looks cutover-ready - but DMS data validation reports `ValidationFailedRecords`, or the row count on the target doesn't match the source. Switching production over with divergent data means silent data corruption that only surfaces when users complain. This runbook shows how to diagnose and clear the discrepancies before you cut over.

This runbook covers data discrepancies detected by AWS DMS validation. If the problem is rising CDC latency or a task failure instead, see the runbook AWS DMS: rising CDC latency and task failure. For the full guide to the service: AWS DMS - Database Migration Service Guide, and for heterogeneous migrations Oracle to PostgreSQL Migration on AWS. For cutover planning, book a consulting session.

Symptoms

The DMS task is “Running”, full load is complete, but data validation won’t drop to zero errors. Typical signals:

# Validation state per table - which tables have discrepancies
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --query 'TableStatistics[?ValidationFailedRecords>`0` || ValidationState!=`Validated`].{schema:SchemaName, table:TableName, valState:ValidationState, failed:ValidationFailedRecords, pending:ValidationPendingRecords, suspended:ValidationSuspendedRecords}' \
  --output table

# Typical ValidationState values:
#   Validated                 - consistent (target state)
#   Mismatched records        - rows with differing content
#   Suspended validation      - validation suspended (e.g. no key)
#   Error                     - validation process error

# Row count difference source vs target (quick sanity check)
# source Oracle:      SELECT COUNT(*) FROM app_schema.orders;
# target Aurora PostgreSQL: SELECT COUNT(*) FROM app_schema.orders;

ValidationFailedRecords > 0, ValidationState = "Mismatched records", or the target row count differs from the source. The task doesn’t report a failure - the data is incomplete or inconsistent despite a “green” replication status.

Cause

Validation mismatches after full load + CDC usually have one of four causes:

  • Truncated LOBs (Limited LOB mode): the default DMS mode truncates BLOB/CLOB/BYTEA columns above LobMaxSize. Full load and CDC work, but the LOB values on the target are shorter than on the source - validation compares content and detects a mismatch. This is the most common silent cause of discrepancies.
  • Type and precision mismatch after heterogeneous conversion: Oracle NUMBER without scale → PostgreSQL numeric/double precision, DATE with a time component, TIMESTAMP WITH TIME ZONE, CHAR mapping with space padding. After conversion the same logical value is stored differently and validation reports a difference.
  • Collation and text comparison: different sort/comparison rules (case sensitivity, trailing spaces, database collation) make DMS treat rows as divergent even though they are equivalent at the application level. This especially affects text keys.
  • Tables without a primary key or with unstable ordering: DMS validation matches rows by key. A missing primary key/unique index causes Suspended validation, and on large tables without a deterministic order it produces false mismatches.

Fix

A) Determine the nature of the discrepancy - which columns are affected:

# Enable detailed validation logging in the task settings (task settings JSON):
# "ValidationSettings": {
#   "EnableValidation": true,
#   "ValidationMode": "ROW_LEVEL",
#   "RecordFailureDelayInMinutes": 5,
#   "TableFailureMaxCount": 1000,
#   "FailureMaxCount": 10000,
#   "ValidationPartialLobSize": 0,      # 0 = validate the full LOB, not just a prefix
#   "PartitionSize": 10000
# }

# Details of the divergent rows land in a control table on the target:
#   awsdms_validation_failures_v1  (DMS control schema on the target database)
# Inspect which columns differ:
# SELECT TABLE_NAME, COLUMN_NAME, FAILURE_TYPE, KEY, DETAILS
# FROM awsdms_control.awsdms_validation_failures_v1
# ORDER BY FAILURE_TIME DESC LIMIT 50;

B) Truncated LOBs - switch to Full LOB and validate the full content:

# In the task settings for tables with large LOBs:
# "TargetMetadata": {
#   "SupportLobs": true,
#   "FullLobMode": true,          # full LOBs without truncation
#   "LobChunkSize": 64,
#   "LimitedSizeLobMode": false
# }
# and in ValidationSettings set "ValidationPartialLobSize": 0.

# After the change stop and resume the task; to be safe, reload the affected tables:
aws dms start-replication-task \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --start-replication-task-type reload-target

C) Type mismatch - pin the mapping in transformation rules:

-- Verify the specific divergence on a sample key (source vs target)
-- Oracle:
SELECT order_id, DUMP(amount) FROM app_schema.orders WHERE order_id = 4711;
-- PostgreSQL:
SELECT order_id, amount, pg_typeof(amount) FROM app_schema.orders WHERE order_id = 4711;

-- Common fixes:
--  * NUMBER without scale -> force numeric(p,s) instead of double precision (SCT / DDL on target)
--  * CHAR(n) -> trim trailing spaces via a DMS transformation rule or switch to VARCHAR
--  * Oracle DATE with time -> make sure the target is timestamp, not date

# DMS transformation rules (table mapping) - example: trim spaces in a text key
# {
#   "rule-type": "transformation",
#   "rule-action": "convert-lowercase",   # or rtrim via an expression in SQL on the target
#   "rule-target": "column",
#   "object-locator": { "schema-name": "APP_SCHEMA", "table-name": "CUSTOMERS", "column-name": "CODE" }
# }

D) Collation and keyless tables - align comparison or scope validation:

# For tables without a primary key: add a key on the source before migration (preferred),
# or tell DMS which columns form a unique validation key in the table mapping:
# {
#   "rule-type": "validation",
#   "rule-action": "override-validation-function",
#   "object-locator": { "schema-name": "APP_SCHEMA", "table-name": "EVENTS" },
#   "rule-settings": { "validate-function": "NONE" }   # last resort: disable for this table
# }

# For discrepancies caused by text collation - align the column collation on the target
# (PostgreSQL): ALTER TABLE app_schema.customers ALTER COLUMN code TYPE text COLLATE "C";

Prevention: enable data validation from the start of the migration, not on cutover day - ValidationState and the awsdms_validation_failures_v1 table surface problems while there’s still time to fix them. For heterogeneous migrations, review the SCT report for types with incomplete mapping before you start the task (see the runbook on SCT conversion errors). For large LOB tables, plan Full LOB mode and a larger replication instance up front.

Validation

# 1. No table has discrepancies and nothing is in a state other than Validated
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --query 'TableStatistics[?ValidationState!=`Validated` || ValidationFailedRecords>`0`].{table:TableName, state:ValidationState, failed:ValidationFailedRecords}' \
  --output table
# Expected: empty list

# 2. Validation summary per table (zero failed/pending/suspended)
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --query 'TableStatistics[].{table:TableName, valid:ValidationState, failed:ValidationFailedRecords, pending:ValidationPendingRecords, suspended:ValidationSuspendedRecords}' \
  --output table
# Expected: ValidationState = "Validated", failed = pending = suspended = 0

# 3. Row-count sanity check on critical tables (source vs target)
# Oracle:            SELECT COUNT(*) FROM app_schema.orders;
# Aurora PostgreSQL: SELECT COUNT(*) FROM app_schema.orders;
# Expected: identical counts (with writes stopped on the source in the cutover window)

If validation reports Validated for all tables, ValidationFailedRecords = 0, and the row counts match, the data is consistent and the task is ready for cutover. Keep replication running and repeat the sanity check right before switchover, after stopping writes on the source.

A data discrepancy that DMS validation catches is a problem you can fix before cutover - truncated LOBs, mismatched types, or collation. The dangerous move is switching production over despite these signals: the task shows "green", and incomplete or inconsistent data reaches production unnoticed until users report it or billing breaks. That's why data validation is treated as a cutover gate, not an optional extra.

 

Jerzy Kopaczewski

DMS validation reporting mismatches before cutover?

Book a free 30-minute call. We'll review your validation settings, type mapping and LOB mode, and get the data consistent before switchover.

Book a call