Optimize BigQuery BI Engine Memory usage by Using Materialized Views

I blog previously how to create a Materialized View in BigQuery, and Obviously I talk a lot about BI Engine, but do they work together ?

To test it, I built a simple report in PowerBI using live mode ( you can use your favorite BI tool), 1 fact table with 78 Millions records at the minute granularity and a small dimension Table

In every Selection PowerBI send a SQL Query which scan 1.79 GB

Then I created a Materialized View , notice I skipped the time dimension

SELECT
  xx.DUID,
  yy.StationName,
  yy.Region,
  yy.FuelSourceDescriptor,
  yy.Technology,
  yy.latitude,
  yy.longitude,
  yy.Tech,
  DAY,
  SUM(Mwh) AS Mwh
FROM
  `xxxxx.UNITARCHIVE` xx
INNER JOIN
  xxxxx.DUID_DIM yy
ON
  xx.DUID=YY.DUID
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9

The Same Query will scan only 10 MB, and take 700 ms

Now let’s add 1 GB of BI Engine Reservation, the duration was reduced from 700 ms to 400 ms

But the nice surprise is this !!! The Memory used is only 2.78 MB

BI Engine was smart enough to load only the Materialized View instead of the Base Table, That’s a better usage of resources and will scale better.

Obviously if you select another column not available in the Materialized View ( like time), BI Engine will load the column Required automatically from the base Table.