Sub-Second BigQuery Dashboards: BI Engine, Materialized Views, and Search Indexes

Most "BigQuery is slow" complaints are not really about BigQuery being slow. They are about a dashboard tile that takes nine seconds to paint, twenty times a day, for forty people. The query itself is fine. The serving layer underneath it is missing.

BigQuery gives you three distinct accelerators for that layer, and they solve genuinely different problems:

Accelerator	Best for	What it actually does	Typical win
BI Engine	Dashboard tiles hitting the same tables repeatedly	In-memory columnar cache in front of the query engine	Seconds to sub-second on repeat queries
Materialized views	Expensive aggregations recomputed constantly	Incrementally maintained, automatically matched pre-aggregate	10-100x less data scanned
Search indexes	Needle-in-haystack lookups on text and JSON	Inverted index over tokenised column values	Full scan to point lookup

Pick the wrong one and you pay for infrastructure that never gets used. This tutorial walks through choosing, implementing, and — most importantly — verifying each one.

Step 0: find out what is actually slow

Do not optimise from intuition. Start from INFORMATION_SCHEMA.JOBS, filtered to the service account your BI tool uses:

SELECT
  query_info.query_hashes.normalized_literals AS query_hash,
  ANY_VALUE(query HAVING MAX total_slot_ms) AS sample_query,
  COUNT(*) AS runs,
  ROUND(AVG(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) / 1000, 2) AS avg_seconds,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS total_tib_billed,
  COUNTIF(cache_hit) AS cache_hits,
  ROUND(SUM(total_slot_ms) / 1000 / 3600, 2) AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND user_email = 'looker-service-account@my-project.iam.gserviceaccount.com'
GROUP BY query_hash
ORDER BY runs * avg_seconds DESC
LIMIT 20;

Ordering by runs * avg_seconds ranks by total human waiting time, which is what your users are complaining about — not by the single slowest query, which is usually a one-off backfill nobody was watching.

Read the top of that list before choosing a tool:

• High run count, small-to-medium tables, low cache-hit ratio → BI Engine.
• Same GROUP BY shape repeated over a large fact table → materialized view.
• WHERE clauses with LIKE '%...%', SEARCH(), or JSON key hunts → search index.
• One giant query nobody repeats → none of the above; fix the query or the partitioning instead.

BI Engine: the cheapest win, when it applies

BI Engine is an in-memory analysis accelerator. You reserve GiB of memory in a location, and BigQuery transparently caches the columns your queries touch. No query rewrites, no new objects, no changes in your BI tool.

-- Create a 10 GiB BI Engine reservation in the US multi-region
CREATE BI_CAPACITY IF NOT EXISTS `my-project.region-us.default`
OPTIONS (
  size_gb = 10,
  preferred_tables = ['my-project.marts.fct_orders',
                      'my-project.marts.dim_customer']
);

Two things matter here.

Size it to the columns, not the tables. BI Engine caches only the columns referenced by your queries, in compressed form. A 2 TiB fact table where dashboards only touch six columns may need far less than you fear. Start small (a few GiB), measure the acceleration mode, then grow.

Use preferred_tables deliberately. Without it, BI Engine caches whatever is hottest, which may be an ad-hoc analyst's scratch table rather than your executive dashboard. Naming the tables you care about protects the workload you actually want fast.

Verifying BI Engine is being used

This is the step most teams skip, then wonder why nothing improved. Check the acceleration mode per job:

SELECT
  bi_engine_statistics.bi_engine_mode AS mode,
  bi_engine_statistics.bi_engine_reasons AS reasons,
  COUNT(*) AS jobs,
  ROUND(AVG(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 0) AS avg_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
  AND bi_engine_statistics IS NOT NULL
GROUP BY mode, reasons
ORDER BY jobs DESC;

FULL means the whole query ran in memory. PARTIAL means some stages did. DISABLED comes with a reason code, and the reasons are usually mundane and fixable: the reservation is too small, the query uses an unsupported construct, or the table is an external/federated table BI Engine will not cache. Fix the reason rather than doubling the reservation size.

When BI Engine will not help you

• Queries that scan enormous ranges each time (year-over-year reports across billions of rows) — the working set never fits.
• Every query is unique, so nothing gets reused.
• Your dashboards hit external tables, or complex non-cacheable constructs.

In those cases, move down the list.

Materialized views: pre-aggregate once, match automatically

A materialized view stores the result of an aggregation and keeps it incrementally fresh as the base table changes. The magic is automatic matching: BigQuery can rewrite a query against the base table to read the MV instead, even if the query never mentions the view.

CREATE MATERIALIZED VIEW `my-project.marts.mv_orders_daily`
PARTITION BY order_date
CLUSTER BY country
OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 30,
  max_staleness = INTERVAL 1 HOUR
)
AS
SELECT
  DATE(order_ts) AS order_date,
  country,
  channel,
  COUNT(*) AS orders,
  SUM(net_amount) AS revenue,
  APPROX_COUNT_DISTINCT(customer_id) AS customers
FROM `my-project.marts.fct_orders`
GROUP BY order_date, country, channel;

Now a dashboard query written against the base table:

SELECT country, SUM(net_amount) AS revenue
FROM `my-project.marts.fct_orders`
WHERE DATE(order_ts) BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY country;

...can be served from the materialized view. Confirm it by checking the query plan or the mv_refresh/materialized_view metadata; the fastest tell is a dramatic drop in total_bytes_billed for the same result.

Rules that decide whether an MV earns its keep

1. Aggregate, do not copy. An MV that reduces 500 million rows to 50,000 is transformative. An MV that returns nearly as many rows as the base table just doubles your storage bill.
2. Watch the write pattern. Incremental refresh is cheap for append-mostly tables. On a table rewritten wholesale each night, every refresh is a full recompute — you are paying twice for the same aggregation.
3. Use max_staleness for cost control. With max_staleness set, queries are served from the stored result as long as it is within the staleness window, without triggering a refresh read of the base table. That is the difference between a predictable serving layer and a surprise bill.
4. Know the restrictions before you design around one. Outer joins, UNION ALL, window functions, and some non-aggregate constructs are unsupported or restricted. If your logic does not fit, a scheduled query writing to a plain summary table is a perfectly respectable alternative — you simply lose automatic matching and must point the dashboard at it explicitly.

Monitoring refresh health

SELECT
  table_name,
  last_refresh_time,
  refresh_watermark,
  last_refresh_status.message AS last_error
FROM `my-project.marts`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;

A materialized view that has silently stopped refreshing — because the base table schema changed, say — quietly serves stale numbers. Put this query in your daily data-quality checks, not in someone's memory.

Search indexes: for needles, not aggregates

Search indexes solve a completely different problem: finding a handful of rows by a token buried in text, JSON, or a high-cardinality identifier column. Without one, WHERE payload LIKE '%ORD-99321%' scans the entire column.

CREATE SEARCH INDEX idx_events_all
ON `my-project.raw.events` (ALL COLUMNS)
OPTIONS (analyzer = 'LOG_ANALYZER');

Then query with SEARCH(), which is what the index accelerates:

SELECT event_ts, service, payload
FROM `my-project.raw.events`
WHERE SEARCH(payload, 'ORD-99321')
  AND event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY event_ts DESC
LIMIT 100;

Practical notes from real engagements:

• Choose the analyzer on purpose. LOG_ANALYZER splits on delimiters common in logs and identifiers; NO_OP_ANALYZER matches whole values exactly, which is what you want for opaque IDs; PATTERN_ANALYZER takes a custom regex. The wrong analyzer produces an index that is technically present and practically useless.
• Still filter the partition. A search index does not replace partition pruning — combine both, as above.
• Index storage is not free, and indexes over ALL COLUMNS on a wide table can be surprisingly large. Check INFORMATION_SCHEMA.SEARCH_INDEXES for index_status and storage size, and index specific columns when you can name them.
• Indexes can fall out of coverage on very high-churn tables; index_status tells you when management is lagging behind ingestion.

Putting it together: a layered decision

For a typical BI estate, we apply these in order, cheapest first:

1. Fix the model. Partition and cluster the fact tables correctly. No accelerator repairs a query that scans a year because someone wrapped the partition column in a function.
2. Add materialized views for the two or three aggregation shapes that dominate your INFORMATION_SCHEMA.JOBS list. This cuts bytes billed — a real, recurring cost saving on on-demand pricing.
3. Turn on BI Engine sized to the columns those dashboards touch, and verify bi_engine_mode = 'FULL'. This cuts latency.
4. Add search indexes only where the access pattern is genuinely a lookup, most often on log and event tables backing support or ops tooling.
5. Re-run the step 0 query a week later. If total waiting time has not moved, you optimised the wrong thing — and now you know it, cheaply.

A note on cost accounting

These three accelerators bill differently, and confusing them makes budget conversations messy. BI Engine is a flat reservation charge for the memory you hold. Materialized views cost storage plus the compute of their refresh jobs, offset against far lower query-time scanning. Search indexes cost index storage, and index management is free below a per-organisation threshold and billed above it. Model the offset, not just the new line item: an MV that adds a modest storage cost while removing tens of TiB of monthly scanning is an obvious win, and you should be able to show it in a spreadsheet before you build it.

Where teams get stuck

The pattern we see most often is a team that enabled BI Engine, saw no improvement, and concluded the feature does not work. Nine times out of ten bi_engine_mode was DISABLED for a stated reason nobody read. The second most common pattern is a materialized view carefully built on a table that is truncated and reloaded nightly, so every refresh is a full recompute and the "optimisation" doubled the bill.

If your dashboards are slow and you would rather have someone measure before they prescribe, get in touch — our BigQuery performance optimization and custom dashboard development teams do exactly this work, starting from your own job history rather than a generic checklist.