When migrating a large table via DMS to an S3 bucket and using filters, is there a way to write out the content to separate sub-filters?

Yes, you can partition your data when migrating a large table to S3 using AWS DMS. AWS DMS supports date-based folder partitioning for S3 target endpoints, which allows you to write data from a single source table to a time-hierarchy folder structure.

To implement this, you need to enable date-based partitioning when configuring your S3 target endpoint in DMS. This feature organizes your data based on transaction commit dates for CDC (Change Data Capture) and can use partition columns for full load.

When properly configured, AWS DMS will create a folder structure that organizes your data by date, which would look something like:

schema_name/table_name1/20140912/LOAD00000001.csv
schema_name/table_name1/20140914/LOAD00000002.csv

For your specific case with data distributed across 20 years, you would need to:

1. Set DatePartitionEnabled to true in your S3 target endpoint settings
2. Set DatePartitionSequence to specify the date format (e.g., YYYY for year-level partitioning)
3. Set DatePartitionDelimiter to SLASH

This approach offers several benefits:

• Better management of your S3 objects
• Limited size for each S3 folder
• Optimized data lake queries and subsequent operations

Note that the default format will be something like "schema_name/table_name/2024/12/04/" rather than including prefixes like "year=2024". If you specifically need the "FILTER_YYYY" naming convention, you might need to consider a post-processing step after the migration.
Sources
Step-by-step an Amazon RDS PostgreSQL database to an Amazon S3 data lake migration walkthrough - Database Migration Guide
Using Amazon S3 as a target for AWS Database Migration Service - AWS Database Migration Service
Using date-based folder partitioning in DMS | AWS re:Post