Creating partitions in DMS

Yes, it is possible to use a transformed field to create partitions directly in AWS Database Migration Service (DMS) when using S3 as a target. DMS supports S3 folder partitions based on transaction commit dates, which can be leveraged to create a time-based hierarchy folder structure in your S3 bucket.

When you set up your DMS task with Oracle as the source and S3 as the target, you can use transformation rules to extract the year and month from your data. Then, you can use these transformed fields to create partitions in your S3 target.

To achieve this, you would need to configure your S3 target endpoint settings to enable date-based folder partitioning. This feature allows you to write data from a single source table to a time-hierarchy folder structure in your S3 bucket. By doing so, you can:

1. Better manage your S3 objects
2. Limit the size of each S3 folder
3. Optimize data lake queries or subsequent operations

When setting up your task, you can specify the partition folder structure using the extracted year and month fields. This will result in your migrated data being organized into folders based on these time elements, effectively creating partitions in your S3 target.

It's important to note that while DMS can create these folder-based partitions in S3, they are not the same as database table partitions. However, this structure can be very useful for data organization and query optimization when working with tools like Amazon Athena or other data lake query engines.

Remember to carefully plan your partition strategy based on your specific use case and data access patterns to ensure optimal performance and manageability of your migrated data in S3.
Sources
Using Amazon S3 as a target for AWS Database Migration Service - AWS Database Migration Service
Setting up transformation rules in DMS Schema Conversion - AWS Database Migration Service