Snowflake fanboy here who can't really answer your question about why it's so valuable. Not sure I can rationalize the current value. Not sure I think it should be valued this much.

But I can probably answer why Snowflake instead of Redshift (sorry, not too familiar with BigQuery)...

First of all it's cloud-provider agnostic so you can set up Snowflake on any or all of the 3 major cloud providers as well as set up replication between them directly or indirectly through their data exchange. Probably the most powerful feature is the way that Snowflake has the ability to scale (up or down) compute (vertically and horizontal) and storage independently of each other. Furthermore, you have the ability to scale compute down to nothing, and spin up "instantly" when the demand arrives. On top of all of this there is an incredible selection of functionality that i could go on and on about.