Hi! I’m trying to get storage cost in BQ with following query:
with
data as (
select
sum(billable_active_logical_usage) as billable_active_logical_mb,
sum(billable_long_term_logical_usage) as billable_long_term_logical_mb
from `region-EU`.`INFORMATION_SCHEMA`.`TABLE_STORAGE_USAGE_TIMELINE`
where date_trunc(usage_date, month) = '2023-12-01'
),
data2 as (
select
*,
billable_active_logical_mb / 1024 as billable_active_logical_gb,
billable_long_term_logical_mb / 1204 as billable_long_term_logical_gb
from data
),
data3 as (
select
*,
billable_active_logical_gb * 0.02 as billable_active_logical_usd,
billable_long_term_logical_gb * 0.01 as billable_long_term_logical_usd
from data2
)
select *
from data3
But it gives me weird results with thousands of USD in last CTE. Spend a lot of time on figuring out how to get values close to Billing Report but no luck. Any thoughts on how to do it correctly?