As someone who spent a few hours this morning bashing my head against this and this is the only relevant thread I could see. A solution I found to inserting a '$' in a query string in a notebook was to concat(chr(36), 'xxx')

I needed a $ character in the context of Java format strings in the SQL printf function.

With some trial and error, I found workarounds:

A trailing backslash escapes the dollar character successfully, like so
SELECT printf('%1$\s, %1$\s', 'hi') AS test
Column names can be escaped normally when wrapped in a statement string and executed with EXECUTE IMMEDIATE:
EXECUTE IMMEDIATE 'SELECT printf(\'%1$\s, %1$\s\', \'hi\') AS `\$test`'