Search code examples
google-bigquerygcloudgoogle-bi-engine

BigQuery - BI Engine measuring savings


I have deployed BI Engine in one of my Google projects and I am measuring cost savings using the following query

with tbl
as
(
    select creation_time, total_bytes_processed, total_bytes_billed,    
           5 * (total_bytes_processed / 1000000000000) as cost_projected, 
           5 * (total_bytes_billed / 1000000000000) as cost_actual

      from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT b
     where 1=1
       and job_type = "QUERY"
       and creation_time >= '2022-05-10 11:30:00.000 UTC'
       and creation_time <= '2022-05-10 19:00:00.000 UTC'
)
select sum(cost_projected) - sum(cost_actual) as savings
  from tbl
 where 1=1
;

However, I noticed that very often I have accelerated queries (bi_engine_statistics.bi_engine_mode = 'FULL') for which 'total_bytes_billed = total_bytes_processed'. I was expecting that for accelerated queries total_bytes_billed should be equal to zero which does not seem to be the case.

So the questions are:

  1. Is my query the correct way of measuring savings,
  2. Is it normal to have fully accelerated queries with total_bytes_billed > 0?

Solution

    1. To answer your question on calculation of savings:
      I think it is the correct way of measuring your savings, but some queries of type QUERY cannot be used for BI Engine, so it's somewhat unfair to keep counting them in.
      Which is why I wrote the script in this SO question:
      BigQuery BI Engine: how to choose a good reservation size?
      Also you could improve on converting bytes to TB by calculating as follows:
      sum(total_bytes_processed) / pow(1024, 4) AS TB_processed

    1. As for your question on FULL mode and still having to pay:
      I have turned BI Engine on and if I run this query on my data, I get 0 results, so all my bi_engine_mode='FULL' queries have savings:
    SELECT 
        total_bytes_processed, 
        total_bytes_billed, 
        bi_engine_statistics
    FROM `my_project_id.region-eu.INFORMATION_SCHEMA.JOBS`
    WHERE 1=1
        and bi_engine_statistics.bi_engine_mode = 'FULL'
        and total_bytes_processed = total_bytes_billed
        and total_bytes_processed > 0