BigQuery and Snowflake are both standards-based SQL with proprietary extensions. The extensions are where ports go wrong.
Arrays and structs
BigQuery has first-class ARRAY and STRUCT with UNNEST. Snowflake uses VARIANT plus FLATTEN. They feel similar but the syntax does not port directly.
Scripting
BigQuery scripting uses DECLARE/SET/IF/WHILE inside one script. Snowflake uses anonymous blocks (BEGIN..END) with a different control-flow syntax.
MERGE
Both support MERGE, but Snowflake errors on duplicate join keys by default; BigQuery requires you to enforce uniqueness yourself.
Time travel
Snowflake: AT(OFFSET => -86400) or AT(TIMESTAMP => ...). BigQuery: FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(...). Same idea, totally different syntax.
String functions
REGEXP functions, JSON parsing, and date formatting all differ. Most ports break here first.