BigQuery BI Engine and Acceleration

Introduction to BigQuery BI Engine and Performance Acceleration

BigQuery BI Engine is an in-memory analysis service that turns BigQuery into a sub-second engine for interactive dashboards. On the GCP PDE exam, BigQuery BI Engine and Performance Acceleration shows up whenever a scenario mentions Looker Studio lag, dashboard concurrency, or a need for predictable interactive latency without re-platforming to a separate OLAP cube. This note covers when BigQuery BI Engine and Performance Acceleration earns its keep, when it is the wrong tool, and how it pairs with materialized views, slot reservations, and query plan analysis.

白話文解釋（Plain English Explanation）

BigQuery BI Engine and Performance Acceleration is one of those features that sounds magical until you understand what is actually happening behind the curtain. Three analogies below make the moving parts concrete.

Think of BI Engine as a Restaurant Pass at a Busy Kitchen

A high-end kitchen has a "pass" — a heated counter where the most-ordered dishes sit ready to plate. When a server calls for a dish, the chef does not start from raw ingredients; the dish is already 90% done and just needs a garnish. BigQuery BI Engine and Performance Acceleration plays the same role. Frequently queried columns are pre-loaded into RAM in a vectorised columnar format. When a Looker Studio dashboard fires a query, BigQuery BI Engine and Performance Acceleration does not scan storage; it grabs hot data from memory and returns the answer in milliseconds. Cold data still needs the full kitchen, but the items on the pass fly out the door.

Think of Materialized Views as a Pre-Filled Crossword Grid

Imagine you do the same crossword puzzle every morning, and the answers never change for the first 50 squares. A sensible person fills those squares once, photocopies the grid, and starts each new day with 50 squares already inked in. Materialized views in BigQuery work the same way. The aggregation result is pre-computed and stored. When a new query asks for the same SUM by region, BigQuery rewrites the query to read the materialized view instead of scanning the base table. Combined with BigQuery BI Engine and Performance Acceleration, the materialized view sits in memory, giving you a pre-filled grid that lives on the express shelf.

Think of Slot Reservations as Renting Lanes at a Bowling Alley

A public bowling alley has 30 lanes. On a Friday night, anyone can grab whatever lane is free, and you might wait 20 minutes. A corporate league rents 10 lanes every Friday at 7 PM — guaranteed, no waiting, even if it means those lanes sit empty Tuesday afternoon. BigQuery slot reservations work exactly like that lane contract. You commit to capacity, your queries get predictable performance, and BigQuery BI Engine and Performance Acceleration adds a separate layer: the in-memory cache lives outside the slot pool, so a hot dashboard query can complete without consuming reserved slots at all.

Core Concepts of BigQuery BI Engine and Performance Acceleration

BigQuery BI Engine and Performance Acceleration is built on a small set of primitives. Knowing these by name is half the battle on the exam.

BI Engine reservation — A regional, project-level commitment of memory (1 GiB to 250 GiB per project per region) that BigQuery uses to cache query data in a vectorised columnar format. You pay for the reservation 24/7 whether queries run or not.

SQL acceleration scope — Since the 2022 GA of BI Engine SQL Interface, BI Engine accelerates any standard SQL query, not just Looker Studio. That includes the bq CLI, the BigQuery API, JDBC/ODBC drivers, and connected tools like Tableau, Power BI, Looker (the enterprise platform), and Looker Studio.

Preferred tables — An optional allowlist that pins specific tables into memory and prevents BI Engine from evicting them when memory pressure hits.

Materialized views — Pre-computed, incrementally maintained query results stored as a separate object. The optimiser auto-rewrites queries to use them when applicable, even if the user query targets the base table.

Query cache — A free, 24-hour result cache that returns identical query results without billing bytes. Cache hits do not count against slot usage either.

Slot reservations — Capacity-based pricing that allocates a fixed number of slots to a workload. Slots are reusable units of compute (CPU + RAM + network) for query execution.

EXPLAIN / query plan — The execution graph BigQuery emits for every query, showing stages, slot-ms, shuffle bytes, and the BI Engine acceleration mode (FULL, PARTIAL, or DISABLED).

HyperLogLog++ (HLL) — Approximate count-distinct algorithm exposed via APPROX_COUNT_DISTINCT and the HLL_COUNT.* family. Trades tiny accuracy loss for massive speed and slot savings on high-cardinality dashboards.

Architecture and Design Patterns

A production deployment of BigQuery BI Engine and Performance Acceleration usually layers four components on top of the same base tables.

[ Source tables ]
       |
       v
[ Materialized views ]   <-- pre-aggregate hot rollups
       |
       v
[ BI Engine reservation ] <-- in-memory cache, regional
       |
       v
[ Looker / Looker Studio ] <-- dashboards, sub-second
       |
       v
[ Query cache ]            <-- 24h dedupe for repeat hits

The pattern is additive. The query cache catches repeat queries for free. The materialized view catches semantically equivalent aggregation queries even if the user query is different. BI Engine catches everything that fits in RAM and qualifies for SQL acceleration. Slots cover the rest. Each layer absorbs a different traffic pattern, and removing any one of them shifts pressure to the next layer below.

A common anti-pattern is treating BI Engine as a substitute for modelling. Teams often skip clustering or partitioning, assume BI Engine will paper over it, then watch their reservation thrash because the working set exceeds memory. BigQuery BI Engine and Performance Acceleration multiplies the value of good schema design; it does not replace it.

GCP Service Deep Dive

BI Engine Reservation Sizing

Reservations are billed per GiB per hour. As of the most recent pricing pages, the rate is in the low single-digit cents per GiB-hour, prorated. You set the size in the BigQuery console under BI Engine → Create reservation, choosing a project, a region, and a memory amount. The minimum is 1 GiB and the maximum is 250 GiB per project per region. Reservations are regional, so a multi-region dashboard hosted from US multi-region versus us-central1 regional storage will not see acceleration; co-locate the reservation with the table region.

To right-size, run dashboards for a week without a reservation, capture the working set from INFORMATION_SCHEMA.JOBS_BY_PROJECT (look at total_bytes_processed for the dashboard service account), and add 30% headroom. If bi_engine_statistics.bi_engine_mode = 'PARTIAL' shows up frequently, you are evicting hot data and need more memory, or you need to declare preferred tables to pin the right ones.

Looker and Looker Studio Integration

Looker Studio (formerly Data Studio) uses the BigQuery connector. As soon as a BI Engine reservation exists in the same project and region as the queried table, Looker Studio queries are eligible for acceleration with zero configuration. The dashboard does not know it is being accelerated; the user just sees faster tile loads.

Looker (the enterprise platform with LookML) also benefits, but with a wrinkle. LookML-generated SQL often includes patterns BI Engine cannot accelerate — symmetric aggregates with COUNT(DISTINCT ...), certain OVER() window patterns, and queries that join against external tables. Run Looker's SQL Runner with EXPLAIN to confirm acceleration. For unsupported patterns, switch to APPROX_COUNT_DISTINCT or pre-aggregate via a persistent derived table.

Tableau and Power BI hit BI Engine through the BigQuery JDBC/ODBC drivers. The same SQL acceleration scope applies — if the SQL the BI tool generates is supported, it accelerates.

Materialized Views in Detail

Materialized views auto-refresh incrementally as the base table changes (default within a few minutes), and the optimiser uses them transparently via "smart tuning". A query like SELECT region, SUM(amount) FROM sales GROUP BY region will be rewritten to read from a matching materialized view even if the user never references the view by name.

Limitations matter on the exam. Materialized views do not support all SQL — no outer joins on the base table, no LIMIT in the view definition, no ORDER BY, no non-deterministic functions like CURRENT_TIMESTAMP(). They cost storage (you pay for the materialized rows) and a small refresh cost. For dashboards with stable aggregation patterns, the cost is far less than re-scanning the base table on every load.

Query Caching

Every query result is cached for 24 hours, keyed by the query text and the underlying table version. Cache hits are free — zero bytes billed, zero slot usage. The cache is per-user by default; set useQueryCache: true and useLegacySql: false and ensure the query is deterministic (no CURRENT_TIME(), no wildcard tables, no DML). Cached results are not used if the destination table is set or if the query writes to a permanent table.

Query cache is the cheapest acceleration layer. Before reaching for BI Engine, check whether your dashboards even benefit from caching. If users hit a "Refresh" button that disables cache, fix the dashboard before paying for memory.

Slot Reservation Interaction

Slots and BI Engine are independent billing dimensions. A query fully accelerated by BI Engine consumes near-zero slots — the work happens in the BI Engine memory pool, not the standard query engine. A query that falls out to PARTIAL acceleration uses slots for the non-accelerated stages. A query that misses BI Engine entirely uses slots like any normal query.

This independence is the core argument for buying both. Reservations smooth out concurrency for ETL and ad-hoc analysis. BI Engine smooths out latency for interactive BI. They protect different SLOs. On the exam, watch for scenarios where dashboards are slow but ETL is fine — that is a BI Engine signal, not a slot signal.

Query Plan Analysis with EXPLAIN

Every BigQuery query exposes a query plan in the job metadata. The console shows it under Execution details; the API returns it in Job.statistics.query.queryPlan. Each stage reports input/output rows, shuffle bytes, slot-ms, and wait/read/compute/write times. The bi_engine_statistics field reports the acceleration mode and, if not FULL, a reason code such as UNSUPPORTED_SQL_TEXT or INPUT_TOO_LARGE.

Reading plans is a learnable skill. The high-leverage signals: a single stage with disproportionate slot-ms is a hot stage worth optimising; high shuffle_output_bytes means the join is moving too much data and probably needs CLUSTER BY on the join key; a JOIN stage producing more rows than its inputs means you have a Cartesian product and a missing join condition.

HyperLogLog and Approximate Aggregation

COUNT(DISTINCT user_id) over a billion-row table is expensive: every distinct value must be tracked in a hash set, which means cross-shuffle communication and memory pressure. HyperLogLog++ replaces the exact count with a probabilistic sketch, typically accurate to ~1%, at a fraction of the cost.

BigQuery exposes two flavours. APPROX_COUNT_DISTINCT(x) is the one-shot version. The HLL_COUNT.* family lets you persist sketches as BYTES columns, merge them across partitions, and re-evaluate without re-scanning raw data. For dashboards showing weekly active users across a year, store one HLL sketch per day and merge — the dashboard query reads 365 sketches instead of billions of events.

HyperLogLog matters for BI Engine because exact COUNT(DISTINCT) often falls out of FULL acceleration on large inputs, while APPROX_COUNT_DISTINCT stays accelerated. Swapping the function is sometimes the difference between a 200 ms dashboard and a 12 second dashboard.

Common Pitfalls and Trade-offs

Teams running BigQuery BI Engine and Performance Acceleration in production trip on a predictable set of issues.

Region mismatch. A reservation in us-central1 will not accelerate queries against tables in the US multi-region. The reservation appears active in the console but bi_engine_mode returns DISABLED with reason UNSUPPORTED_TABLE_LOCATION. Always co-locate.

Working set exceeds reservation. With three dashboards each touching 30 GiB of hot columns, a 50 GiB reservation thrashes. Symptoms: random PARTIAL modes, inconsistent dashboard latency, evictions visible in INFORMATION_SCHEMA.BI_ENGINE_STATISTICS_BY_PROJECT. Fix: increase reservation, declare preferred tables, or shrink the working set with materialized views.

SELECT * dashboards. A dashboard that pulls every column blows out the working set even if only three columns render. Trim the SQL.

Wildcard tables. Queries against project.dataset.events_* are not eligible for BI Engine SQL acceleration. Either use partitioned tables or accept that wildcard queries skip the cache.

Looker symmetric aggregates. LookML's symmetric aggregate trick produces SQL with MD5 hashing that BI Engine cannot accelerate. Switch the measure to a non-symmetric aggregate where data integrity allows.

Forgetting query cache. Buying BI Engine to fix a dashboard that just needs useQueryCache: true is expensive theatre. Diagnose first.

Over-relying on autoscaling slots. Editions slot autoscaling solves throughput, not latency. A dashboard waiting 4 seconds for autoscaling to provision slots is still a slow dashboard. BI Engine bypasses that wait entirely.

Best Practices

• Co-locate the BI Engine reservation with the table region — same regional name, not US versus us-central1.
• Right-size by measuring actual hot working set with INFORMATION_SCHEMA.JOBS for one to two weeks before committing.
• Declare preferred tables for predictable dashboards to prevent LRU eviction by ad-hoc queries.
• Run an EXPLAIN on every dashboard tile during development and confirm bi_engine_mode = 'FULL'.
• Replace exact COUNT(DISTINCT) with APPROX_COUNT_DISTINCT on any dimension above 100k cardinality unless the report is a financial reconciliation.
• Cluster fact tables on the most common filter columns; clustering reduces scanned bytes before BI Engine even loads data.
• Use materialized views for stable rollups (daily revenue by region, hourly active users) where the SQL constraints permit.
• Monitor INFORMATION_SCHEMA.BI_ENGINE_STATISTICS_BY_PROJECT weekly to catch growing eviction rates before users complain.

Real-World Use Case

A mid-sized retail analytics team — 200 internal dashboard users at a fashion retailer — runs Looker Studio dashboards over a 4 TB sales fact table partitioned by order_date and clustered by store_id. Pre-BI Engine, peak-hour dashboard tile loads averaged 8 seconds; users complained about the regional sales heatmap that fanned out 50 simultaneous queries on Monday morning leadership reviews.

The data engineering team rolled out a four-step fix. First, they created two materialized views: daily sales by store and hourly sales by region, both refreshed every 10 minutes. Second, they created a 25 GiB BI Engine reservation in us-central1 co-located with the storage. Third, they declared the two materialized views and the calendar dimension as preferred tables. Fourth, they swapped exact COUNT(DISTINCT customer_id) to APPROX_COUNT_DISTINCT for the customer-count tile.

Post-rollout numbers: average tile load dropped from 8 seconds to 350 ms. The Monday-morning fan-out, which previously hit slot autoscaling and queued, now ran from BI Engine in under a second. The reservation cost roughly $1,200 per month — a fraction of what they would have paid in slot capacity to achieve the same latency through brute-force compute. The team kept their existing 100-slot annual reservation for ETL and ad-hoc analysis untouched.

Exam Tips for BigQuery BI Engine and Performance Acceleration

PDE questions on BigQuery BI Engine and Performance Acceleration cluster around four scenario shapes.

"Looker Studio is slow" scenarios. The right answer almost always mentions BI Engine reservation, not bigger slots. If the question constrains cost, layer materialized views first then a small BI Engine reservation. Distractors will offer "increase slot count" or "migrate to Bigtable" — both wrong for interactive BI on aggregated data.

"Predictable latency for executive dashboard" scenarios. Look for keywords like "sub-second", "concurrent users", "hot data". The answer combines BI Engine with preferred tables.

"Cost-optimised acceleration" scenarios. Query cache (free) + materialized views (cheap storage) come before BI Engine (paid memory). On-demand pricing for occasional analysts, BI Engine for interactive BI, slots for ETL — the three layers solve three problems.

"Why is my query not accelerated" scenarios. The answer involves checking bi_engine_statistics.bi_engine_mode, region match, supported SQL, and table type. Wildcard tables, external tables, and certain DML exclude BI Engine.

Memorise the 250 GiB max per project per region. Memorise that reservations are regional. Memorise that BI Engine and slot reservations are independent billing dimensions. These three facts answer at least one PDE question by themselves.

Frequently Asked Questions (FAQ)

Does BigQuery BI Engine and Performance Acceleration work with all SQL queries?

Since the BI Engine SQL Interface went GA in 2022, any standard SQL query against a supported table can be accelerated. However, queries with external tables, wildcard tables, certain window functions, and queries returning more than 1 GB to the client may fall out of FULL acceleration. Always check bi_engine_statistics.bi_engine_mode in the query plan.

How is BI Engine pricing different from slot pricing?

BI Engine bills per GiB of memory reserved per hour, 24/7, regardless of query volume. Slot pricing bills either per TiB scanned (on-demand) or per slot-hour committed (editions). The two are independent — a query fully accelerated by BI Engine consumes near-zero slots, while a query that falls back uses slots normally.

Can I use BI Engine with multi-region datasets?

Reservations are regional only — us-central1, europe-west1, etc. Tables in the US or EU multi-region locations will not be accelerated by a regional BI Engine reservation. Co-locate your tables and reservation in the same regional location for acceleration.

When should I use materialized views instead of BI Engine?

They solve different problems. Materialized views pre-compute and store aggregation results, reducing scanned bytes for any consumer. BI Engine caches data in memory for sub-second response. Use materialized views when the same aggregation pattern repeats across many queries; use BI Engine when interactive latency matters; use both for high-traffic dashboards over large bases.

What is the difference between APPROX_COUNT_DISTINCT and exact COUNT(DISTINCT)?

APPROX_COUNT_DISTINCT uses HyperLogLog++ to estimate distinct counts with roughly 1% error. It runs much faster, uses fewer slots, and stays within FULL BI Engine acceleration where exact COUNT(DISTINCT) often does not. Use the approximate version for dashboards, analytics, and trend reporting; use exact only for financial reconciliation or compliance reports requiring precise counts.

How do I monitor BI Engine usage and tune the reservation?

Query INFORMATION_SCHEMA.BI_ENGINE_STATISTICS_BY_PROJECT for per-query acceleration mode and reason codes. Query INFORMATION_SCHEMA.JOBS_BY_PROJECT for query patterns and bytes processed. Watch for rising PARTIAL or DISABLED rates — they indicate eviction pressure or unsupported queries. Cloud Monitoring also exposes BI Engine reservation utilisation metrics.

Does the query cache replace BI Engine?

Partially, for free. The 24-hour query cache returns identical query results at zero cost when the same query runs again. BI Engine accelerates queries that the cache misses — different filter values, different time ranges, different aggregations. Always enable cache first; reach for BI Engine when cache hit rate is too low to meet latency goals.

Related Topics

• BigQuery Data Modeling and Clustering — partitioning and clustering reduce scanned bytes before BI Engine ever loads them.
• BigQuery Slot Management and Reservations — slots and BI Engine are independent billing dimensions; both have a role.
• Looker vs Looker Studio Decision — choosing the BI tool affects how cleanly BI Engine acceleration applies.

Further Reading

• BigQuery BI Engine overview — official reference for reservations, supported SQL, and acceleration modes.
• Introduction to materialized views — official guide to view limitations, refresh semantics, and smart tuning.
• Query plans and timeline — official documentation for reading EXPLAIN output and bi_engine_statistics.
• Cached query results — official reference for query cache behaviour and constraints.