ETL options in aws ?

It ultimately depends on your use-case. If you know the data you want to move, then it would be more efficient to read just those items, using something like StepFunctions. You can also consider building an index to retrieve those items efficiently should the known value not be keys on your base table.

If you don't know the keys of the items you require, then AWS Glue is a good option, which will Scan the DynamoDB table, and has built in DynamoDB connectors making it easy to read from source and sink to destination.

https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-dynamodb-home.html

For your scenario of moving a subset of data from one DynamoDB table to another, there are several ETL options in AWS:

1. AWS Glue: As you mentioned, AWS Glue is a strong option. You can create an ETL job that reads from your source DynamoDB table, applies transformations or filters to select your subset of data, and then writes to your target DynamoDB table. AWS Glue uses Apache Spark under the hood, making it scalable for your hundreds of thousands of records.

2. AWS Data Pipeline: This managed ETL service can copy data between DynamoDB tables. You can define a pipeline that extracts data from your source table, applies filtering logic, and loads it into your target table. Data Pipeline handles scheduling, execution, retry logic, and dependency tracking.

3. DynamoDB Export to S3 + Glue: You could export your source DynamoDB table to S3, then use AWS Glue to process the data in S3 (apply your filtering logic), and finally load the subset into your target DynamoDB table. This approach doesn't consume read capacity units from your source table.

4. Custom Solution with Lambda/Step Functions: For more control, you could build a custom solution using AWS Lambda functions orchestrated by Step Functions. This approach allows for fine-grained control over the process, including rate limiting to prevent impact on production traffic.

5. Amazon Athena with CTAS and INSERT INTO: If you first export your DynamoDB data to S3, you can use Athena's SQL capabilities to query and transform the data, then load the results back to DynamoDB.

When choosing between these options, consider factors like:

• The frequency of the data transfer (one-time vs. recurring)
• The impact on your source table's performance
• The complexity of your filtering logic
• Cost considerations
• Your team's familiarity with the tools

For your scale of hundreds of thousands of records, most of these approaches would work well, with AWS Glue being particularly well-suited due to its purpose-built ETL capabilities and ease of use.
Sources
AWS Data Pipeline Features - Managed ETL Service - Amazon Web Services
Use CTAS and INSERT INTO for ETL and data analysis - Amazon Athena
Extract, transform and load (ETL) using custom connectors with Apache Spark - Patterns for Ingesting SaaS Data into AWS Data Lakes
Cost-Effective ETL for DynamoDb tables with sizes more than 3 terabytes & export to s3 | AWS re:Post
Recommended way to scan and update large DDB table | AWS re:Post