[Wranglers] Identifying worst performing DBT queries

[Ali, Saqib]

DBT is excellent for building data pipelines. However it will not prevent
developers from writing bad queries and scheduling them to run on a regular
interval. This will drive up your Snowflake spend. Badly written query that
executes one time is not an issue, but if it gets executed continuously
then it becomes an issue. It is important to monitor Snowflake to identify
such queries. Here is a Skyline query that I use regularly to identify such
queries.

The following query is looking at three dimensions:
1) Average Credit Spent using the credits_attributed_compute metric in
query_attribution_history
2) Number of times the query was executed;
3) Average Remote spillage. This is an important metric since queries that
cause Remote Spillage tend to be very expensive.


with query_history as (
  select
    query_parameterized_hash
    , any_value (query_text) as query_text
    , avg(credits_attributed_compute) as average_credits
    , count(query_id) as number_of_times_executed
    , avg(bytes_spilled_to_remote_storage) as
average_bytes_spilled_to_remote_storage
from snowflake.account_usage.query_history
inner join SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY using
(query_id)
where start_time >=   DATEADD(hour ,-24, sysdate())
group by all
)
-- using Skyline Query to get the worst performing queries that get
executed multiple times and consume the most credits
select
  query_text
  , average_credits
  , number_of_times_executed
  , average_bytes_spilled_to_remote_storage
from query_history as o
where not exists (
  select 1
  from query_history as i
  where i.average_credits >= o.average_credits and
i.number_of_times_executed >= o.number_of_times_executed and
i.average_bytes_spilled_to_remote_storage >=
o.average_bytes_spilled_to_remote_storage
    and (i.average_credits > o.average_credits or
i.number_of_times_executed > o.number_of_times_executed or
i.average_bytes_spilled_to_remote_storage >=
o.average_bytes_spilled_to_remote_storage
    )
  )
;

This query will output the worst performing queries based on the above
three criterias (dimensions).
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://analyticsengineering.net/pipermail/wranglers/attachments/20250623/138b05a4/attachment.htm>