Optimize Bi Engine Materialized Views Joins

What materialized views and BI Engine are

BigQuery BI Engine is a fast, in-memory analysis service that accelerates SQL queries by intelligently caching frequently used data. Materialized views are precomputed views that periodically cache the results of a query, such as complex joins or aggregations.

How it meets the requirements

• Minimize query latency: BI Engine caches the data in memory, providing sub-second response times for the dashboard.
• Reduce analytical costs: The materialized view pre-joins the massive fact table with the dimension tables, producing a single flat table. This ensures the expensive join operations are not recomputed every time a user interacts with the dashboard.
• Optimize data layout: Partitioning the materialized view by the sales date ensures that only the latest partitions (the current month's data) are loaded into BI Engine memory, optimizing memory allocation.
• Leverage BI Engine: By adding both the materialized view and its base tables to the preferred tables list, the configuration satisfies the strict requirements for accelerating materialized views.

Key advantages/capabilities

• Combines the pre-aggregation benefits of materialized views with the in-memory speed of BI Engine.
• Partitioning prevents older, unused data from consuming valuable memory reservations.
• Preferred tables prevent other traffic from interfering with the acceleration of high-visibility dashboards.

Why this is optimal

BI Engine works best with pre-joined or pre-aggregated data. Using a materialized view to flatten the data structure is the recommended best practice for optimizing performance. Furthermore, BigQuery requires that both the materialized view and its base tables be designated as preferred tables for the acceleration to occur, making this the only technically correct configuration.