Snowflake: alter session set query_tag='<stuff>'

[trillich]

1. My goal: for each app, as we connect to snowflake we want to "ALTER SESSION SET QUERY_TAG='xxx'"

2. Issue: reporting on query load in snowflake is much more flexible if we can set up query_tag before each session gets going

3. Steps I've taken to troubleshoot:

We set up all queries Retool with a "debounce" of 100ms to 500ms... except for the inital one:

    -- initial SQL:
    alter session
    set query_tag = 'Something_Distinct_Here'

We can see that statement happening in snowflake's query history... but there aren't a lot of queries running on that same session:

   select * from snowflake.account_usage.query_history
   where session_id = '2202076992122922';

All the meaningful queries are using a different session?

In snowflake we check it like so:

    select session_id,count(*) from snowflake.account_usage.query_history
    where user_name='DREAD_PIRATE_ROBERTS'
      and start_time>'2025-04-24 14:20:00'
    group by 1;

And we can see that there have been 43 sessions in the past hour

4. Additional info: Retool Cloud

The snowflake connection is dynamic, and there are several dozen queries in the Retool app that leverage this connection. All other queries either have debounce > 100ms or are "manual".

Snowflake has object-level query tags and user-level query tags, but our structure would work much better with session-level query tags.

So are there any suggestions on how to set query_tag when an app starts up, for all the snowflake-sessions that are gonna be instantiated?

[lindakwoo]

@trillich,
We maintain a connection pool with individual connections, and successive queries might not be in the same session. So unfortunately your method will not work.

As a workaround, you COULD put a variable identifying the session in each individual query. For example, I did something like this:

I first created a global variable:

Then added it to my query:

and then you can search your snowflake queries like this: WHERE QUERY_TEXT ILIKE '%some tag name here%'

In order to do this, you would have to disable prepared statements like this:

However, I must caution you that this is not a recommended approach because:

• Security risk: Disabling prepared statements opens you up to potential SQL injection vulnerabilities, even if inputs are "trusted."
• Performance impact: Prepared statements allow Snowflake (and databases generally) to optimize queries ahead of time. Without them, every query needs to be parsed, planned, and compiled from scratch, which can slow things down significantly at scale.
• Debugging difficulty: Without prepared statements, it becomes harder to consistently reproduce query behavior, making troubleshooting more complex.

[trillich]

Pooh.

Thanks for the suggestion tho