Snowflake: monitoring and debugging stored procedures using query tagging

When you run Snowflake stored procedures, debugging is not the easiest thing. This is my example stored procedure, dynamically dropping tables:

create or replace procedure groupby.public.droptables(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
DECLARE  
v_sql_query VARCHAR;
c_table CURSOR FOR SELECT TABLE_NAME FROM groupby.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC';

BEGIN
v_sql_query := '';
    
FOR record IN c_table DO
    v_sql_query := 'DROP TABLE GROUPBY.PUBLIC.' || record.TABLE_NAME;
    EXECUTE IMMEDIATE v_sql_query;
END FOR;    

RETURN 'completed';
END;
$$;

call groupby.public.droptables('');

Looking at the query history, the call to the stored procedure is only the call itself:

The statements that are executed from the stored procedure are also visible in the query history. In my example — an isolated trial account with only one user — it is pretty easy to find:

But what happens when you run inside a production environment, with thousands of queries executed, how can we then trace & debug this stored procedure from the query history. One…