View full changelog

New views in data shares to simplify Usage Group queries

Date
  • Fernando Brito
    Software Engineer at SELECT

Previously, when writing a query against our data shares involving Usage Groups, the full Usage Group definition would be present in the query leading to increased query complexity and damaging readability.

with
select
start_day as day,
sum_query_cost
from (
select
start_day as day,
-- full usage group definition included
case
when warehouse_name in ('DBT_S', 'DBT_M') or user_name in ('SVC_DBT') then 'Analytics Engineering'
when warehouse_name in ('Warehouse 1', 'Warehouse 2') or user_role in ('MARKETING_ANALYST') or database_name in ('MARKETING') then 'Marketing Team'
when ...
when ...
else 'Uncategorized'
end as usage_group,
sum(sum_query_cost) as sum_query_cost
from select_datashare.transformed.daily_workloads_usage_groups
where
start_day >= '2024-09-01'
and start_day <= '2024-09-14'
group by all
)
where
usage_group in ('Marketing Team')
order by day desc nulls last

We've since created new views, which abstract away the Usage Group definition leading to simpler queries.

select
start_day as day,
sum(sum_query_cost) as sum_query_cost
from select_datashare.transformed.daily_workloads_usage_groups
where
usage_group in ('Marketing Team')
and start_day >= '2024-09-01'
and start_day <= '2024-09-14'
group by all
order by day desc nulls last

Get up and running with SELECT in 15 minutes.

Snowflake optimization & cost management platform

Gain visibility into Snowflake usage, optimize performance and automate savings with the click of a button.

SELECT web application screenshot

Want to get notified when we release new features? 
Subscribe to get notified.