Schema unnesting

When integrating with analytics services through Zero-ETL, you can choose how nested structures are represented in the target tables. AWS Glue Zero-ETL provides schema unnesting options to flatten complex data structures into more analytics-friendly formats.

Unnesting options

When creating a Zero-ETL integration with a source, you can choose from the following unnesting options. These options correspond to specific enumeration values that you'll use when calling the CreateIntegrationTableProperty API. For all unnest options, we would traverse to most inner layer and map DDB type to target spark/iceberg primitive type with best efforts. The type mapping between source DDB and target table is as below:

aws glue create-integration-table-property 
  --resource-arn "arn:aws:glue:us-east-1:123456789012:database/my_db" 
  --table-name "my-table" 
  --cli-input-json '{
      "TargetTableConfig": {
          "UnnestSpec": "FULL",
          "TargetTableName": "my-target-table",
      }
  }'

Unnesting examples

Consider a DynamoDB item with the following structure:

// Input DynamoDB Record
{
  "Item": {
    "col_1": {
      "S": "value_1"
    },
    "col_2": {
      "M": {
        "col_3": {
          "M": {
            "id": {
              "S": "value_3"
            }
          }
        },
        "col_4": {
          "BOOL": true
        }
      }
    }
  }
}

NO_UNNEST example

With NO_UNNEST, the entire row is stored within one column plus the primary key. DynamoDB type information is preserved. This maintains compatibility with Redshift querying patterns.

Resulting Iceberg table (assuming col_1 is primary key):

{
  "col_2": {
    "M": {
      "col_3": {
        "M": {
          "id": {
            "S": "value_3"
          }
        }
      },
      "col_4": {
        "BOOL": true
      }
    }
  }
}

Queries would need to use struct and array access patterns:

SELECT 
  value.col_1,
  value.col_2.M.col_3.M.id.S,
  value.col_2.M.col_4.BOOL
FROM product_table;

TOP_LEVEL example

With TOP_LEVEL, only the top-level fields are unnested while keeping nested fields intact as structs. DynamoDB type information is removed and typing is maintained. Converts to string type when schema conflicts occur.

Resulting Glue table after replication:

{
  "col_3": {
    "id": "value_3"
  },
  "col_4": true
}

Queries would be simplified for the first level:

SELECT 
  col_1, 
  col_2.col_3.id,
  col_2.col_4
FROM product_table;

FULL example

With FULL unnesting, both top-level fields and nested struct/map fields are flattened. Dot notation is used for nested fields (e.g., "col_2.col_3.id"). Array elements remain unnested. Each leaf node becomes a top-level column.

Resulting Glue table after replication:

Queries would be fully flattened:

SELECT 
  col_1, 
  "col_2.col_3.id",
  "col_2.col_4"
FROM product_table;