If you're familiar with common SQL dialects like Postgres or MySQL, you'll find that while there are a few specific differences, the basics will be very familiar, as the Snowflake SQL dialect is ANSI SQL compliant. (More on SQL standards and interoperability.)
Here's a roundup of some of the key differences people run into when switching from Postgres to Snowflake that we found helpful. In particular:
Filtering in Snowflake must be done with CASE statements.
No support for DISTINCT ON, so people use window functions, generally.
To help navigate this all of this, Snowflake also maintains a comprehensive SQL reference, and many Snowflake-specific functions are available. (Crypto analysts often find their JSON parsing functions can be particularly helpful.)