Using date-based folder partitioning in DMS

It appears you're using date-based folder partitioning for your S3 endpoint in AWS Database Migration Service (DMS), but not getting the desired key-value pair format in your folder structure. To achieve the format you're looking for (XYZ/year=2024/month=12/day=04/), you'll need to adjust your DMS endpoint settings.

By default, when you enable date-based partitioning, DMS uses a simple date format without the key names. However, you can customize this behavior to include the key names as you've specified.

Unfortunately, there isn't a built-in setting in DMS that directly produces the exact format you're looking for with "year=", "month=", and "day=" prefixes. The standard DatePartitionSequence options (like YYYYMMDD, YYYYMMDDHH, etc.) don't include these key names.

To get closer to your desired format, you could use the following settings:

1. Set DatePartitionEnabled to true
2. Set DatePartitionSequence to YYYYMMDD (this is usually the default)
3. Set DatePartitionDelimiter to SLASH

This will give you a structure like XYZ/2024/12/04/, which is close to what you want but without the key names.

To achieve the exact format you're looking for (XYZ/year=2024/month=12/day=04/), you might need to consider a post-processing step. After the data is migrated to S3, you could use AWS Glue or a custom script to reorganize the folder structure and add the "year=", "month=", and "day=" prefixes.

Alternatively, if your primary goal is to have these partitions recognized correctly by services like Amazon Athena or AWS Glue, you might be able to achieve this by using the GlueCatalogGeneration option in your DMS endpoint settings. This allows AWS Glue to catalog your S3 bucket, and you can then define the partition keys in the Glue Data Catalog, which Athena can use for querying, even if the physical S3 path doesn't include the "year=", "month=", and "day=" prefixes.

Remember to test these settings in a non-production environment first to ensure they meet your requirements.
Sources
Using Amazon S3 as a target for AWS Database Migration Service - AWS Database Migration Service
S3Settings - AWS Database Migration Service