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…