This is going to be your next cloud agnostic data warehouse.
Performance was superior, client libraries and built-in HTTP interface was a god-send, it supported geospatial queries. I had perpetual issues with getting Snowflake to properly escape strings in CSV, handle JSON in anything approaching a sensible way, there’s claims that it integrates properly with Kafka as a consumer, but it most certainly does not. The UX is horrible to boot.
It doesn't support streaming data though so things like Kafka aren't a good fit yet. They have a connector but it's basically a little app that automates consuming from Kafka, writing files to S3, then loading them into your tables.
Does it have geospatial support now? Because they definitely didn’t have it when I used it.
Ultimately though, I don’t see enough benefits of SF over CH: feature set overlaps but with no real winner features. observed performance was worse, the client libraries are few and far between and not great, and no HTTP interface, and no real streaming support. On top of being hideously expensive, and closed source.
Snowflake is basically EC2 servers reading files from S3 so you get more bandwidth with a larger warehouse size but it's fundamental limit and will have much higher latency compared to running on a local SSD with clickhouse. Lack of streaming is a known problem. They actually do have an HTTP interface, you just don't see it but that's how all the ODBC drivers are implemented (with HTTP calls and JSON data in the background).
If your data fits or you don't mind the operational overhead of running your own data warehouse then it's almost always a cheaper and faster option.