Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Feature Request: True column exclusion in DMS source queries

5

When using remove-column transformation rules, DMS still fetches ALL columns from the source database and only removes them after retrieval. This means:

  • Large columns (CLOBs, BLOBs, etc.) are still transferred over the network
  • Truncation warnings appear in logs for columns that won't even be used
  • Unnecessary bandwidth and processing overhead
  • Confusing log entries about "failed" operations on data we're intentionally excluding

Requested Enhancement: Add support for column-level selection in source queries, similar to:

  {
      "rule-type": "selection",
      "rule-name": "select-specific-columns",
      "object-locator": {
          "schema-name": "schema",
          "table-name": "table"
      },
      "rule-action": "include",
      "include-columns": ["col1", "col2", "col3"],
      "exclude-columns": ["large_blob_col", "unused_clob_col"]
  }

Business Benefits:

  • Reduced network bandwidth - don't transfer data that will be discarded
  • Cleaner logs - eliminate truncation warnings for intentionally excluded columns
  • Better performance - especially for tables with large text/binary columns
  • Simplified troubleshooting - logs only show actual issues, not false positives

Use Cases:

  • Migrating tables with audit/history columns containing large documents
  • Excluding deprecated columns with binary data
  • Data lake ingestion where only specific columns are needed
  • Any scenario where source tables have columns too large for target constraints

This would make DMS more efficient and provide cleaner operational experience when working with tables containing mixed column sizes.

Topics
Migration & ModernizationAWS Well-Architected Framework
Tags
Performance EfficiencyAWS Database Migration Service
Language
English

asked a year ago465 views

3 Answers
1

Hi Jason,

I have tested using Aurora Postgres as source and do see that when a column is removed using transformation rule, DMS will not fetch the removed column from the source.

In my source Aurora Postgres database, I used the below sample table:

create table pgcoltest (c1 int primary key, c2 int, c3 int, c4 int);

I created a Full Load and CDC task with Aurora Postgres as source and Oracle as target.

Added the below selection rule:

    {
      "rule-type": "selection",
      "rule-id": "485422916",
      "rule-name": "485422916",
      "object-locator": {
        "schema-name": "public",
        "table-name": "pgcoltest"
      },
      "rule-action": "include",
      "filters": [

      ]
    }

Added the below transformation rule along with rules to convert the table name, schema name and column names to upper case:

{
      "rule-type": "transformation",
      "rule-id": "485476492",
      "rule-name": "485476492",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "public",
        "table-name": "pgcoltest",
        "column-name": "c3"
      },
      "rule-action": "remove-column",
      "value": null,
      "old-value": null
    },

I enabled detailed debug on SOURCE_UNLOAD component and started the task.

From the task logs, I can see the below SQL being executed by the SOURCE UNLOAD component:

2025-06-21T06:09:04:967555 [SOURCE_UNLOAD   ]D:  SELECT "c1","c2","c4"  FROM "public"."pgcoltest", postgres_construct_select_statement  (postgres_endpoint_unload.c:890)

In the source database, I had all statement logging enabled and can see the below SQL query being run from the DMS Task:

2025-06-21 06:09:04 UTC:10.0.2.41(37684):postgres@apg:[4754]:LOG:  statement: BEGIN;declare "SQL_CUR0x14e5140ef000" cursor with hold for SELECT "c1","c2","c4"  FROM "public"."pgcoltest";fetch 10000 in "SQL_CUR0x14e5140ef000"

We can see that the column "C3" is not selected by the source unload query.

I have tested with Oracle as source as well and can see the below from the DMS task logs:

2025-06-21T06:02:32:234578 [SOURCE_UNLOAD   ]D:  Select statement for UNLOAD is 'SELECT "C1","C2","C4"  FROM "ADMIN"."ORCLCOLTEST"'  (oracle_endpoint_utils.c:2212)
2025-06-21T06:02:32:237432 [SOURCE_UNLOAD   ]D:  Fetching records from source for table 'ADMIN'.'ORCLCOLTEST' with query 'SELECT "C1","C2","C4"  FROM "ADMIN"."ORCLCOLTEST"'  {operation:LOAD_START_OF_TABLE (31), tableName:ORCLCOLTEST, schemaName:ADMIN, connectionId:674}  (oracle_endpoint_unload.c:447)

Is there a specific engine where you have observed the behaviour wherein all columns are fetced from the source even if some columns were excluded? If so, please let me know the source engine, the DMS version being used and a sample table DDL of table being migrated along with the column which has to be excluded and I can try to reproduce the issue.

EDIT 1: 16-JUL-2025 - Summary of comments

  • OP was migrating from IBM DB2 LUW to Redshift using DMS 3.5.3

  • Table being migrated had two CLOB columns - OLD_DOCUMENT and NEW_DOCUMENT

  • DMS task had transformation rules to remove columns OLD_DOCUMENT and NEW_DOCUMENT

  • In my tests, when the task had transformation rules to remove the columns OLD_DOCUMENT and NEW_DOCUMENT, and convert lowercase rule for table and schema, the task was able to remove the OLD_DOCUMENT and NEW_DOCUMENT columns and selected only the subset of columns for migration. In the task logs, I see the below SQL for the unload:

[SOURCE_UNLOAD   ]D:  SQL statement constructed: SELECT "SERIAL","PARENT_SERIAL","SYSTEM_GENERATED","COLUMN_NAME","DATA_TYPE","OPERATION","OLD_CONTENTS","NEW_CONTENTS"  FROM "ADMIN"."FM_FIELD" WHERE ((("SERIAL" <= 50000000))) FOR READ ONLY   (db2luw_endpoint_util.c:264)

As we can see, the NEW_DOCUMENT and OLD_DOCUMENT columns were not selected.

However, if I add a table mapping transformation rule to convert-lowercase for all columns, then the SQL for unload changes to:

[SOURCE_UNLOAD   ]D:  SQL statement constructed: SELECT "SERIAL","PARENT_SERIAL","SYSTEM_GENERATED","COLUMN_NAME","DATA_TYPE","OPERATION","OLD_CONTENTS","NEW_CONTENTS","OLD_DOCUMENT","NEW_DOCUMENT"  FROM "ADMIN"."FM_FIELD" WHERE ((("SERIAL" <= 50000000))) FOR READ ONLY   (db2luw_endpoint_util.c:264)

This is expected behaviour as DMS has the below limitation with respect to transformation rules and actions:

"You cannot apply more than one transformation rule action against the same object (schema, table, column, table-tablespace, or index-tablespace). You can apply several transformation rule actions on any level as long as each transformation action is applied against a different object. However, this restriction is not applicable when using data masking transformation rules where you can have another transformation like ADD-COLUMN or CHANGE-DATA-TYPE for the same column." [1]

>When the convert-lowercase rule is used for all columns and we add a rule to remove OLD_DOCUMENT and NEW_DOCUMENT, then there are multiple transformation happening on columns OLD_DOCUMENT and NEW_DOCUMENT and DMS applies just one (i.e. convert lowercase). To convert all columns to lower case and remove certain columns, you will need to add transformation rules for each column to convert them to lower case.

[1]  Transformation rules and actions - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html
AWS
SUPPORT ENGINEER

answered a year ago

  • Jason
    a year ago

    Hi Ruchir,

    The engine I am using is DB2 (IBM LUV). The DMS Version used is 3.5.3.

    Here is an example output from CW Logs:

    2025-06-10T08:27:21 [SOURCE_UNLOAD   ]W:  Truncation of a column occurred while fetching a value. Statement: 'SELECT "SERIAL","PARENT_SERIAL","SYSTEM_GENERATED","COLUMN_NAME","DATA_TYPE","OPERATION","OLD_CONTENTS","NEW_CONTENTS","OLD_DOCUMENT","NEW_DOCUMENT"  FROM "CORE"."FM_FIELD" FOR READ ONLY '  (ar_odbc_stmt.c:3654)

    Here are the transformation rules:

    {
    "rules": [
        ...,
        {
            "rule-type": "transformation",
            "rule-name": "exclude-old-document-column",
            "rule-action": "remove-column",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "CORE",
                "table-name": "FM_FIELD",
                "column-name": "OLD_DOCUMENT"
            },
            "rule-id": "7"
        },
        {
            "rule-type": "transformation",
            "rule-name": "exclude-new-document-column",
            "rule-action": "remove-column",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "CORE",
                "table-name": "FM_FIELD",
                "column-name": "NEW_DOCUMENT"
            },
            "rule-id": "8"
        }
    ]
}
  • Jason
    a year ago

    Here is the selection rule, and maybe that is conflicting?

    {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "Select CORE.FM_FIELD",
            "object-locator": {
                "schema-name": "CORE",
                "table-name": "FM_FIELD"
            },
            "rule-action": "include"
        },
  • Ruchir_A SUPPORT ENGINEER
    a year ago

    I tried the below:

    Created table with CLOB and inserted dummy data:

    CREATE TABLE db2coltest (c1 int NOT NULL PRIMARY key, c2 int, c3 clob, c4 int);
INSERT INTO db2coltest VALUES ( 2, 1, REPEAT('AA',10240), 1)

    Removed CLOB column and included the table in the table mapping:

    {
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "531080932",
      "rule-name": "531080932",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%",
        "column-name": "C3"
      },
      "rule-action": "remove-column",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "selection",
      "rule-id": "531030940",
      "rule-name": "531030940",
      "object-locator": {
        "schema-name": "ADMIN",
        "table-name": "DB2COLTEST"
      },
      "rule-action": "include",
      "filters": []
    }
  ]
}

    Task setting had "LobMaxSize": 1 Detailed debug enabled on SOURCE_UNLOAD components.

    From task logs, I see that column C3 is not selected in the unload:

    [SOURCE_UNLOAD   ]D:  SQL statement constructed: SELECT "C1","C2","C4"  FROM "ADMIN"."DB2COLTEST" FOR READ ONLY   (db2luw_endpoint_util.c:264)

    This was tested on DMS 3.5.3 with DB2 LUW as source.

  • Ruchir_A SUPPORT ENGINEER
    a year ago
    • In your example, what is the data type for OLD_DOCUMENT and NEW_DOCUMENT? Would you be able to share the DDL for the table FM_FIELD?
    • What is the target engine type?
    • Have you tried to run the task with detailed debug enabled on SOURCE_UNLOAD? And if so, what SQL do you see being run during the unload? Which column is getting truncated (will be the line after the line which states "Truncation of a column occurred while fetching a value"?
  • Jason
    a year ago

    Hi Ruchir,

    The DDL for the table is:

    CREATE TABLE FM_FIELD (SERIAL BIGINT NOT NULL, PARENT_SERIAL BIGINT NOT NULL, SYSTEM_GENERATED CHARACTER(1) DEFAULT '-' NOT NULL, COLUMN_NAME VARCHAR(30) NOT NULL, DATA_TYPE CHARACTER(1) DEFAULT 'N' NOT NULL, OPERATION CHARACTER(1) DEFAULT 'G' NOT NULL, OLD_CONTENTS VARCHAR(4000), NEW_CONTENTS VARCHAR(4000), OLD_DOCUMENT CLOB(16777216), NEW_DOCUMENT CLOB(16777216), CONSTRAINT FM_FIELD_I_PK PRIMARY KEY (SERIAL), CONSTRAINT FM_FIELD_C_PS FOREIGN KEY (PARENT_SERIAL) REFERENCES "FM_ACTION" ("SERIAL"));

    The target engine is Amazon RedShift (ra3.xlarge Patch 191).

    Here is the output based on SOURCE_UNLOAD logging: [SOURCE_UNLOAD ]D: SQL statement constructed: SELECT "SERIAL","PARENT_SERIAL","SYSTEM_GENERATED","COLUMN_NAME","DATA_TYPE","OPERATION","OLD_CONTENTS","NEW_CONTENTS","OLD_DOCUMENT","NEW_DOCUMENT" FROM "CORE"."FM_FIELD" WHERE ( ("SERIAL" <= 50000000) ) FOR READ ONLY

    Thanks!

0
Accepted Answer

Thanks to Ruchir_A the solution is that order matters when it comes to transactional rules. If you have an open-ended rule (IE: schema: %, table: %, and/or columns: %), that will take precedence and prevent other rules from being used. It is best to move those types of rules as the highest-possible rule-id:

"You cannot apply more than one transformation rule action against the same object (schema, table, column, table-tablespace, or index-tablespace). You can apply several transformation rule actions on any level as long as each transformation action is applied against a different object. However, this restriction is not applicable when using data masking transformation rules where you can have another transformation like ADD-COLUMN or CHANGE-DATA-TYPE for the same column." [1]

[1] Transformation rules and actions - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html

answered a year ago

-2

You're addressing an important limitation in AWS DMS's current column exclusion functionality.

Currently, when using transformation rules with remove-column, DMS still retrieves all columns from the source database before discarding the unwanted ones. This approach creates several inefficiencies:

  1. Large columns like LOBs and CLOBs are still transferred over the network even when they'll be discarded
  2. You may see truncation warnings in logs for columns that won't be used in the target
  3. It consumes unnecessary bandwidth and processing resources
  4. It generates confusing log entries about operations on data you're intentionally excluding

The current workaround is to use transformation rules with remove-column to exclude columns after they've been retrieved:

{
"rule-type": "transformation",
"rule-id": "2",
"rule-name": "2",
"rule-action": "remove-column",
"rule-target": "column",
"object-locator": {
"schema-name": "test",
"table-name": "Actor",
"column-name": "collected"
}
}

Or you can use selection rules with filters to exclude specific columns:

{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "test",
"table-name": "Actor"
},
"rule-action": "include",
"filters": [
{
"filter-type": "exclude",
"column-name": "column_i_want_dms_to_ignore"
}
]
}

However, these approaches still have the limitation you've identified - they don't prevent DMS from initially retrieving the columns from the source.

Your feature request for true column exclusion at the source query level would indeed provide significant benefits:

  • Reduced network bandwidth
  • Cleaner logs without irrelevant truncation warnings
  • Better performance, especially with large LOB columns
  • Simplified troubleshooting

This enhancement would be particularly valuable when working with tables containing large text or binary columns that aren't needed in the target database.
Sources
AWS DMS and generated colums | AWS re:Post
AWS DMS is ignoring LONGTEXT data type column in migration task | AWS re:Post
Optimize data validation using AWS DMS validation-only tasks | AWS Database Blog

answered a year ago

  • Jason
    a year ago

    Yes, and while those solutions are good for the target, it is a huge need when pulling data from the source.

    Workarounds and Their Limitations: While there are potential workarounds like creating database views that exclude unwanted columns, these approaches have significant operational drawbacks:

    Database Views Approach:

    • CDC Complications: Change Data Capture may not work reliably with views since transaction logs reference base tables, not views
    • Operational Overhead: Requires manual coordination between full load (which could use the view) and CDC phases (which need the base table)
    • Database Administration: Additional database objects to maintain and secure
    • Cross-team Dependencies: Requires coordination with DBAs for view creation and permissions

    Multi-Phase Workarounds: Some teams resort to complex approaches like:

    1. Using views for initial full load (better performance)
    2. Switching to base tables for CDC (reliability)
    3. Managing the transition between phases

    This adds significant operational complexity and potential failure points to what should be a straightforward replication setup.

    Why Native DMS Support is Better: A native DMS solution would:

    • Eliminate the need for database schema modifications
    • Work consistently across both full load and CDC phases
    • Reduce operational complexity and potential failure points
    • Provide a cleaner, more maintainable solution

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content