Yes, Athena is the serverless data querying alternative to BigQuery, not Redshift. When I used Athena for some large scale data querying just a couple years ago it was extremely slow, like two orders of magnitude slower than BigQuery for the same queries. And the UI, documentation, and just general user experience is nowhere near as good as BigQuery's. Nothing from AWS competes with BigQuery, it's genuinely a good product.
Athena lets you run SQL queries on datasets in S3. You can do something similar in BQ with external tables. BQ is much faster. Athena will easily be more expensive on larger datasets, because you pay $5 per terabyte scanned.
AWS doesn't have a service similar to BQ in terms of performance.
I think the 3 main players in the Analytics space are BQ, Snowflake and Databricks.
I don't really know, tbh. I've been at Snowflake shops for a long time now, but from a quick overview it looks like the serverless part is Athena, so like BQ in that sense (and SF also).
But given that Athena uses HiveQL for DDL I'm gonna stand by my original statement.
There is a Redshift serverless offering now, not sure how it compares though. I think it's not 100% there when it comes to not having to think about the underlying infra yourself...
