Snowflake Dashboard: Credit Usage By User (2024)

Disclaimer: Credit consumption by user is purely an artistic (rather than scientific) calculation when it comes to slicing up the warehouse cost, which is the only knowable.

Calculating Credit Consumption By User

Snowflake costs are incurred by warehouse (compute) usage. Compute is inherently shared amongst anyone who has access to the resource and this makes it rather difficult to quantify what individual users are consuming when it comes to their own activity.

A huge shout out is required to this article for providing the methodology listed below. I started out truly standing on the shoulders of giants.

The basic scenario we have to attempt to charge back credit consumption to the user of a shared resource is as follows.

1. Determine total number of credits spent by an active warehouse. This is easiest to use by the hour as we are provided the actual cost from Snowflake in snowflake.account_usage.warehouse_metering_history.
2. Determine what queries were submitted to the warehouse during a period of time and for how many seconds each spent executing within that hour slice.
3. Assign cost of the executing time to each query. If it is a sole query causing the warehouse usage, assign 100% of the active cost to that query. If it is multiple queries, share the cost equally across the queries running at the time. If a query spans multiple time slices, only charge back the portion of time spent running in that window. Any time the warehouse spent idle we will evenly split the cost to all queries in scope.
4. Calculate the cost for each query ran during the slice and attribute this to the user of the query.
5. Create a Snowsight dashboard to give users some insights into their usage patterns and where they have room for improvements in their querying and usage of the Snowflake platform!

• All the queries are scoped to a :daterange filter set in the UI (top left).
• All the queries are scoped to those executed under the context of the current_user().

Daily Credit and Average Daily Credit Usage

--daily credit usage - current day
with query_warehouse_usage as (
 select
 usg.start_time
 ,usg.end_time
 ,usg.user_name
 ,usg.role_name
 ,usg.warehouse_name
 ,usg.warehouse_size
 ,usg.total_elapsed_secs
 ,usg.est_query_cost
 from (
 select 
 qh.start_time
 ,qh.end_time
 ,to_char(qh.start_time, 'DD-MON-YYYY')::DATE as start_dt
 ,to_char(qh.start_time, 'HH24:MI')::TIME as time_slot_min
 ,to_time(to_char(qh.start_time, 'HH24') || ':00')::TIME as time_slot_hour
 ,qh.user_name
 ,qh.role_name
 ,qh.warehouse_name
 ,qh.warehouse_size
 ,wmh.credits_used as total_vwh_credits_per_hour
 ,round(qh.total_elapsed_time * 1000) as total_elapsed_secs
 ,ceil(total_elapsed_secs / 60) as query_time_slots --query_time_slots: Number of 1 min slots taken by the query during this window
 ,sum(query_time_slots) over (partition by qh.warehouse_name, time_slot_hour order by qh.warehouse_name, time_slot_hour) as total_time_slots --total_time_slots: Total Number 1 min slots across all parallel queries running during this window
 ,query_time_slots / total_time_slots * 100 as est_query_cost_percent --est_query_cost_percent: This Query load on Warehouse during this window
 ,total_vwh_credits_per_hour * est_query_cost_percent/100 as est_query_cost --est_query_cost: Based on est_query_cost_percent we will allocate WH cost % during this window
 from snowflake.account_usage.query_history as qh
 inner join snowflake.account_usage.warehouse_metering_history as wmh on qh.warehouse_id = wmh.warehouse_id
 where qh.warehouse_size is not null
 and qh.start_time < wmh.end_time
 and qh.end_time > wmh.start_time
 and qh.total_elapsed_time > 0
 and qh.user_name = current_user()
 and qh.start_time >= getdate()::date::timestamp --get daily metric only
 ) as usg
)
,warehouse_usage_share as (
 select 
 start_time::DATE as query_date
 ,warehouse_name
 ,round(sum(est_query_cost), 2) as aprox_credits
 ,1/count(distinct user_name) as unqiue_user_share
 from query_warehouse_usage
 group by start_time::DATE, warehouse_name
)
,warehouse_credits as (
 select start_time::DATE as wh_date,
 warehouse_name,
 round(sum(credits_used), 2) as credits_used
 from snowflake.account_usage.warehouse_metering_history
 where warehouse_name <> 'CLOUD_SERVICES_ONLY'
 group by start_time::DATE, warehouse_name
)
,warehouse_adjusted_usage as (
 select 
 whc.wh_date
 ,whc.warehouse_name
 ,whc.credits_used - whs.aprox_credits as gap
 ,gap * unqiue_user_share as adjustperuser
 from warehouse_credits as whc
 inner join warehouse_usage_share as whs on whc.wh_date = whs.query_date
 AND whc.warehouse_name = whs.warehouse_name
)
,query_adjusted_usage as (
 select 
 qwu.user_name
 ,qwu.warehouse_name
 ,qwu.start_time::DATE as query_date
 ,round(sum(qwu.est_query_cost), 4) as aprox_credits
 from query_warehouse_usage as qwu
 group by qwu.user_name, qwu.warehouse_name, query_date
)
select 
 sum(round(qau.aprox_credits + wau.adjustperuser, 4)) over (partition by qau.query_date, qau.warehouse_name, qau.user_name) AS total_credits
from warehouse_adjusted_usage as wau
join query_adjusted_usage as qau on wau.wh_date = qau.query_date
and wau.warehouse_name = qau.warehouse_name
order by query_date desc, total_credits desc
;

-- average daily credit usage
with query_warehouse_usage as (
 select
 usg.start_time
 ,usg.end_time
 ,usg.user_name
 ,usg.role_name
 ,usg.warehouse_name
 ,usg.warehouse_size
 ,usg.total_elapsed_secs
 ,usg.est_query_cost
 from (
 select 
 qh.start_time
 ,qh.end_time
 ,to_char(qh.start_time, 'DD-MON-YYYY')::DATE as start_dt
 ,to_char(qh.start_time, 'HH24:MI')::TIME as time_slot_min
 ,to_time(to_char(qh.start_time, 'HH24') || ':00')::TIME as time_slot_hour
 ,qh.user_name
 ,qh.role_name
 ,qh.warehouse_name
 ,qh.warehouse_size
 ,wmh.credits_used as total_vwh_credits_per_hour
 ,round(qh.total_elapsed_time * 1000) as total_elapsed_secs
 ,ceil(total_elapsed_secs / 60) as query_time_slots --query_time_slots: Number of 1 min slots taken by the query during this window
 ,sum(query_time_slots) over (partition by qh.warehouse_name, time_slot_hour order by qh.warehouse_name, time_slot_hour) as total_time_slots --total_time_slots: Total Number 1 min slots across all parallel queries running during this window
 ,query_time_slots / total_time_slots * 100 as est_query_cost_percent --est_query_cost_percent: This Query load on Warehouse during this window
 ,total_vwh_credits_per_hour * est_query_cost_percent/100 as est_query_cost --est_query_cost: Based on est_query_cost_percent we will allocate WH cost % during this window
 from snowflake.account_usage.query_history as qh
 inner join snowflake.account_usage.warehouse_metering_history as wmh on qh.warehouse_id = wmh.warehouse_id
 where qh.warehouse_size is not null
 and qh.start_time < wmh.end_time
 and qh.end_time > wmh.start_time
 and qh.total_elapsed_time > 0
 and qh.user_name = current_user()
 ) as usg
)
,warehouse_usage_share as (
 select 
 start_time::DATE as query_date
 ,warehouse_name
 ,round(sum(est_query_cost), 2) as aprox_credits
 ,1/count(distinct user_name) as unqiue_user_share
 from query_warehouse_usage
 group by start_time::DATE, warehouse_name
)
,warehouse_credits as (
 select start_time::DATE as wh_date,
 warehouse_name,
 round(sum(credits_used), 2) as credits_used
 from snowflake.account_usage.warehouse_metering_history
 where warehouse_name <> 'CLOUD_SERVICES_ONLY'
 group by start_time::DATE, warehouse_name
)
,warehouse_adjusted_usage as (
 select 
 whc.wh_date
 ,whc.warehouse_name
 ,whc.credits_used - whs.aprox_credits as gap
 ,gap * unqiue_user_share as adjustperuser
 from warehouse_credits as whc
 inner join warehouse_usage_share as whs on whc.wh_date = whs.query_date
 AND whc.warehouse_name = whs.warehouse_name
)
,query_adjusted_usage as (
 select 
 qwu.user_name
 ,qwu.warehouse_name
 ,qwu.start_time::DATE as query_date
 ,round(sum(qwu.est_query_cost), 4) as aprox_credits
 from query_warehouse_usage as qwu
 group by qwu.user_name, qwu.warehouse_name, query_date
)
,daily_totals as (
 select
 qau.query_date,
 qau.warehouse_name,
 qau.user_name,
 sum(round(qau.aprox_credits + wau.adjustperuser, 4)) over (partition by qau.query_date, qau.user_name) as total_credits
 from warehouse_adjusted_usage as wau
 join query_adjusted_usage as qau on wau.wh_date = qau.query_date
 AND wau.warehouse_name = qau.warehouse_name
 and qau.query_date = :daterange --dateadd(day, -30, getdate())
)
select 
 round(avg(total_credits), 4) as avg_total_credits
from daily_totals
;

Total Credit Usage By Day (By Warehouse) Visualized

-- total credit usage by day (sliced by warehouse in chart)with query_warehouse_usage as (
 select
 usg.start_time
 ,usg.end_time
 ,usg.user_name
 ,usg.role_name
 ,usg.warehouse_name
 ,usg.warehouse_size
 ,usg.total_elapsed_secs
 ,usg.est_query_cost
 from (
 select 
 qh.start_time
 ,qh.end_time
 ,to_char(qh.start_time, 'DD-MON-YYYY')::DATE as start_dt
 ,to_char(qh.start_time, 'HH24:MI')::TIME as time_slot_min
 ,to_time(to_char(qh.start_time, 'HH24') || ':00')::TIME as time_slot_hour
 ,qh.user_name
 ,qh.role_name
 ,qh.warehouse_name
 ,qh.warehouse_size
 ,wmh.credits_used as total_vwh_credits_per_hour
 ,round(qh.total_elapsed_time * 1000) as total_elapsed_secs
 ,ceil(total_elapsed_secs / 60) as query_time_slots --query_time_slots: Number of 1 min slots taken by the query during this window
 ,sum(query_time_slots) over (partition by qh.warehouse_name, time_slot_hour order by qh.warehouse_name, time_slot_hour) as total_time_slots --total_time_slots: Total Number 1 min slots across all parallel queries running during this window
 ,query_time_slots / total_time_slots * 100 as est_query_cost_percent --est_query_cost_percent: This Query load on Warehouse during this window
 ,total_vwh_credits_per_hour * est_query_cost_percent/100 as est_query_cost --est_query_cost: Based on est_query_cost_percent we will allocate WH cost % during this window
 from snowflake.account_usage.query_history as qh
 inner join snowflake.account_usage.warehouse_metering_history as wmh on qh.warehouse_id = wmh.warehouse_id
 where qh.warehouse_size is not null
 and qh.start_time < wmh.end_time
 and qh.end_time > wmh.start_time
 and qh.total_elapsed_time > 0
 and qh.user_name = current_user()
 and qh.start_time = :daterange 
 ) as usg
)
,warehouse_usage_share as (
 select 
 start_time::DATE as query_date
 ,warehouse_name
 ,round(sum(est_query_cost), 2) as aprox_credits
 ,1/count(distinct user_name) as unqiue_user_share
 from query_warehouse_usage
 group by start_time::DATE, warehouse_name
)
,warehouse_credits as (
 select start_time::DATE as wh_date,
 warehouse_name,
 round(sum(credits_used), 2) as credits_used
 from snowflake.account_usage.warehouse_metering_history
 where warehouse_name <> 'CLOUD_SERVICES_ONLY'
 group by start_time::DATE, warehouse_name
)
,warehouse_adjusted_usage as (
 select 
 whc.wh_date
 ,whc.warehouse_name
 ,whc.credits_used - whs.aprox_credits as gap
 ,gap * unqiue_user_share as adjustperuser
 from warehouse_credits as whc
 inner join warehouse_usage_share as whs on whc.wh_date = whs.query_date
 AND whc.warehouse_name = whs.warehouse_name
)
,query_adjusted_usage as (
 select 
 qwu.user_name
 ,qwu.warehouse_name
 ,qwu.start_time::DATE as query_date
 ,round(sum(qwu.est_query_cost), 4) as aprox_credits
 from query_warehouse_usage as qwu
 group by qwu.user_name, qwu.warehouse_name, query_date
)
select 
 qau.query_date
 ,qau.warehouse_name
 ,qau.user_name
 ,sum(round(qau.aprox_credits + wau.adjustperuser, 4)) over (partition by qau.query_date, qau.warehouse_name, qau.user_name) AS total_credits
from warehouse_adjusted_usage as wau
join query_adjusted_usage as qau on wau.wh_date = qau.query_date
and wau.warehouse_name = qau.warehouse_name
and qau.query_date = :daterange -->= dateadd(day, -30, getdate())
order by qau.query_date desc, total_credits desc;

Visual Chart Setup for this query:

Execution Time Averaged By Query Type

select
 qh.query_type
 ,qh.warehouse_size
 ,round(avg(qh.execution_time) / 1000, 2) as average_execution_time
from snowflake.account_usage.query_history as qh
where start_time::date = :daterange --dateadd(day, -30, getdate()) --> dateadd('days', -30, getdate())
and qh.user_name = current_user()
group by 1, 2
order by average_execution_time desc;

Top 25 Longest Queries (in execution time minutes)

select top 25
 qh.query_id
 ,qh.query_text
 ,(qh.execution_time / 60000) as execution_time
from account_usage.query_history as qh
where execution_status = 'SUCCESS'
and start_time::date = :daterange --> dateadd('days', -30, getdate())
and qh.user_name = current_user() 
order by execution_time desc
;

Top Credit Usage By Repeated Queries

WITH
filtered_queries AS (
 SELECT
 query_id,
 user_name,
 query_text AS original_query_text,
 -- First, we remove comments enclosed by /* <comment text> */
 REGEXP_REPLACE(query_text, '(/\*.*\*/)') AS _cleaned_query_text,
 -- Next, removes single line comments starting with --
 -- and either ending with a new line or end of string
 REGEXP_REPLACE(_cleaned_query_text, '(--.*$)|(--.*\n)') AS cleaned_query_text,
 warehouse_id,
 TIMEADD(
 'millisecond',
 queued_overload_time + compilation_time +
 queued_provisioning_time + queued_repair_time +
 list_external_files_time,
 start_time
 ) AS execution_start_time,
 end_time
 FROM snowflake.account_usage.query_history AS q
 WHERE TRUE
 AND warehouse_size IS NOT NULL
 AND start_time = :daterange -->= DATEADD('day', -30, DATEADD('day', -1, CURRENT_DATE))
),
-- 1 row per hour from 30 days ago until the end of today
hours_list AS (
 SELECT
 DATEADD(
 'hour',
 '-' || row_number() over (order by null),
 DATEADD('day', '+1', CURRENT_DATE)
 ) as hour_start,
 DATEADD('hour', '+1', hour_start) AS hour_end
 FROM TABLE(generator(rowcount => (24*31))) t
),
-- 1 row per hour a query ran
query_hours AS (
 SELECT
 hl.hour_start,
 hl.hour_end,
 queries.*
 FROM hours_list AS hl
 INNER JOIN filtered_queries AS queries
 ON hl.hour_start >= DATE_TRUNC('hour', queries.execution_start_time)
 AND hl.hour_start < queries.end_time
),
query_seconds_per_hour AS (
 SELECT
 *,
 DATEDIFF('millisecond', GREATEST(execution_start_time, hour_start), LEAST(end_time, hour_end)) AS num_milliseconds_query_ran,
 SUM(num_milliseconds_query_ran) OVER (PARTITION BY warehouse_id, hour_start) AS total_query_milliseconds_in_hour,
 num_milliseconds_query_ran/total_query_milliseconds_in_hour AS fraction_of_total_query_time_in_hour,
 hour_start AS hour
 FROM query_hours
),
credits_billed_per_hour AS (
 SELECT
 start_time AS hour,
 warehouse_id,
 warehouse_name,
 credits_used_compute
 FROM snowflake.account_usage.warehouse_metering_history
),
query_cost AS (
 SELECT
 query.*,
 credits.credits_used_compute*2.28 AS actual_warehouse_cost,
 credits.credits_used_compute*fraction_of_total_query_time_in_hour*2.28 AS query_allocated_cost_in_hour,
 credits.warehouse_name
 FROM query_seconds_per_hour AS query
 INNER JOIN credits_billed_per_hour AS credits
 ON query.warehouse_id=credits.warehouse_id
 AND query.hour=credits.hour
),
cost_per_query AS (
 SELECT
 query_id,
 user_name,
 ANY_VALUE(MD5(cleaned_query_text)) AS query_signature,
 SUM(query_allocated_cost_in_hour) AS query_cost,
 ANY_VALUE(original_query_text) AS original_query_text,
 ANY_VALUE(warehouse_id) AS warehouse_id,
 ANY_VALUE(warehouse_name) as warehouse_name,
 SUM(num_milliseconds_query_ran) / 1000 AS execution_time_s
 FROM query_cost
 GROUP BY 1, 2
)
SELECT top 50
 user_name,
 --query_signature,
 ANY_VALUE(warehouse_name) as warehouse_name,
 COUNT(*) AS num_executions,
 AVG(query_cost) AS avg_cost_per_execution,
 SUM(query_cost) AS total_cost_last_time_period,
 ANY_VALUE(original_query_text) AS sample_query_text
FROM cost_per_query
where user_name = CURRENT_USER()
GROUP BY 1, query_signature
order by total_cost_last_time_period desc
;

Queries With Full Table Scans

--queries with (near) full table scans
select
 user_name
 ,start_time
 ,end_time
 ,round(partitions_scanned / partitions_total, 2) * 100 as percent_table_partitions_scanned
 ,query_text
from snowflake.account_usage.query_history
where start_time::date = :daterange --> dateadd('days', -30, current_date)
and partitions_scanned > (partitions_total * 0.95)
and query_type not like 'CREATE%'
and partitions_total > 0
and user_name = current_user()
order by start_time desc;

Heavy Query Partition Scanning By Warehouse (vs an organization average)

with warehouse_average_all as (
 select 
 warehouse_name
 ,avg(case when partitions_total > 0 then partitions_scanned / partitions_total else 0 end) as avg_pct_scanned
 from snowflake.account_usage.query_history
 where start_time::date = :daterange -- > dateadd('days', -30, getdate())
 and warehouse_name is not null
 group by warehouse_name
)
select 
 qh.user_name
 ,qh.warehouse_name
 ,round(avg(case when qh.partitions_total > 0 then qh.partitions_scanned / qh.partitions_total else 0 end), 4) as user_avg_query_pct_scanned
 ,round(waa.avg_pct_scanned, 4) as org_avg_query_pct_scanned
from snowflake.account_usage.query_history as qh
left join warehouse_average_all waa on waa.warehouse_name = qh.warehouse_name
where start_time::date = :daterange -- > dateadd('days', -30, getdate())
and qh.warehouse_name is not null
and qh.user_name = current_user()
group by qh.user_name, qh.warehouse_name, waa.avg_pct_scanned
order by user_avg_query_pct_scanned desc
;

Information on Roles and Warehouses Used

--get roles used for visibility of resources being used/available to users
select role_type, role_name, max(start_time::date) as last_used_date
FROM snowflake.account_usage.query_history 
where user_name = current_user() 
and start_time::date = :daterange -->= dateadd(day, -30, getdate())
group by role_type, role_name
order by last_used_date desc
;
--get warehousehouses used for visibility of resources being used/available to roles assigned
select warehouse_name, warehouse_size, max(start_time::date) as last_used_date
FROM snowflake.account_usage.query_history 
where user_name = current_user() 
and warehouse_name is not null and warehouse_size is not null
and start_time::date = :daterange -->= dateadd(day, -30, getdate())
group by warehouse_name, warehouse_size
order by last_used_date desc;

With these queries as a starting point, I hope your journey on creating visibility into cloud computing spend is well underway! My manager will certainly be happier that I can explain and pinpoint what behaviors I have as a user that need some nudges toward best practice and further platform education when the bill comes each month.