BigQuery pricing unit for onDemandCost is tebibyte instead of terabyte

[dleser]

Many queries used in this repo are calculating the on-demand costs and billed bytes/MBs/GBs like in this example:

       SUM(COALESCE(totalBilledBytes, 0)) AS totalBilledBytes,
        ROUND(SUM(COALESCE(totalBilledBytes,
              0)) / 1000000, 2) AS totalMegabytesBilled,
        ROUND(SUM(COALESCE(totalBilledBytes,
              0)) / 1000000000, 2) AS totalGigabytesBilled,
        ROUND(SUM(COALESCE(totalBilledBytes,
              0)) / 1000000000000, 2) AS totalTerabytesBilled,
        ROUND(SAFE_DIVIDE(SUM(COALESCE(totalBilledBytes, 0)),
          1000000000000) * 5, 2) AS onDemandCost,

But the pricing unit used by Google to calculate the costs is tebibyte , not terabyte

• Kibibyte (KiB) 1024¹ = 1,024
• Mebibyte (MiB) 1024² = 1,048,576
• Gibibyte (GiB) 1024³ = 1,073,741,824
• Tebibyte (TiB) 1024⁴ = 1,099,511,627,776

I have used this query with the exported billing records table to check this:

select
  service.description as service_description, 
  sku.description as sku_description,
  usage.unit as usage_unit,
  usage.pricing_unit as usage_pricing_unit,
  count(*) as count,
  sum(cost) as cost_eur,
  sum(cost / currency_conversion_rate) as cost_usd,
  sum(usage.amount) as usage_amount,
  sum(usage.amount_in_pricing_units) as amount_in_pricing_units,
  sum(cost / currency_conversion_rate) / sum(usage.amount_in_pricing_units) as cost_per_pricing_unit_usd,
  sum(usage.amount) / sum(usage.amount_in_pricing_units) as amount_to_pricing_unit_conversion_rate,
  pow(1024, 4)  as tebibyte_in_bytes
from `<MY_BILLING_EXPORT_TABLE`
where 1=1
  and _partitiontime >= timestamp_sub(current_timestamp, interval 7 day)
  and service.description = "BigQuery"
group by service.description, sku.description, usage_unit, usage_pricing_unit
order by service.description, sku.description 

BTW: Awesome that you have collected these SQL statements here, this will really help me! :-)