The best way to describe Snowflake is that it is a brute force method to run complex queries without creating indexes.
If you have a more traditional database, you will notice you need to set up indexes to be able to get anything from it in finite time. What if you don't know the indexes upfront? What if you want your users to be able to ask arbitrary queries and get answers before bedtime?
That's what Snowflake is for. It automates using ENORMOUS amount of hardware to get your query executed fast, very inefficiently.
It is not for free though. That inefficiency will cause a lot of resources used for queries. It is meant for those few queries when your users try to get some insight into your data and you can't predict indexes beforehand. Sometimes this is exactly what you want, like when you let your data people in to figure stuff out. Or when you have very rare functionality that allows the user to build their own queries -- which you should avoid like hell (and there are tricks to make it index pretty well) but can't always avoid.
For everything else, whenever you can predict your indexes, you always want to use more traditional database that can be very efficient on queries properly supported by indexes.
The issue is a lot of people try to use Snowflake as a database or to support frequently executing queries of the same kind. This is bad and it will cost you.