Disclaimer:
I'm the co-founder of a start-up that provides performance analytics for Amazon Redshift. So I'm biased towards using Redshift because otherwise I can't sell you our service. With that cleared, some considerations.

One of our customers I thought made a great observation. "I see a lot of people using Redshift. But I don't see anybody using it happily". Because of all the issues that people point out in this thread. lots of operations. little visibility ("black box"). All very true. It's the problem our product solves, and let me talk about how we see companies successfully using Redshift at scale. (and that customer is now a happy Redshift user, btw).

Here's the architecture that we're seeing companies on AWS moving to:

- all your data in S3 (I hate the term, but call it a "data lake")

- a subset of your data in Redshift, for ongoing analysis

- daily batch jobs to move data in and out of Redshift from / to S3

- Athena to query data that's in S3 and not in Redshift

The subset of the data sitting in Redshift is determined by your needs / use cases. For example, let's say you have 3 years of data, but your users only query data that's less than 6 months old. Then moving data older than 6 months to S3 makes a lot of sense. Much cheaper. For the edge cases where a users does want to query data older than 6 months, you use Athena to query data sitting in S3.

Why not use Athena for everything? Two reasons. For one, Athena is less mature. And then you still need a place to run your transformations, and Redshift is the better choice for that.

Three uses cases for Redshift:

1) classic BI / reporting ("the past")

2) log analysis ("here and now")

3) predictive apps ("the future")

Your use case will determine how you have to think about your data architecture. For some it's ok if a daily report takes a few minutes to run. I'll go get a coffee. It also means my batch jobs are running on daily or hourly cycles. But if I'm running a scoring model, e.g. for fraud prevention, I want that score to be as fresh / real-time as possible. And make sure that the transformations leading up to that score have executed in their proper order.

For Redshift to work at scale, there are really only three key points you need to check the box on:

- setting up your WLM queues so that your important workloads are isolated from each other

- allocating enough concurrency and memory to each queue to enable high throughput

- monitoring your stack for real-time changes that can affect those WLM settings

That's it. And then Redshift has a rich ecosystem and rich feature set to enable all uses cases (etl vendors, dashboard tools, etc.). Those users who have addressed the performance challenges that do come at scale for Redshift - they're happy users and never looked back.

On the pay-per-TB pricing for BigQuery and Snowflake. I think that's more of a marketing spin. Most companies we work with are compute-bound. So per-TB pricing helps them very little. They want to crunch their data as fast as possible. More CPUs give them more I/O. If you feel you're storage-bound - take a hard look at the data that really needs to be available in Redshift for analysis, and move everything else to S3.

For inspiration, watch the AWS Reinvent videos on S3 / Redshift / Athena from Netflix and NASDAQ. Like this one:

https://www.youtube.com/watch?v=o52vMQ4Ey9I&t=256s

If you're entire data is already within AWS, I think Redshift is the way to go. But then again I'm biased :)