Which storage strategy is the MOST cost-effective and meets the design requirements?

Explanations:

While storing records as .csv files in S3 allows for simple storage and retrieval, it does not support efficient indexed retrieval for millions of small records. Additionally, managing many small files in S3 can lead to performance issues, especially for high throughput applications. Although a lifecycle policy can delete older data, the overall efficiency and retrieval speed may be inadequate.

Using Amazon DynamoDB is a suitable option as it is designed for high throughput and can handle millions of small records. The DynamoDB Time to Live (TTL) feature automates the deletion of records after 120 days, ensuring data management is streamlined. This approach offers low-latency access and is cost-effective for the scale required, making it the best option.

Amazon RDS MySQL is not ideal for storing millions of small records due to potential performance bottlenecks and higher costs associated with relational databases at scale. Running a nightly cron job to delete old records is inefficient and does not provide the necessary automated lifecycle management. This method may also lead to latency issues during peak ingestion times.

Although batching records before storing them in S3 can reduce the number of requests made to S3, the retrieval process using metadata search is not straightforward for efficient access to specific records. This approach may introduce additional complexity in managing the records and metadata, and it still relies on S3’s eventual consistency model, which can lead to latency issues. While it can leverage lifecycle policies for deletion, it does not match the efficiency of DynamoDB for this use case.