Why do you need “data replication with snapshots”? What requirement is that fulfilling for you? Why not just write the data to s3 in time stamped partitioned folders? Redshift can read it from there later using spectrum or copy, or you can use any other number of tools. More on reddit.com

Might be a silly question, but why are you bothering with joins if you're so overprovisioned? Have you experimented with denormalising to increase performance? More on reddit.com

You’ve generally got a couple options - AWS Athena. This is basically managed Presto. You put your JSON data in S3 and Athena let’s you query it directly. Pay-per-query. AWS Redshift. This is basically the data warehouse version of Postgres. You still put your JSON data in S3, but then you copy it into Redshift. Pay-per-server. AWS Redshift Spectrum. This is a hybrid of Redshift and Athena. Both pay-per-server (you need at least 1 Redshift node) and pay-per-query. AWS RDS. Using an OLTP database to perform OLAP workloads. If you’re more comfortable with MySQL or regular Postgres this can get you started more quickly but it isn’t a good fit on its own. AWS EMR. This is the AWS version of Apache Spark. Put your data in S3 (do you see a pattern yet?) and then write spark jobs to calculate your aggregates. If you need some sort of complex processing while calculating the aggregates that SQL can’t do alone, this is probably a good fit. That being said, SQL can do a lot of heavy lifting these days. Once you’ve calculated your aggregates you could dump them into an RDS instance for querying. If this is a personal project, where cost is the primary driver, I’d go with Athena. It should be dead simple to set up. Create a script to hit the API, compress the data, and save the results to S3. Throw your dimensional tables into S3/Athena and then query away. Athena now supports Create Table As Select so you would only have to run your aggregate queries once and just save the results as a new table. Because it’s pay-per-query, you only pay for S3 storage cost when you’re not using it. Airflow - I am a strong believer that Airflow is major overkill for anything but large projects with 5+ data sources. If you need scheduling, a simple server with cron works great, or lambdas triggered with a cloudwatch schedule. Kinesis - You should consider Kinesis Data Firehose as a way to have AWS do all your ETL work for you. With this set up, your script would hit the API and push the JSON data to a Kinesis Firehose. The stream then buffers up batches of data, writes them to S3, and optionally copies the data into Redshift for you. Plain jane Kinesis isn’t appropriate here. I’d recommend taking a look at Athena. The benefits are low cost, easy to get started, and it scales on demand. The gotchas are - you need to compress and partition your data, performance won’t be amazing if your JSON objects are really fat (5kb+), and converting to the alternate Athena formats like ORC or Parquet are a pain. More on reddit.com

+1 for Athena. Athena also allows you to progress towards a data lake architecture with ability to store and process in a redshift cluster or a postgresql database if you need to in the future. More on reddit.com