How to reference a variable in snowflake?

Question

SET schema = 'VAI';

select  name, dob
       from vai_database.&{schema}_public.alerts as ac;

I have tried Executing the query in snowflake but i got the following error syntax error line 3 at position 26 unexpected '&'. syntax error line 3 at position 15 unexpected '{'. (line 13)

Answer 1

As NickW mentioned in comment you have to use $ to reference variable.And then use identifier keyword to use it in from clause. Also as you need build your fully qualifier table name first and then use in select.

SET schema = 'VAI';
SET full_name =  'vai_database.'||$schema||'.alerts';

select  name, dob
       from identifier($full_name) as ac;