get-select / dbt-snowflake-monitoring

A dbt package from SELECT to help you monitor Snowflake performance and costs
https://select.dev/docs/dbt-snowflake-monitoring
MIT License
219 stars 40 forks source link

[v5.0.0] Add query acceleration costs, incrementalize cost per query, account for new service type #141

Closed ian-whitestone closed 10 months ago

ian-whitestone commented 10 months ago

This PR does the following:

cost per query reconciliations:

check that everything lines up, ✅

with
existing as (
    select 
        query_id,
        start_time,
        query_cost,
        query_credits
    from cost_per_query -- current model in prod
    where start_time between '2023-11-01' and '2023-12-01'
),
new as (
    select 
        query_id,
        start_time,
        query_cost,
        query_credits
    from dev.ian_dbt_snowflake_monitoring.cost_per_query
    where start_time between '2023-11-01' and '2023-12-01'
)
select
    *
from existing
full outer join new
    on existing.query_id=new.query_id
where
    existing.query_cost <> new.query_cost
    or existing.query_credits <> new.query_credits
    or existing.query_id is null
    or new.query_id is null
;

check account with QA enabled, costs line up if you remove QA ✅

with
existing as (
    select 
        query_id,
        start_time,
        query_cost,
        query_credits
    from cost_per_query -- prod model
    where start_time between '2024-01-01' and '2024-01-05' -- spot checked multiple sets of dates
),
new as (
    select 
        query_id,
        start_time,
        query_cost - query_acceleration_cost as query_cost,
        query_credits - query_acceleration_credits as query_credits
    from dev.ian_dbt_snowflake_monitoring.cost_per_query
    where start_time between '2024-01-01' and '2024-01-05'
)
select
    *
from existing
full outer join new
    on existing.query_id=new.query_id
where
    round(existing.query_cost, 4) <> round(new.query_cost, 4)
    or round(existing.query_credits, 4) <> round(new.query_credits, 4)
    or existing.query_id is null
    or new.query_id is null
;