You can go for Aurora first then migrate to redshift when your data gets around 5Tb or so, that’s my estimate in which the query starts to get slow in a rds and dwh will be much faster

Which service fits the most here?

imho you could do a PoC to see which service is more feasible for you. It really depends on how much data you have, what queries and what load you plan to execute.

AWS Redshift is intended for OLAP on top of peta- or exa-bytes scale handling heavy parallel workload. RS can as well aggregate data from other data sources (jdbc, s3,..). However RS is not OLTP, it requires more static server overhead and extra skills for managing the deployment.

So without more numbers and use cases one cannot advice anything. Cloud is great that you can try and see what fits you.

RDS is a managed service for Online Transaction Processing databases (OLTP), i.e. a managed service for the usual MySQL, PostgreSQL, Oracle, MariaDB, Microsoft SQL Server or Aurora (Amazon's own relational database)

Redshift is a managed service for data warehousing, i.e. columnar oriented storage, typical for business analytics type of workloads.

Amazon Athena is well suited for interactive analytics and exploration of data in your data lake or any data source through an extensible connector framework without worrying about ingesting or processing data. Amazon Athena is built on open-source engines and frameworks such as Spark, Presto, and Apache Iceberg, giving customers the flexibility to use Python or SQL or work on open data formats. If customers want to do ***interactive analytics*** using open-source frameworks and data formats, Amazon Athena is a great place to start. Amazon Redshift is a fully managed data warehouse. With its Massively Parallel Processing (MPP) architecture that separates storage and compute and machine learning led automatic optimization capabilities, a data warehouse like Amazon Redshift, whether it's serverless or provisioned, is a great choice for customers that need the best price performance at any scale for ***complex BI and analytics workloads***. Using Amazon Redshift, you can also directly query data in open formats (such as Parquet or ORC) in the Amazon S3 data lake, or query data in operational databases, such as Amazon Aurora and Amazon RDS PostgreSQL and MySQL. Amazon QuickSight is a cloud-scale business intelligence (BI) service that you can use to deliver easy-to-understand insights to the people who you work with, wherever they are. Amazon QuickSight connects to your data in the cloud and combines data from many different sources. Here is a document on the Modern Data architecture with AWS which gives an idea on how the purpose built services can be used together. [https://aws.amazon.com/big-data/datalakes-and-analytics/modern-data-architecture/]