How to create materialized views and understanding how BI Engine works

BigQuery has a feature called materialized views. It's not a table, nor a view; it's a materialized view. To understand it, let's go back to what a table is compared to a view. One of the reasons you create tables is that you want to store transformation results to be used for downstream usage. The reason you create a view instead of a table is that you need the data in real time, but with a view, you always pre-compute all the processes. A materialized view is somewhere in between. With materialized views, you can have real-time access, but the processes aren't pre-computed. 

It's easier to understand is by trying it in practice, so let's set up a scenario. Let's use our facts_trip_daily table and run this query from the BigQuery console:

SELECT trip_date, sum(sum_duration_sec) as sum_duration_sec 
FROM `packt-data-eng-on-gcp.dwh_bikesharing.facts_trips_daily`...