Migrating Data to AWS: DMS and Best Practices

Most data platforms begin with someone else's database. The orders, customers and payments that analysts want live in an operational system, and they have to be copied somewhere analytical without disturbing the application that owns them. AWS Database Migration Service, DMS, is the AWS tool for that job. Despite the name, it is used for continuous replication into a lake or warehouse as well as for one-off migrations.

AWS describes DMS as a cloud service for migrating relational databases, data warehouses, NoSQL databases and other data stores, either as one-time migrations or by replicating ongoing changes to keep sources and targets in sync (DMS docs: what is AWS DMS).

The three components

The guide's own summary is disarmingly simple: at a basic level, DMS is a server in the AWS Cloud that runs replication software.

• Replication instance. The managed server that does the work. DMS Serverless removes this piece, provisioning and scaling capacity automatically (DMS docs: DMS Serverless).
• Endpoints. The connections to the source and the target. Engines can be the same on both sides or different, and one endpoint must be on an AWS service (DMS docs: concepts).
• Task. The definition of what to move: which tables, with which rules, in which mode.

For engine changes, such as Oracle to PostgreSQL, a separate schema conversion step translates table definitions and code. DMS itself moves data.

Three kinds of task

Ongoing replication is change data capture. DMS collects changes from the database's logs using the engine's native API, so it sees inserts, updates and deletes without querying the tables repeatedly. CDC explained covers why reading the log beats polling.

One expectation to set early. The guide states that DMS CDC does not provide real-time replication, that latency can increase to several minutes or longer, and that there are no SLAs for CDC latency (DMS docs: ongoing replication). It is a tool for near-real-time analytics, not for systems that need a guaranteed delay.

DMS into a data lake

With S3 as the target, DMS writes files (DMS docs: S3 as a target):

• Full load files, one set per table, in a folder per schema and table.
• CDC files, named by timestamp, containing the changes since.
• Format: .csv by default, or Parquet.
• An operation field. Each CDC record carries an added first field: I for insert, U for update, D for delete.

That last point is the one that matters. DMS delivers a log of changes, not a current table. If a customer's country changes twice, the lake receives the original row and two U rows. Querying the raw folder as if it were the table gives duplicates and resurrects deleted rows.

Turning changes into a table

The script below shows the step every DMS-to-lake pipeline needs. The full load is the real customer table from the Chinook sample database, 59 rows. The five change records are written by hand in DMS's documented format. It runs locally; DMS itself is not involved.

import sqlite3
import duckdb
import pandas as pd

# Full load: what DMS writes first, one row per source row (here, Chinook's 59 customers)
full_load = pd.read_sql("SELECT CustomerId, FirstName, LastName, Country FROM Customer",
                        sqlite3.connect("chinook.sqlite"))

# CDC files: later changes, each row prefixed with the operation and a commit timestamp
cdc = pd.DataFrame(
    [("U", "2026-10-01 09:00:05", 12, "Roberto", "Almeida", "Portugal"),     # customer 12 moves country
     ("I", "2026-10-01 09:02:11", 60, "Asha", "Verma", "India"),             # a new customer
     ("U", "2026-10-01 09:07:40", 60, "Asha", "Verma", "Singapore"),         # ...who is then corrected
     ("D", "2026-10-01 09:15:02", 59, "Puja", "Srivastava", "India"),        # customer 59 is deleted
     ("U", "2026-10-01 09:30:00", 12, "Roberto", "Almeida", "Brazil")],      # customer 12 moves back
    columns=["Op", "commit_ts", "CustomerId", "FirstName", "LastName", "Country"])

con = duckdb.connect()
current = con.execute("""
    WITH events AS (
        SELECT 'I' AS Op, TIMESTAMP '2026-10-01 00:00:00' AS commit_ts, * FROM full_load
        UNION ALL
        SELECT Op, CAST(commit_ts AS TIMESTAMP), CustomerId, FirstName, LastName, Country FROM cdc
    ),
    latest AS (                                  -- the last event for each key wins
        SELECT *, row_number() OVER (PARTITION BY CustomerId ORDER BY commit_ts DESC) AS rn FROM events
    )
    SELECT CustomerId, FirstName, LastName, Country FROM latest
    WHERE rn = 1 AND Op <> 'D'                   -- and a delete removes the row
    ORDER BY CustomerId
""").df()

print("full load rows:", len(full_load), "| change records:", len(cdc), "| current rows:", len(current))
print(current[current["CustomerId"].isin([12, 59, 60])].to_string(index=False))
print("naive append would give:", len(full_load) + len(cdc), "rows")

full load rows: 59 | change records: 5 | current rows: 59
 CustomerId FirstName LastName   Country
         12   Roberto  Almeida    Brazil
         60      Asha    Verma Singapore
naive append would give: 64 rows

Five changes arrive and the table still has 59 rows, which is correct:

• Customer 12 was updated twice. Only the last state, Brazil, survives.
• Customer 60 was inserted and then corrected. One row, with the corrected country.
• Customer 59 was deleted, and is gone.

Appending the change files instead would give 64 rows and wrong results for all three customers. The rule is the same in any engine: for each key take the latest event by commit order, and drop keys whose latest event is a delete. In production this runs as a scheduled merge into a curated table, often using a table format such as Iceberg or Delta Lake so that updates and deletes are applied in place. Two details make it reliable:

• Order by a commit timestamp or sequence that DMS can add to each record, not by file arrival time.
• Make the merge idempotent, so that reprocessing a file changes nothing. See idempotency in data pipelines.

If history matters, keep every change record as well. The raw log is exactly what a slowly changing dimension is built from.

Practices from the documentation

The best-practices page is worth reading in full (DMS docs: best practices). The points that affect most projects:

Protect the source. A full load performs a full table scan of each table processed in parallel, and every task queries the source for changes. If the source is overburdened, reduce the number of tasks or the tables per task. Some engines need more written to their change log for CDC to work, which is a change the database's owners must agree to.

Unblock the target. During the initial load, turn off unnecessary triggers and secondary indexes on the target, and turn them back on afterwards.

Validate. AWS highly recommends data validation: once a table's full load completes, DMS compares source and target data. For targets where that is not supported, such as S3, reconcile row counts and checksums yourself.

Run a proof of concept with real data volumes before committing to sizing.

Plan for large objects. LOB columns have their own settings and need attention before a large migration.

Monitor latency. DMS publishes task metrics to CloudWatch. Alarm on replication lag, since a stalled task fails quietly; CloudWatch for data pipelines covers the alarm pattern.

A migration, in outline

1. Assess the source: size, engine, large objects, tables without primary keys.
2. Prepare the network so the replication instance reaches both endpoints, and create least-privilege database users.
3. Convert the schema if the engine is changing.
4. Run a full load and CDC task on a subset, and validate.
5. Run it for everything. Let CDC catch up.
6. Cut over, for a migration: stop writes to the source, let the last changes apply, switch the application.
7. For a lake feed, there is no cutover; the task keeps running, and the merge job keeps the curated tables current.

When DMS is not the right tool

DMS is deliberately narrow: it moves and replicates data faithfully. Cleaning, modelling and joining belong in the layers after it; building a data lake on AWS covers where its output should land, and Amazon Redshift explained a common final destination.

Data ingestion and ETL pipeline design on AWS are covered in the Data Engineering course.

Sources

• What is AWS Database Migration Service? (AWS DMS User Guide) — AWS Documentation
• AWS DMS terminology and concepts (AWS DMS User Guide) — AWS Documentation
• Creating tasks for ongoing replication using AWS DMS (AWS DMS User Guide) — AWS Documentation
• Using Amazon S3 as a target for AWS Database Migration Service (AWS DMS User Guide) — AWS Documentation
• Best practices for AWS Database Migration Service (AWS DMS User Guide) — AWS Documentation
• Working with AWS DMS Serverless (AWS DMS User Guide) — AWS Documentation