Introduction
The following sample queries are useful starting points for performing analysis on top of the models provided by this package. Before running the queries, make sure that the default database and schema have been set:
use database [dbt-snowflake-monitoring model database]
use schema [dbt-snowflake-monitoring model schema]
Reproducing the Monthly Snowflake Invoice
The daily_spend model reproduces the monthly Snowflake invoice precisely, broken down by the same service categories. The daily_spend model contains additional columns for warehouse_name, database_name and storage_type to further break down spend (see more examples below).
select
date_trunc(month, date)::date as month,
service,
sum(spend)
from daily_spend
group by 1, 2
Warehouse Costs
Monthly Spend per Warehouse
This query uses the daily_spend model to explore spend by warehouse name. Some queries don't require a running warehouse, which is why you'll see a row for warehouse_name = 'Cloud Services Only'.
select
date_trunc(month, date)::date as month,
warehouse_name,
sum(spend_net_cloud_services) as spend
from daily_spend
where service in ('Compute', 'Cloud Services')
group by 1, 2
Storage Costs
Monthly Spend per Database
This query uses the daily_spend model to explore table spend by database. Note that this also returns storage costs for data held in stages (user and table stages or internal named stages), see the storage_type column.
select
date_trunc(month, date)::date as month,
service,
storage_type,
database_name,
sum(spend)
from daily_spend
where service in ('Storage')
group by 1, 2, 3, 4
Unused Tables
This query uses the query_base_table_access model (available as of 4.0.0) to identify tables which have not been queried in the last 30 days. The total storage costs of each table are also shown, which uses the account's current storage rate (usually between $20-$40 per TB per month). See this post for more details on the difference between base versus direct tables accessed.
Query Cost Attribution
Snowflake bills for the number of seconds that a warehouse is running, not by query. Query cost attribution helps understand how queries are contributing to warehouse active time. Removing a query will not reduce the bill by the exact amount attributed to the query if other queries are running at the same time and causing the warehouse to stay active.
Cloud services credits are only billed if they exceed 10% of the compute credit consumption on a given day. The cost per query model accounts for this, but current day values will change up until the end of the day.
Top 10 costliest queries in the last 30 days
with
max_date as (
select max(date(end_time)) as date
from query_history_enriched
)
select
md5(query_history_enriched.query_text_no_comments) as query_signature,
any_value(query_history_enriched.query_text) as query_text,
sum(query_history_enriched.query_cost) as total_cost_last_30d,
total_cost_last_30d*12 as estimated_annual_cost,
max_by(warehouse_name, start_time) as latest_warehouse_name,
max_by(warehouse_size, start_time) as latest_warehouse_size,
max_by(query_id, start_time) as latest_query_id,
avg(execution_time_s) as avg_execution_time_s,
count(*) as num_executions
dbt Cost Attribution
Top 10 costliest dbt models in the last 30 days
with
max_date as (
select max(date(end_time)) as date
from dbt_queries
)
select
dbt_queries.dbt_node_id,
sum(dbt_queries.query_cost) as total_cost_last_30d,
total_cost_last_30d*12 as estimated_annual_cost
from dbt_queries
cross join max_date
where
dbt_queries.start_time >= dateadd('day', -30, max_date.date)
and dbt_queries.start_time < max_date.date -- don't include partial day of data
group by 1
Daily dbt model running costs
select
date(start_time) as date,
sum(query_cost) as cost
from dbt_queries
where dbt_node_id = '<dbt model node id>'
group by 1
order by 1 desc