AWS DMS treats string field as NULL in transformation when migrating DocumentDB → S3 (table format)

Question

I’m migrating data from Amazon DocumentDB to Amazon S3 using AWS DMS. The S3 target uses table format (CSV). I add two derived columns via Table Mapping transformation rules: one from an integer field and one from a string field.

The integer-based derived column works.

The string-based derived column is always NULL in the derived column (even though the original column lands correctly in S3).

**Source document (DocumentDB):**

```json

{
  "_id": { "$oid": "6771f9ce5457653e1d99b931" },
  "userId": 123,
  "phase": "testPhase"
}

```

**Table mapping in transformation:**

```json
{
  "rules": [
    {
      "rule-id": "100",
      "rule-name": "IncludeAllCollections",
      "rule-type": "selection",
      "rule-action": "include",
      "object-locator": { "schema-name": "%", "table-name": "%" }
    },
    {
      "rule-id": "120",
      "rule-name": "AddPhase",
      "rule-type": "transformation",
      "rule-target": "column",
      "object-locator": { "schema-name": "%", "table-name": "%" },
      "rule-action": "add-column",
      "value": "newPhaseValue",
      "expression": "$phase",
      "data-type": { "type": "string", "length": 256 }
    },
    {
      "rule-id": "130",
      "rule-name": "AddUserId",
      "rule-type": "transformation",
      "rule-target": "column",
      "object-locator": { "schema-name": "%", "table-name": "%" },
      "rule-action": "add-column",
      "value": "newUserId",
      "expression": "$userId",
      "data-type": { "type": "string", "length": "1024" }
    }
  ]
}

```

**Observed S3 CSV output:**

```csv
userId,phase,newUserId,newPhaseValue
123,testPhase,123,
```

- newUserId (derived from integer userId) is populated as expected.
- newPhaseValue (derived from string phase) is empty/NULL, even though the original phase column in the CSV is testPhase.

DMS Engine Version: I tested mainly in 3.5.4, however, it didn't work in 3.6.1, either.  
**Question:**

Is there a known limitation or special syntax for referencing string fields from DocumentDB in DMS transformation expressions when the S3 target is table format? Or is there any config issue in our table transformation json?

Thanks

re:Post Agent · Answer

This issue appears to be related to how AWS DMS handles string data types during transformations when migrating from DocumentDB to S3 in table format (CSV).

When AWS DMS processes data type conversions, it can sometimes replace values with NULL if they can't be properly converted to the target data type. In your case, while the integer field is being correctly transformed, the string field is being treated as NULL in the derived column.

There are a few potential solutions to try:

1. You could try prefixing your source column name with "json_" in your transformation rule to coerce the data type. For example, instead of using "$phase" in your expression, you might try "$json_phase". This tells DMS to treat the field as a JSON object rather than a simple string.

2. Check the CsvNullValue setting in your S3 target endpoint settings. This parameter specifies how AWS DMS treats null values when writing to the target. By default, it's set to "NULL", but you can customize it to differentiate between empty strings and null values.

3. If your string contains special characters or JSON formatting, you might need to use a different approach to extract the string value properly in your transformation expression.

4. Consider using a different data type in your transformation rule. While you've specified "string" with length 256, you might try adjusting these parameters or using a different data type.

The fact that the original "phase" column is correctly populated in the output while the derived "newPhaseValue" column is NULL suggests that there's an issue specifically with how the transformation expression is handling the string data from DocumentDB.  
**Sources**  
[S3Settings - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/APIReference/API_S3Settings.html)  
[Using Amazon DocumentDB as a target for AWS Database Migration Service - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.DocumentDB.html)  
[CHANGE_DATA_TYPE - AWS Glue DataBrew](https://docs.aws.amazon.com/databrew/latest/dg/recipe-actions.CHANGE_DATA_TYPE.html)

AWS DMS treats string field as NULL in transformation when migrating DocumentDB → S3 (table format)

Add your answer

Relevant content