AWS DMS full load - 1 table - What order is the data selected from source db to target

Hello,

I could understand that you would like to know in what order the source table data is retrieved in DMS and is it order by.

Regarding your query, I would kindly like to inform you that, in order to understand your query better I replicated your use-case in my test environment and below are my findings:-

1. Created a Full_Load DMS task.
2. Created a table with 1 column and another table with 3 columns on the source.

>>> create table test(id int);
CREATE TABLE

>>> create table qwerty1(id int,id1 int,id2 int);
CREATE TABLE

1. For table with 1 column:-

LOG: statement: BEGIN;declare "SQL_CUR0x1526a80e2a70" cursor with hold for SELECT "id" FROM "public"."test";

2. For table with 3 columns:-

LOG: statement: BEGIN;declare "SQL_CUR0x1502b812bec0" cursor with hold for SELECT "id","id1","id2" FROM "public"."qwerty1";

With the help of logs I could find what query DMS ran during the full load. As we can see from the above output that DMS doesn't use order by. For the table with 1 column DMS ran SELECT column FROM tablename; similarly for table with 3 columns DMS ran SELECT column1,column2,column3 FROM tablename;

I sincerely hope above information is helpful for you. Have a great rest of your week!

AWS Database Migration Service (DMS) is capable to assessing your source table and determining the best possible way to execute the full load jobs. If the source table is partitioned then during a full load task, AWS DMS performs a full table scan for each of the source tables in parallel. For your specific table you can check into DMS logs for details. Also review your full load job settings for MaxFullLoadSubTasks which controls the number of tables or table segments to load in parallel, and ParallelLoadThreads which controls the number of threads that are used by a migration task to execute the loads in parallel. These settings are multiplicative and determine the source table scans.

This AWS white paper Optimizing AWS Database Migration Service Performance with Amazon Redshift as Target covers in-depth the various database migration considerations, preparing source database, optimizing AWS DMS performance, preparing Amazon Redshift as a target for AWS DMS, testing the migration and even troubleshooting issues.