The following snippet works when i check it in Snowflake (dbeaver).
set uuid=(select UUID_STRING())
select colA,col,$uuid from MYTABLE
However, when I try to do the same thing in a dbt script, I get an error that:
13:35:41 Database Error in model third (models/file.sql)
13:35:41 001003 (42000): SQL compilation error:
13:35:41 syntax error line 4 at position 4 unexpected 'set'.
13:35:41 syntax error line 4 at position 34 unexpected ')'.
13:35:41 compiled SQL at target/run/project/models/file.sql
Looks like I cannot use the "SET" command here. How else can I set a uuid variable?
You can use Jinja and Macros in dbt to set variables and make use of them in your SQL statement: https://docs.getdbt.com/docs/building-a-dbt-project/jinja-macros
In your particular case: Why aren't you just calling the function in your select itself? This would probably be the most easiest solution. (Of course if you are re-using the same output of UUID_STRING() somewhere else, then you need to go for variable)
select colA,col,UUID_STRING() from MYTABLE
Also please note: This example would generate different strings for every row. I assume you need the same UUID for every row (at least this is what you would have in your example). You may go for this solution, which is SQL only and not including any Jinja/Macros or variables.
with uuid_generation AS
(select uuid_string() as uuid_string)
select colA,col,uuid_string
from MYTABLE cross join uuid_generation;