Creating 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 by trying it in practice, so let’s set up a scenario:

1. Create an aggregation query in the BigQuery console.

   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...