Snowflake is great for smaller data volumes, but slow at supersized datasets. It has the best UX out of all the cloud-hosted options though, and the best JSON handling. Interesting billing model based on running time that's a good fit for focused data querying sessions using BI tools where you can scale up while in use and turn off after.

Tables are stored as S3 data making sharing, cloning, and snapshot queries easy. Major missing features are lack of streaming (although they have a auto loading option from S3 files) and no stored procedures. Transactions are also tricky and client drivers are sometimes neglected because everything is a wrapper around their HTTP/JSON interface.