We all agree that Redshift is not scalable at some point and people switch to other technologies when they need to but that's not the point. Creating a Redshift cluster is dead-easy and getting the data in it is also not complex compared to other solutions if you're already an AWS user.
The case for Snowflake is different, they also use AWS but they manage your services. Although they did a smart move and store the data on their customers' S3 buckets in order to make them feel like they "own" the data but it's possible only because AWS lets them to that way.
I believe that AWS doesn't try to make Redshift cost-efficient for large volumes of data because they already have the advantage of vendor-lock and making money from their large enterprise customers processing billions of data points in Redshift. That's why there are many Redshift monitoring startups out there to save the cost for you.
On the other hand, AWS is smart enough to build a Snowflake-like solution when their Redshift users start to switch BigQuery and Snowflake and the companies such as Snowflake needs to be prepared when the day comes. Cloud is killing everyone including us.
It's true that AWS has Redshift Spectrum (and Athena) to help with more scalable querying across S3, however I don't think that makes a big risk for another company that provide a focused offering on top. Snowflake is very well capitalized with close to $500M in investment and plenty of customers so I wouldn't worry about them going out of business.
I especially like them because they have the elastic computing style of BigQuery but charge for computing time rather than data scanned, which is a much more effective billing model than anything else out there.
Anyway, at this point I'm just repeating myself so I suggest you actually try them if you care for a better model. It works for us at 600B rows of data that was too expensive with BigQuery and too slow and complicated with Redshift/Spectrum.
Please note that this is our core business. Of course, we maintain these services but we also try hard to push more work to cloud providers. For example, both AWS and GC offers managed Mysql servers, auto scale groups for nodes, object stores such as S3 and Cloud Storage so we actually maintain the software and help the customers to upgrade the software when they need.
I agree that BigQuery will save time if the users are not familiar with distributed systems and big-data but again, "ability to go from 0 cores to thousands" doesn't make sense to me because in practice I have never experienced such case. Since we need to start the instances it may take up to 2 - 3 minutes but this is often acceptable for data analysts.
Could you please elaborate the part "re-materializing and optimizing your dataset."? We do a number of optimizations for compacting ORC files, bucketing etc. but I would love to hear how BigQuery does post-processing.
As a note, I usually tend to simplify things but most of the BigQuery customers that I see usually do overengineering because of the cost optimization. For example in the article the author uses Redshift for the dashboard data the solution they use for moving data from BigQuery to Redshift also needs to be maintained and it's not that easy. If I'm going to adopt my whole system to the way how BigQuery works and push hard to save costs, then I expect it to be pretty cheap but 40K for reserved slots doesn't sound like cheap to me. We maintain similar size clusters for 20% of this price for the same data volume and it's much more flexible.