Instrument Snowflake Queries using a Query Tracker

tracking.sql

alter session set QUERY_TAG = 'MY_TAG';

select
//QTR:Query_123//
start_station_id, end_station_id,
    count(1) num_trips,
    avg(datediff("minute", starttime, stoptime))::integer avg_duration_mins
  from trips
  group by 1, 2
  order by 3 desc;

alter session set QUERY_TAG = '';

with filtered_query_history as (
  select *
    from snowflake.account_usage.query_history
   where 1=1
         and query_type             in ('SELECT') -- Add types if needed
         and execution_status       = 'SUCCESS'
         -- and cluster_number         > 0 -- filter out results from cache
         -- and warehouse_size         IS NOT NULL -- filter out results from cache
         and start_time             > '2021-08-31'::timestamp
         and query_tag              = 'MY_TAG'
)
select
    query_id
  , query_tag                                      AS query_tag
  , DATE_TRUNC('minute', start_time)               AS interval_time
  , extract(minute from start_time)                AS minute
  , SUBSTR(query_text, REGEXP_INSTR(query_text, 'QTR:', 1, 1) + 4, REGEXP_INSTR(query_text,'//', 1, 2) - (REGEXP_INSTR(query_text, 'QTR:', 1, 1) + 4) )  AS query_tracker
  , warehouse_name                                 AS warehouse_name
  , warehouse_size                                 AS warehouse_size
  , cluster_number                                 AS cluster_number
  , ROUND(execution_time / 1000, 2)                AS execution_time
  , ROUND(queued_overload_time / 1000, 2)          AS queued_time
from filtered_query_history
;