データシェアの新しいビューでUsage Groupsクエリを簡素化

以前は、Usage Groupsを含むデータシェアに対してクエリを記述する際、完全なUsage Groupの定義がクエリに含まれるため、クエリの複雑さが増し、可読性が低下していました。

1with 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

この問題を解決するため、Usage Groupの定義を抽象化する新しいビューを作成し、よりシンプルなクエリが可能になりました。

1select 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