AWS DMS data validation

Oracle

PostgreSQL-compatible database (PostgreSQL, Aurora PostgreSQL, or Aurora Serverless for PostgreSQL)

MySQL-compatible database (MySQL, MariaDB, Aurora MySQL, or Aurora Serverless for MySQL)

Microsoft SQL Server

IBM Db2 LUW

Oracle

PostgreSQL-compatible database (PostgreSQL, Aurora PostgreSQL, or Aurora Serverless for PostgreSQL)

MySQL-compatible database (MySQL, MariaDB, Aurora MySQL, or Aurora Serverless for MySQL)

Microsoft SQL Server

IBM Db2 LUW

Amazon Redshift

Amazon S3. For information about validating Amazon S3 target data, see Amazon S3 target data validation.

ValidationState—The validation state of the table. The parameter can have the following values:

ValidationPending—The number of records that have been migrated to the target, but that haven't yet been validated.

ValidationSuspended—The number of records that AWS DMS can't compare. For example, if a record at the source is constantly being updated, AWS DMS can't compare the source and the target.

ValidationFailed—The number of records that didn't pass the data validation phase.

On the console, you can choose to validate a task when you create or modify the task. To view the data validation report using the console, choose the task on the Tasks page and choose the Table statistics tab in the details section.

Using the CLI, set the EnableValidation parameter to true when creating or modifying a task to begin data validation. The following example creates a task and enables data validation.

create-replication-task  
  --replication-task-settings '{"ValidationSettings":{"EnableValidation":true}}' 
  --replication-instance-arn arn:aws:dms:us-east-1:5731014:
     rep:36KWVMB7Q  
  --source-endpoint-arn arn:aws:dms:us-east-1:5731014:
     endpoint:CSZAEFQURFYMM  
  --target-endpoint-arn arn:aws:dms:us-east-1:5731014:
     endpoint:CGPP7MF6WT4JQ 
  --migration-type full-load-and-cdc 
  --table-mappings '{"rules": [{"rule-type": "selection", "rule-id": "1", 
     "rule-name": "1", "object-locator": {"schema-name": "data_types", "table-name": "%"}, 
     "rule-action": "include"}]}'

Use the describe-table-statistics command to receive the data validation report in JSON format. The following command shows the data validation report.

aws dms  describe-table-statistics --replication-task-arn arn:aws:dms:us-east-1:5731014:
rep:36KWVMB7Q

The report would be similar to the following.

{
    "ReplicationTaskArn": "arn:aws:dms:us-west-2:5731014:task:VFPFTYKK2RYSI", 
    "TableStatistics": [
        {
            "ValidationPendingRecords": 2, 
            "Inserts": 25, 
            "ValidationState": "Pending records", 
            "ValidationSuspendedRecords": 0, 
            "LastUpdateTime": 1510181065.349, 
            "FullLoadErrorRows": 0, 
            "FullLoadCondtnlChkFailedRows": 0, 
            "Ddls": 0, 
            "TableName": "t_binary", 
            "ValidationFailedRecords": 0, 
            "Updates": 0, 
            "FullLoadRows": 10, 
            "TableState": "Table completed", 
            "SchemaName": "d_types_s_sqlserver", 
            "Deletes": 0
        }
}

Using the AWS DMS API, create a task using the CreateReplicationTask action and set the EnableValidation parameter to true to validate the data migrated by the task. Use the DescribeTableStatistics action to receive the data validation report in JSON format.

ValidationSucceededRecordCount— Number of rows that AWS DMS validated, per minute.

ValidationAttemptedRecordCount— Number of rows that validation was attempted, per minute.

ValidationFailedOverallCount— Number of rows where validation failed.

ValidationSuspendedOverallCount— Number of rows where validation was suspended.

ValidationPendingOverallCount— Number of rows where the validation is still pending.

ValidationBulkQuerySourceLatency— AWS DMS can do data validation in bulk, especially in certain scenarios during a full-load or on-going replication when there are many changes. This metric indicates the latency required to read a bulk set of data from the source endpoint.

ValidationBulkQueryTargetLatency— AWS DMS can do data validation in bulk, especially in certain scenarios during a full-load or on-going replication when there are many changes. This metric indicates the latency required to read a bulk set of data on the target endpoint.

ValidationItemQuerySourceLatency— During on-going replication, data validation can identify on-going changes and validate those changes. This metric indicates the latency in reading those changes from the source. Validation can run more queries than required, based on number of changes, if there are errors during validation.

ValidationItemQueryTargetLatency— During on-going replication, data validation can identify on-going changes and validate the changes row by row. This metric gives us the latency in reading those changes from the target. Validation may run more queries than required, based on number of changes, if there are errors during validation.

Control exactly when validation occurs — Validation queries add an additional load to both source and target endpoints. So, migrating or replicating data in one task first, then validating the results in another task can be beneficial.

Reduce load on the replication instance — Splitting data validation to run on its own instance can be advantageous.

Quickly obtain how many rows don't match at a given moment in time — For example, just before or during a maintenance window production cut–over to a target endpoint, you can create a Full Load validation only task to get an answer to your question.

When validation failures are expected for a migration task with a CDC component — For example, if migrating Oracle varchar2 to PostgreSQL jsonb, CDC validation keeps retrying these failed rows and limits the number of failures reported each time. But, you can create a Full Load validation only task and obtain a quicker answer.

You've developed a data repair script/utility that reads the validation failure table — (See also, Troubleshooting). A Full Load validation only task quickly reports failures for the data repair script to act upon.

Query volume

Types of DML queries on the source table

Task latency

Total number of records

Validation settings such as PartitionSize

Reduce the PartitionSize and ThreadCount settings to help reduce the workload during full-load validation. Note that this will slow down data validation.

While Redshift doesn’t enforce primary keys, AWS DMS relies on primary keys to uniquely identify records on the target for data validation. If possible, set the primary key to mirror the sort key so that full load validation queries execute more quickly.

Oracle: grant the EXECUTE permission on SYS.DBMS_CRYPTO to the user account that accesses the Oracle endpoint:

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO dms_endpoint_user;

Install the pgcrypto extension on the PostgreSQL database:

For self-managed PostgreSQL instances, you need to install the contrib module libraries and create the extension:

For Amazon RDS for PostgreSQL instances, configure the SSL mode for the AWS DMS endpoint:

For PostgreSQL 12 and 13, create the BIT_XOR aggregate:

CREATE OR REPLACE AGGREGATE BIT_XOR(IN v bit) (SFUNC = bitxor, STYPE = bit);

Database endpoint requirements: This improvement is enabled only for database endpoints that meet the following criteria:

Database version support:

Data validation requires that the table has a primary key or unique index.

Validation of NULL PK/UK values aren't supported.

If the collation of the primary key column in the target PostgreSQL instance isn't set to "C", the sort order of the primary key is different compared to the sort order in Oracle. If the sort order is different between PostgreSQL and Oracle, data validation fails to validate the records.

Data validation generates additional queries against the source and target databases. You must ensure that both databases have enough resources to handle this additional load. This is especially true for Redshift targets. For more information, see Redshift Validation Performance following.

Data validation isn't supported when consolidating several databases into one.

For a source or target Oracle endpoint, AWS DMS uses DBMS_CRYPTO to validate LOBs. If your Oracle endpoint uses LOBs, then you must grant the execute permission on dbms_crypto to the user account used to access the Oracle endpoint. You can do this by running the following statement:

grant execute on sys.dbms_crypto to dms_endpoint_user;

If the target database is modified outside of AWS DMS during validation, then discrepancies might not be reported accurately. This result can occur if one of your applications writes data to the target table, while AWS DMS is performing validation on that same table.

If one or more rows are being continuously modified during validation, then AWS DMS can't validate those rows.

If AWS DMS detects more than 10,000 failed or suspended records, it stops the validation. Before you proceed further, resolve any underlying problems with the data.

AWS DMS doesn't support data validation of views.

AWS DMS doesn't support data validation when character substitution task settings are used.

AWS DMS doesn't support validating the Oracle LONG type.

AWS DMS doesn't support validating the Oracle Spatial type during heterogeneous migration.

Data validation ignores those columns in tables for which data masking transformations exists in table mapping.

Data validation skips an entire table if there is a data masking transformation rule for its PK/UK column. The validation state will show as No primary key for such tables.