".. Snowflake is something that a company can run on AWS, Amazon, or GCP .."
Maybe you meant to say Azure instead of "Amazon", but Snowflake can be hosted in Azure as well. We're actually researching Snowflake as a potential alternative to our current EDW in SQL Server (and maybe Azure SQL Analytics is an option as well). I'll be involved in a proof of concept implementation this quarter.
When comparing database platforms, especially NoSQL or cloud native options, one thing to consider is that most are optimized for specific data access patterns, and the underlying architecture excels for some use cases - while being very unsuited for others.
For example, Snowflake doesn't create indexes. Instead it splits up the data into 10 MB "micro-partitions" and clusters the data horizontally across distributed nodes. This is all done auto-magically, and that is it's big selling point. Little to no database administration as we know it (physical data modeling, configuration, etc.) is required.
I'm just now getting my feet wet, but apparently it's optimized for extremely fast bulk ELT and OLAP or data science workloads using ANSI SQL or Spark, but not recommended for application (ie: select for customer 123) type queries. So, the trade off is that it's not as flexible as SQL Server. My assumption at this point is that if Snowflake is used as the core EDW, then it will serve as a hub, and various application centric datamarts will need to spoke off from it. The datamart or microservices layer, that is where SQL Server, Azure SQL, or Azure CosmosDB would be better suited.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho