I'm using DMS to create an on-going task that will deploy data from my production database to the development one. However, the PII must be masked so I'm creating a set of rules to do that.

It's easier for the developers if the length of the data in each row is still the same as in production. So I created a rule to preserve the first letter and add some x after. This is my transformation schema:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "mydatabase",
        "table-name": "my_table"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-action": "add-column",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "mydatabase",
        "table-name": "my_table"
      },
      "value": "TMPcontact_name",
      "expression": "substr($contact_name, 0, 2) || replace(printf('%.' || length($contact_name) || 'c', '/'), '/', 'x')",
      "data-type": {
        "type": "string",
        "length": "50"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-action": "remove-column",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "mydatabase",
        "table-name": "my_table",
        "column-name": "contact_name"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "4",
      "rule-name": "4",
      "rule-action": "rename",
      "rule-target": "column",
      "object-locator": {
          "schema-name": "mydatabase",
          "table-name": "my_table",
          "column-name" : "TMPcontact_name"
      },
      "value": "contact_name"
    }
  ]
}

The problem is that DMS is ignoring the rule #4. After the process is finished, the name of the column in the target is TMPcontact_name. The logs in CW does not contain any error or notice related to that. I could not find any related documentation.

I was facing exactly the same problem just this week. After a lot of reverse engineering and reading, I came to the conclusion that DMS version 3.4.6 is not able to do what we need.

It (probably) does not work

• As you mentioned, a use case like that is not mentioned in AWS docs.
• Limitations section in docs state You can't apply more than one transformation rule action against the same object. In your solution, you are applying two transformation rules on column TMPcontact_name.

Proposed Solution

Instead in docs as well as AWS blog articles, but also others (and even others) that mostly copy from the first article, the propagated solution is to create a new column with transformed data and remove the old one.

After lot of reconsideration, I think this approach might be even preferrable because it is less confusing to users since your replicated table is not even pretending that the new column has the same contents as the source column because the name stays same. Using contact_name_masked as name should make it clear to everyone, even those who do not understand PII implications, that this is the masked contact name. Same goes for contact_name_hashed when using hash_sha256().