Example Usage

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:

1use database [dbt-snowflake-monitoring model database]
2use 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).

1select
2    date_trunc(month, date)::date as month,
3    service,
4    sum(spend)
5from daily_spend
6group 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'.

1select
2    date_trunc(month, date)::date as month,
3    warehouse_name,
4    sum(spend_net_cloud_services) as spend
5from daily_spend
6where service in ('Compute', 'Cloud Services')
7group 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.

1select
2    date_trunc(month, date)::date as month,
3    service,
4    storage_type,
5    database_name,
6    sum(spend)
7from daily_spend
8where service in ('Storage')
9group 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

1with
2max_date as (
3    select max(date(end_time)) as date
4    from query_history_enriched
5)
6select
7    md5(query_history_enriched.query_text_no_comments) as query_signature,
8    any_value(query_history_enriched.query_text) as query_text,
9    sum(query_history_enriched.query_cost) as total_cost_last_30d,
10    total_cost_last_30d*12 as estimated_annual_cost,
11    max_by(warehouse_name, start_time) as latest_warehouse_name,
12    max_by(warehouse_size, start_time) as latest_warehouse_size,
13    max_by(query_id, start_time) as latest_query_id,
14    avg(execution_time_s) as avg_execution_time_s,
15    count(*) as num_executions

dbt Cost Attribution

Top 10 costliest dbt models in the last 30 days

1with
2max_date as (
3    select max(date(end_time)) as date
4    from dbt_queries
5)
6select
7    dbt_queries.dbt_node_id,
8    sum(dbt_queries.query_cost) as total_cost_last_30d,
9    total_cost_last_30d*12 as estimated_annual_cost
10from dbt_queries
11cross join max_date
12where
13    dbt_queries.start_time >= dateadd('day', -30, max_date.date)
14    and dbt_queries.start_time < max_date.date -- don't include partial day of data
15group by 1

Daily dbt model running costs

1select
2    date(start_time) as date,
3    sum(query_cost) as cost
4from dbt_queries
5where dbt_node_id = '<dbt model node id>'
6group by 1
7order by 1 desc