A materialized view stores the result of a query and keeps it up to date, so repeated queries read a small precomputed result. BI Engine is an in-memory acceleration layer for dashboards. Both reduce scan cost and latency for repeated analytics.
Materialized views
CREATE MATERIALIZED VIEW analytics.daily_revenue AS SELECT order_date, region, SUM(amount) AS revenue, COUNT(*) AS orders FROM analytics.orders GROUP BY order_date, region;
BigQuery refreshes materialized views automatically in the background and, when it can, incrementally, processing only the changed data. If the base table has changed since the last refresh, BigQuery can combine the stored results with the new rows at query time, so answers stay current.
Smart tuning is a useful feature: if someone queries the base table with an aggregation that a materialized view could answer, BigQuery can rewrite the query to read the view instead, without the user knowing.
Limits: only a restricted set of SQL is allowed (limited joins, a set of supported aggregate functions, no non-deterministic functions such as CURRENT_TIMESTAMP), and refresh uses compute, which is billed. If your needed query is outside those limits, build a summary table with a scheduled query.
BI Engine
BI Engine keeps frequently used data in memory, and serves queries from it quickly, mostly for dashboards. It works with Looker, Looker Studio, connected Sheets and other BI tools that send SQL. You buy a reservation sized in GB of memory. Queries that fit run in sub-second time, and parts that cannot be accelerated fall back to normal execution.
When to use which
- Many users run the same aggregation over a big table: materialized view.
- Dashboards with many small interactive queries on a modest dataset, where speed matters: BI Engine.
- Both together are common: the view shrinks the data, and BI Engine serves it from memory.
Mention the trade-off: both cost something, and they pay off only when the same patterns repeat often. A report run once a week does not need either.