This may be a small post, but hopefully, it will save you some time.
Recently, I had the problem where I had a procedure in Snowflake that was using the '$$' on a literal.
Lets say something like: INSERT INTO TABLE1(COL1) VALUES 'SOMEVALUE$$SOMEVALUE';
My stored procedure was something simple:
CREATE OR REPLACE PROCEDURE FOO() RETURNS STRING LANGUAGE JAVASCRIPT AS $$
snowflake.execute(sqlText:"INSERT INTO TABLE1(COL1) VALUES 'SOMEVALUE$$SOMEVALUE'");
$$
Everything should work, right? Tragically, this was the result:
SQL compilation error: syntax error line 3 at position 71 unexpected 'SOMEVALUE'. parse error line 5 at position 3 near '<EOF>'. parse error line 5 at position 3 near '<EOF>'.
The solution was simple. Just use an escape code like '\$' for example:
CREATE OR REPLACE PROCEDURE FOO() RETURNS STRING LANGUAGE JAVASCRIPT AS $$
snowflake.execute(sqlText:"INSERT INTO TABLE1(COL1) VALUES 'SOMEVALUE\$\$SOMEVALUE'");
$$
And now you won't get any errors.