Gowtham Potureddi

Posted on Jun 29

BigQuery Materialized Views + BI Engine: Sub-Second Dashboards on Petabytes

#python #sql #dataengineering #interview

bigquery materialized view is the single biggest dashboard-performance lever a BigQuery team has in 2026 — and the one most teams underuse because they treat bigquery mv as "a faster table" instead of as a declarative pre-aggregate that the planner can rewrite a base-table query into transparently. Pair the MV with bigquery bi engine — the in-memory column-store cache that sits in front of BigQuery — and an 8-second dashboard query on a 12 TB fact table collapses to a sub-50-millisecond response. The DDL fits on six lines; the operational reasoning behind those six lines is what senior interviewers actually probe.

This guide is the senior-DE explainer you wished existed the first time an interviewer asked "how would you make a Tableau dashboard hit bigquery sub-second on a 50-billion-row events table?" or "walk me through the four layers of bigquery caching BigQuery uses and which one you'd reach for first." It walks through the materialized view DDL surface (aggregate awareness, smart MVs, max_staleness, refresh credits), the BI Engine reservation model (eligibility, bi engine pricing, capacity sizing, regional placement), the four-layer cake that combines partitioning, clustering, MV, and BI Engine into a single sub-second stack, and the cost / decision matrix senior engineers use to pick when each layer pays back its overhead. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works. Improving bigquery dashboard performance is rarely about a single setting; it is about stacking these four layers in the right order, with bigquery aggregate awareness as the load-bearing middle layer.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen the cohort-aggregate axis with the aggregation problem set →, and pressure-test the dashboard-rewrite muscle on query optimization problems →.

On this page

Why MV + BI Engine are the BigQuery dashboard combo
Materialized views — declarative pre-aggregates
BI Engine — in-memory acceleration layer
MV + BI Engine + partitioning + clustering — the four-layer cake
Cost / decision matrix
Cheat sheet — MV + BI Engine recipes
Frequently asked questions
Practice on PipeCode

1. Why MV + BI Engine are the BigQuery dashboard combo

Dashboards on raw fact tables scan the world; pre-aggregated MV + in-memory BI Engine collapses the scan to a memory lookup

The one-sentence invariant: a BigQuery dashboard hitting a multi-TB fact table directly is paying for a full partition scan on every refresh, while the same dashboard hitting a materialized view that BigQuery has pre-aggregated — and that BI Engine has loaded into RAM — is paying for a memory lookup. Every other piece of bigquery dashboard performance engineering (partition pruning, clustering, slot sizing, query caching) is either feeding that stack or running underneath it. Once you internalise "MV pre-aggregates the cohort, BI Engine caches the result," the entire BigQuery acceleration interview surface collapses to a sequence of consequences from that pair.

The four "must-answer" axes interviewers probe.

Aggregate awareness. Does the BigQuery query planner rewrite a base-table SELECT COUNT(*) FROM events GROUP BY day into a scan against your MV automatically? Yes — without any change to the dashboard SQL. The MV is declarative; the planner picks it. The interview answer is "BigQuery's bigquery aggregate awareness is built into the optimizer; you don't SELECT FROM the_mv, you SELECT FROM the_base_table and the planner substitutes."
Refresh. When does the MV refresh? Default is automatic background refresh within minutes; the team can opt into stricter freshness with max_staleness (allows a query to fall back to base + MV merge when the MV is stale), or looser freshness for cheaper refresh credits. The interview answer is "automatic by default, configurable via max_staleness, billable via on-demand bytes or reservation slots — and stale MVs do not silently return wrong answers."
In-memory cache. What does BI Engine actually cache? Hot columns from hot tables (including MVs) in a columnar in-memory store, automatically populated by hot queries — no DDL, no manual CACHE TABLE. The interview answer is "BI Engine is a reservation-based capacity buy; you pay for GB of in-memory cache per region, and BigQuery decides which columns to pin based on observed query patterns."
Cost. Where does the money go? MV refresh credits (bytes scanned in the refresh, or slot-time in a reservation), BI Engine capacity (GB-hour), reduced base-table scan cost (the savings). The interview answer is "you trade predictable MV refresh and BI Engine capacity for unpredictable dashboard scan bills, and the break-even comes around 30–50 dashboard refreshes per day on the same cohort."

The 2026 reality — what changed since 2022.

Smart MVs. Until 2022, BigQuery MVs supported only GROUP BY with a small subset of aggregate functions. In 2026, smart MVs support JOINs (with at least one base table), more aggregate functions (APPROX_COUNT_DISTINCT, HLL_COUNT.MERGE), and a growing list of window-function patterns. Earlier blog posts that say "MVs can't join" are out of date.
BI Engine unified pricing. Pre-2023, BI Engine had two SKUs (capacity-based and per-query). In 2026, the unified pricing is per-GB-hour of reservation in a region, billed continuously while the reservation exists. There is no longer a per-query SKU.
max_staleness clause. Lets a base-table query satisfy itself from the MV even if the MV is stale, by merging the MV with the un-refreshed delta from the base table. Trades a small CPU cost for skipping a full MV refresh on every freshness-sensitive query.
BI Engine + tables, not just MVs. BI Engine can now cache any hot table's columns — base tables, MVs, or views materialised into temp results. The cache decision is made by BigQuery's planner based on observed query patterns, not by the team running CACHE TABLE.

What interviewers listen for.

Do you say "MV is declarative; the planner rewrites base-table queries" in the first sentence? — senior signal.
Do you mention "BI Engine is a reservation — you buy GB-hours, the planner picks what to pin" unprompted? — senior signal.
Do you describe max_staleness as "the freshness vs refresh-cost knob" and not as "the refresh interval"? — senior signal.
Do you push back on "just use BI Engine" with "BI Engine accelerates whatever the planner can serve — if the cohort isn't pre-aggregated, BI Engine still scans the base columns"? — senior signal.

What interviewers do not want to hear.

"Set up a cron job to refresh the MV every 5 minutes." BigQuery refreshes MVs automatically; the cron is a relic from Postgres.
"Enable BI Engine on the dashboard." BI Engine is a reservation on a region, not a per-dashboard toggle.
"The MV is just a faster table." The MV is a materialised query result with aggregate-awareness rewrites; the planner — not the dashboard SQL — picks it.

Worked example — same dashboard, three execution shapes

Detailed explanation. A daily-revenue-by-country Looker dashboard runs over a 50-billion-row events fact table. Run 1 hits the raw table with no acceleration. Run 2 has a materialized view in place. Run 3 also has BI Engine reservation. The three runs surface what each layer contributes to bigquery dashboard performance.

Question. Show the same dashboard SQL run in three configurations: (a) raw events table, (b) MV in place but no BI Engine, (c) MV + BI Engine. Highlight where the scan happens, what changes in the execution plan, and the latency and cost numbers.

Input.

Run	Acceleration	Bytes scanned	Latency	Slot-time
(a)	none	12 TB	8 s	450 slot-s
(b)	MV only	8 GB	200 ms	4 slot-s
(c)	MV + BI Engine	0 (in-memory)	50 ms	0 (cached)

Code.

-- The dashboard query stays the SAME across all three runs
SELECT
  country,
  DATE(event_ts) AS day,
  SUM(revenue) AS revenue
FROM `proj.analytics.events`           -- base table, NOT the MV
WHERE event_ts BETWEEN '2026-06-15' AND '2026-06-21'
GROUP BY country, day
ORDER BY day, country;

-- Run (b) — add a materialized view
CREATE MATERIALIZED VIEW `proj.analytics.events_daily_country`
PARTITION BY day
CLUSTER BY country
AS
SELECT
  country,
  DATE(event_ts) AS day,
  SUM(revenue) AS revenue,
  COUNT(*) AS n_events
FROM `proj.analytics.events`
GROUP BY country, day;

-- Run (c) — add a BI Engine reservation in the same region
-- (Console / Terraform; not SQL DDL)
-- bigquery.reservation.bi_reservation.size = 8 GB, location = US

Step-by-step explanation.

Run (a) hits the raw events fact table directly. BigQuery prunes partitions to the 7 requested days, but each day's partition is ~1.7 TB. Total scan: ~12 TB across 450 slot-seconds. The dashboard's "refresh" button blocks for 8 seconds.
Run (b) keeps the same dashboard SQL. After the MV is created, the BigQuery planner notices that events_daily_country already contains the aggregate over country, day and rewrites the query to scan the MV instead. The MV is ~8 GB across all partitions; scan time drops to 200 ms.
Run (c) adds an 8 GB BI Engine reservation in the same region. BigQuery's planner sees the MV is hot and pins its columns into the in-memory column store. The next dashboard refresh hits BI Engine for a memory lookup — 50 ms, no bytes-scanned charge.
Across the three runs, the dashboard SQL did not change. What changed is what BigQuery did underneath — MV substitution at the planner, BI Engine pinning at the cache layer. This is the senior signal: the acceleration is declarative, not imperative.
The cost shape also flipped. Run (a) is pay-per-byte: 12 TB × $5/TB = $60 per refresh. Run (b) is 8 GB × $5/TB ≈ $0.04 per refresh, plus the MV refresh cost (a one-time-per-update scan of the new data). Run (c) is $0 per refresh from BI Engine, plus the reservation cost (~$30/GB-month for 8 GB ≈ $240/month).

Output.

Run	Bytes scanned per refresh	Latency p99	Refresh cost	Reservation cost	30-refresh/day cost
(a)	12 TB	8 s	$60	$0	$1,800/day
(b)	8 GB	200 ms	$0.04	$0 + MV refresh	~$1/day + MV
(c)	0	50 ms	$0	~$8/day (8 GB BIE)	$8/day + MV

Rule of thumb. If a dashboard refreshes more than 5 times per day on a >100 GB cohort, the MV pays back the refresh credits inside a week. If you have ≥3 dashboards on the same MV, the BI Engine reservation pays back even faster.

Worked example — what counts as "MV-eligible" SQL

Detailed explanation. Smart MVs support more SQL surface than first-generation MVs, but there are still patterns that are not eligible. Knowing which patterns are MV-eligible is a load-bearing senior skill — write the wrong SQL and the planner silently falls back to a base-table scan, with no error.

Question. Of the four queries below, which can be served by a materialized view in 2026? Mark each one and explain why or why not.

Input.

Query	Pattern
(A)	`SUM(revenue) GROUP BY country, day`
(B)	`APPROX_COUNT_DISTINCT(user_id) GROUP BY day`
(C)	`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts)`
(D)	`JOIN orders + payments ON order_id GROUP BY country`

Code.

-- (A) — classic GROUP BY aggregation: MV-eligible
CREATE MATERIALIZED VIEW mv_a AS
SELECT country, DATE(event_ts) AS day, SUM(revenue) AS rev
FROM events GROUP BY country, day;

-- (B) — APPROX_COUNT_DISTINCT via HLL_COUNT — MV-eligible (smart MV)
CREATE MATERIALIZED VIEW mv_b AS
SELECT DATE(event_ts) AS day,
       HLL_COUNT.INIT(user_id) AS users_hll
FROM events GROUP BY day;
-- The dashboard then does HLL_COUNT.EXTRACT(HLL_COUNT.MERGE(users_hll))

-- (C) — ROW_NUMBER() window: NOT MV-eligible
-- BigQuery does not refresh windowed MVs incrementally in 2026
-- (workaround: materialise a table via scheduled query)

-- (D) — JOIN of two base tables: MV-eligible (smart MV)
CREATE MATERIALIZED VIEW mv_d AS
SELECT o.country, SUM(p.amount) AS paid
FROM orders o JOIN payments p ON o.order_id = p.order_id
GROUP BY o.country;

Step-by-step explanation.

(A) is the canonical MV — GROUP BY plus a SUM/COUNT/MIN/MAX. Eligible since BigQuery's first-generation MVs in 2020. The planner rewrites any base-table query matching this aggregation shape to scan the MV instead.
(B) uses APPROX_COUNT_DISTINCT via HLL_COUNT.INIT. The MV stores the HyperLogLog sketch per day; the dashboard calls HLL_COUNT.MERGE + EXTRACT to roll up daily sketches to weekly/monthly without re-scanning the base table. Eligible as a smart MV.
(C) uses ROW_NUMBER(), a windowed function. In 2026, windowed MVs are not incrementally refreshable in BigQuery, so they are not MV-eligible. The workaround is a scheduled query that writes a result table — gives the same shape but loses the planner's aggregate awareness.
(D) joins orders and payments then aggregates. Smart MVs in 2026 support joins where at least one side is a base table, with restrictions (no RIGHT JOIN, no correlated subqueries). The planner can rewrite a base-table-style dashboard query into the MV scan.
The danger pattern: a team writes (C), creates an MV, sees no error at DDL time, then wonders why the dashboard still costs $60 per refresh. The MV exists; the planner just doesn't use it. Always run EXPLAIN or check the query's query_plan.materializedViewUsed to confirm the substitution.

Output.

Query	MV-eligible 2026?	Why
(A) `GROUP BY + SUM`	yes	first-gen MV
(B) `HLL_COUNT.INIT`	yes	smart MV, sketch aggregate
(C) `ROW_NUMBER()`	no	windowed, not incrementally refreshable
(D) two-table `JOIN + GROUP BY`	yes	smart MV (one base table on each side)

Rule of thumb. If your dashboard query is GROUP BY + sum/count/avg/min/max or HLL_COUNT.INIT it is MV-eligible. Anything windowed or with multiple-level subqueries needs a scheduled-query result table instead.

Worked example — verifying the planner used your MV

Detailed explanation. A common production gotcha — you create an MV, the dashboard is still slow, and you suspect the planner is scanning the base table anyway. There are three ways to confirm: EXPLAIN, the query's job info, and the INFORMATION_SCHEMA.JOBS history. Senior engineers automate one of these into a CI check that fires when a dashboard regresses to base-table scans.

Question. Given a slow dashboard query, write the diagnostic SQL that confirms whether BigQuery's planner used your MV or fell back to the base table. Show how to detect the regression early.

Input.

Symptom	Possible cause
Dashboard latency 8 s instead of 200 ms	planner not using MV
Bytes-scanned per refresh = 12 TB	planner not using MV
MV exists but stale	refresh credit issue

Code.

-- 1) EXPLAIN the dashboard query and read the plan
EXPLAIN
SELECT country, DATE(event_ts) AS day, SUM(revenue)
FROM `proj.analytics.events`
WHERE event_ts BETWEEN '2026-06-15' AND '2026-06-21'
GROUP BY country, day;
-- Look for "materializedViewUsed": "proj.analytics.events_daily_country" in the plan

-- 2) Run the query, then inspect the job's metadata
SELECT
  job_id,
  total_bytes_processed,
  total_slot_ms,
  query_info.referenced_tables,
  query_info.optimization_details
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND query LIKE '%events%revenue%'
ORDER BY creation_time DESC
LIMIT 5;

-- 3) Check MV freshness explicitly
SELECT
  table_name,
  refresh_watermark,
  last_refresh_time,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_refresh_time, MINUTE) AS minutes_stale
FROM `proj.analytics.INFORMATION_SCHEMA.MATERIALIZED_VIEWS`;

Step-by-step explanation.

The EXPLAIN output's query_plan block contains a materializedViewUsed field naming the MV the planner substituted in. If absent, the planner did not use any MV.
Running the query (or sampling from INFORMATION_SCHEMA.JOBS_BY_PROJECT) shows the actual bytes-scanned. If you see a multi-TB number where the MV should have been a few GB, you know the planner missed.
The MV freshness check shows when the MV was last refreshed and whether it is currently stale. A stale MV without max_staleness set will not be used by the planner for queries that need fresh data — they fall back to the base table silently.
The senior pattern: instrument a daily check that picks the top 10 dashboard queries by frequency and confirms materializedViewUsed is populated. If any drops to null, alert.
Three common reasons the planner skips the MV: (a) query filters don't align with MV partition/cluster keys, (b) MV is stale and max_staleness is not set or is too tight, (c) query uses a function or pattern the smart-MV rewriter doesn't recognise.

Output.

Diagnostic	Healthy reading	Unhealthy reading
`EXPLAIN.materializedViewUsed`	`proj.analytics.events_daily_country`	null
`total_bytes_processed`	~8 GB	~12 TB
`minutes_stale`	< `max_staleness`	> `max_staleness`
`query_info.optimization_details`	`MV_REWRITE_APPLIED`	not present

Rule of thumb. Never trust that the MV is being used because you created it — verify with EXPLAIN once at deploy time and again on a daily CI check. The silent fallback to base table is the #1 cause of "I made an MV and nothing got faster."

Senior interview question on the MV + BI Engine decision

A senior interviewer often opens with: "You inherit a Tableau dashboard hitting a 12 TB BigQuery events table that costs $60 per refresh and runs 30 times a day. Walk me through how you'd decide between adding a materialized view, adding BI Engine, or both. What questions do you ask in what order, and what answer to each pushes you to one layer over another?"

Solution Using a 4-question MV / BI Engine framework

Decision framework — when to add MV, BI Engine, or both
=======================================================

Q1. Is the dashboard cohort stable?
   - Yes (same GROUP BY shape, same filters)        → MV pays
   - No (ad-hoc, every refresh picks new dims)       → MV cannot pre-aggregate

Q2. How often does the dashboard refresh?
   - > 5x per day on the same cohort                 → MV refresh credits pay back
   - < 1x per day                                    → on-demand scan may be cheaper

Q3. How many dashboards share the same cohort?
   - 1 dashboard                                     → MV alone may be enough
   - 3+ dashboards on related cohorts                → BI Engine reservation amortises

Q4. What is the latency target?
   - 100ms–1s OK                                     → MV alone hits it
   - Sub-100ms required (interactive Tableau)        → MV + BI Engine

Step-by-step trace.

Dashboard	Q1 stable?	Q2 refresh/day	Q3 shared?	Q4 latency	Pick
Daily revenue by country	yes	30	1	200 ms OK	MV only
Tableau exec scorecard	yes	120	3 dashboards	< 100 ms	MV + BI Engine
Ad-hoc data science	no	2	1	any	neither
Hourly KPI flash	yes	24	5 dashboards	< 100 ms	MV + BI Engine

After the 4-question pass, the layering choice is unambiguous. The remaining 5% — where both MV and BI Engine are borderline — defaults to "add the MV first, measure, add BI Engine if p99 still misses the budget."

Output:

Layer	When it pays back
MV alone	stable cohort, > 5 refreshes/day, latency 100ms–1s
BI Engine alone	many hot tables/MVs, sub-100ms p99 required
MV + BI Engine	high-QPS executive dashboards on stable cohorts
Neither	ad-hoc data-science exploration, ever-changing dimensions

Why this works — concept by concept:

Stable cohort is the load-bearing precondition — MVs pre-compute a fixed GROUP BY. If the dashboard filters or grouping changes every refresh, the MV can't pre-aggregate, and you're paying refresh credits for nothing.
Refresh frequency drives MV break-even — refreshing the MV costs a one-time scan of the new partition delta; that cost is amortised over the number of dashboard refreshes between MV refreshes. Above ~5/day, MV is almost always net cheaper.
BI Engine amortises across dashboards — the reservation cost is fixed per GB-hour. With 1 dashboard hitting the cache, the cost is per-dashboard; with 5 dashboards on the same hot columns, the cost-per-dashboard drops by 5x.
Latency target gates the BI Engine decision — MV alone reliably hits 100ms–1s on multi-GB MVs. Sub-100ms p99 with no slot contention requires the in-memory layer.
Cost — MV refresh is O(delta bytes) per refresh tick; BI Engine reservation is O(GB-hour) of capacity; raw-table scan is O(TB scanned per refresh). The crossover point is where the dashboard refresh frequency × scan cost > MV refresh + BI Engine reservation.

SQL
Topic — sql
BigQuery dashboard SQL drills

Practice →

SQL Topic — aggregation Aggregation problems (SQL)

Practice →

2. Materialized views — declarative pre-aggregates

`bigquery mv` is a stored aggregate the planner rewrites your base-table query into — six lines of DDL, automatic refresh, aggregate-aware substitution

The mental model in one line: a BigQuery materialized view is a stored SELECT … GROUP BY … result, refreshed automatically as the base table changes, that the planner transparently rewrites your dashboard's base-table query into when the aggregation shape matches. Once you say that out loud, every bigquery materialized view interview question becomes a deduction from "declarative pre-aggregate plus planner rewrite."

The six-line DDL.

CREATE MATERIALIZED VIEW project.dataset.mv_name
PARTITION BY <expr>
CLUSTER BY <cols>
OPTIONS(max_staleness = INTERVAL 30 MINUTE, enable_refresh = true)
AS
SELECT … FROM base_table GROUP BY …;

That is the entire surface area. The MV inherits partition and cluster keys from the SELECT (or from the DDL clause); refresh runs in the background; the planner picks the MV when it matches.

Aggregate awareness — the planner rewrite rule.

The planner compares each incoming query's logical plan to the catalogue of MVs on the referenced base table.
If the query's GROUP BY keys are a subset (or exact match) of the MV's GROUP BY keys, and the aggregate function is one the MV stored, the planner substitutes the MV.
The substitution is transparent — the dashboard SQL is unchanged. You do not SELECT … FROM the_mv; you continue to SELECT … FROM the_base_table.
This is the difference between an MV and "a table you populate yourself." A table requires the dashboard to know it exists; an MV is silently picked by the optimizer.

Smart MVs — what's new since 2023.

Joins. Smart MVs can include a JOIN between two base tables (one must be referenced as the "primary" base). Useful for star-schema rollups.
Window-function patterns. Some windowed patterns are eligible (incremental running totals via HLL_COUNT sketch merging). Pure ROW_NUMBER() is still not eligible.
More aggregate functions. APPROX_COUNT_DISTINCT, HLL_COUNT.INIT/MERGE/EXTRACT, APPROX_QUANTILES. These let you build approximate-distinct dashboards that don't blow up on cardinality.
Nested record support. MVs can flatten or aggregate over ARRAY<STRUCT> columns — useful for event-style fact tables with arrays of items.

Refresh modes.

Automatic refresh (default). BigQuery refreshes the MV in the background after base-table writes, typically within minutes. No DDL needed; you can disable via OPTIONS(enable_refresh = false).
Manual refresh. CALL BQ.REFRESH_MATERIALIZED_VIEW('project.dataset.mv_name'). Useful in CI/CD pipelines that want to refresh the MV before a known consumer reads it.
max_staleness. Sets a freshness tolerance. If the MV is fresher than max_staleness, the planner uses it. If staler, the planner merges the MV with the un-refreshed delta from the base table (still cheaper than a full base scan).

Refresh credit model — what you actually pay for.

On-demand pricing. The refresh scans the base-table partitions that changed since the last refresh, plus a small overhead. Billed at $5/TB scanned.
Reservation pricing. The refresh consumes slot-time from your BigQuery reservation. Billed as part of your committed slot pool.
The delta is small if base writes are small. Incremental refresh means you don't re-scan the whole base table; you scan the new partition(s).
Cost trap. A team enables MV on a table that's appended in tiny micro-batches all day → MV refreshes hundreds of times → refresh credits balloon. Fix: set max_staleness to batch the refreshes.

Co-design with partitioning + clustering.

The MV's PARTITION BY should match the dashboard's date filter — otherwise the planner cannot prune partitions on the MV scan.
The MV's CLUSTER BY should match the dashboard's high-cardinality filter columns — so the MV scan does block-level pruning.
A well-designed MV is itself a partitioned + clustered table; the same physical-layout rules apply.

Common interview probes on MV.

"How does the planner know to use my MV?" — aggregate awareness: the query's logical plan is compared to the catalogue of MVs on the referenced base table.
"What happens when the MV is stale?" — without max_staleness, the planner does not use it; with max_staleness, the planner merges MV + base delta.
"Can I SELECT from the MV directly?" — yes, but you almost never should. The whole point is for the dashboard to keep selecting from the base table and let the planner substitute.
"What's the difference between a smart MV and a scheduled query?" — the MV is declarative + planner-rewritten; the scheduled query is imperative + the dashboard has to know the result table's name.

Worked example — daily-revenue MV with planner rewrite

Detailed explanation. The canonical interview MV. A 12 TB events table, daily revenue dashboard, latency budget 250 ms. Show the DDL, the planner's rewrite of the base-table query, and the before/after performance.

Question. Build a materialized view that pre-aggregates events to daily revenue per country. Show the DDL, the dashboard SQL (which should NOT change), and the planner output that proves the MV is being used.

Input.

Table	Rows	Size	Daily delta
`proj.analytics.events`	50 B	12 TB	~1.7 TB / day

Code.

-- DDL — six lines, declarative
CREATE MATERIALIZED VIEW `proj.analytics.events_daily_country`
PARTITION BY day
CLUSTER BY country
OPTIONS(
  max_staleness = INTERVAL 30 MINUTE,
  enable_refresh = true
)
AS
SELECT
  country,
  DATE(event_ts) AS day,
  SUM(revenue) AS revenue,
  COUNT(*) AS n_events
FROM `proj.analytics.events`
GROUP BY country, day;

-- Dashboard SQL — unchanged from before the MV existed
SELECT
  country,
  day,
  revenue
FROM (
  SELECT
    country,
    DATE(event_ts) AS day,
    SUM(revenue) AS revenue
  FROM `proj.analytics.events`
  WHERE event_ts BETWEEN '2026-06-15' AND '2026-06-21'
  GROUP BY country, day
)
ORDER BY day, country;

-- Verify the planner used the MV
EXPLAIN
SELECT country, DATE(event_ts) AS day, SUM(revenue)
FROM `proj.analytics.events`
WHERE event_ts BETWEEN '2026-06-15' AND '2026-06-21'
GROUP BY country, day;
-- query_plan.materializedViewUsed = "proj.analytics.events_daily_country"

Step-by-step explanation.

The DDL declares an MV partitioned by day and clustered by country. The MV stores (country, day, revenue, n_events) — about 250 countries × 365 days × ~30 bytes = ~3 MB per year. The MV is roughly 4 orders of magnitude smaller than the base table.
enable_refresh = true (the default) means BigQuery refreshes the MV in the background as the base table grows. The first refresh scans the full base table (~12 TB, billed once); subsequent refreshes scan only the new partitions (~1.7 TB per day).
max_staleness = INTERVAL 30 MINUTE allows the planner to use the MV even if the latest refresh tick was up to 30 minutes ago. For queries that need fresher data, the planner merges the MV with the un-refreshed base delta.
The dashboard SQL is identical to what it was before the MV existed. The planner compares the query's GROUP BY country, DATE(event_ts) to the MV's stored aggregate and substitutes — no dashboard change.
The EXPLAIN output's query_plan.materializedViewUsed field names the MV. If this field is populated, the substitution happened; if null, you're scanning the base table and need to investigate.

Output (before / after performance).

Metric	Before MV	After MV
Bytes scanned	12 TB	8 GB
Latency p99	8 s	200 ms
Slot-time	450 slot-s	4 slot-s
Cost per refresh	$60	$0.04
MV refresh cost	n/a	~$8/day (one daily scan)

Rule of thumb. For any dashboard hitting > 100 GB and refreshing > 5×/day, the MV pays back the refresh credits within a week. Pick max_staleness matching the freshest acceptable view; the larger the tolerance, the cheaper the refresh.

Worked example — `max_staleness` and the freshness vs cost knob

Detailed explanation. The max_staleness clause is the single most misunderstood MV setting. Senior interviewers love it because the right answer is "freshness vs cost trade-off, not refresh interval." Smaller max_staleness = fresher MV = more frequent refresh = higher refresh credits. Larger max_staleness = staler MV = the planner may still use it by merging with a base delta.

Question. A team has an MV with max_staleness = INTERVAL 10 MINUTE. The dashboard refreshes every 2 minutes. Show what happens at each refresh tick. Then show what changes if max_staleness becomes INTERVAL 60 MINUTE.

Input.

Setting	Value
MV refresh interval (effective)	every 5 min (BigQuery's heuristic)
`max_staleness`	10 MINUTE
Dashboard refresh	every 2 min

Code.

-- Option A — tight freshness
CREATE OR REPLACE MATERIALIZED VIEW mv_orders_daily
OPTIONS(max_staleness = INTERVAL 10 MINUTE, enable_refresh = true)
AS
SELECT DATE(ts) AS day, SUM(amount) AS amt
FROM orders GROUP BY day;

-- Option B — relaxed freshness; cheaper refresh, planner falls back to merge
CREATE OR REPLACE MATERIALIZED VIEW mv_orders_daily
OPTIONS(max_staleness = INTERVAL 60 MINUTE, enable_refresh = true)
AS
SELECT DATE(ts) AS day, SUM(amount) AS amt
FROM orders GROUP BY day;

Step-by-step explanation.

With max_staleness = 10 MINUTE, BigQuery refreshes the MV whenever the staleness would exceed 10 minutes. For a heavily-written table, the refresh ticks every few minutes. Each refresh scans the new base-table partitions and updates the MV.
At each dashboard refresh tick (every 2 min), the planner checks the MV's refresh_watermark. If it's within 10 minutes of CURRENT_TIMESTAMP(), the MV is used directly. If older, the planner refreshes-then-uses or falls back to base + delta.
With max_staleness = 60 MINUTE, the MV is allowed to be up to an hour stale. BigQuery refreshes it less frequently — saving refresh credits — but when the dashboard hits an old MV, the planner merges the MV with the un-refreshed base delta on the fly. This costs less than a full base scan, more than a pure MV scan.
The trade-off: tight max_staleness = predictable MV freshness but higher refresh-credit bill. Loose max_staleness = lower refresh bill but per-query CPU cost to merge the delta.
For executive dashboards where freshness is the headline ("yesterday's revenue must be visible by 8 AM"), use a tight max_staleness. For monitoring dashboards where freshness is secondary, use a loose one and let the per-query merge cost amortise across far fewer refreshes.

Output.

Setting	Refresh freq	Dashboard latency	Refresh cost / day	Merge cost / query
`max_staleness = 10 MIN`	~every 5 min	200 ms	$40 (high)	0
`max_staleness = 60 MIN`	~hourly	200–400 ms	$5 (low)	small merge
`max_staleness` not set	only on demand	200 ms only if fresh; else base scan	$0	n/a

Rule of thumb. Set max_staleness to the freshest the dashboard actually needs, not the freshest you can imagine. Most dashboards do not care if data is 5 minutes vs 30 minutes stale; the credit savings on a wider window are substantial.

Worked example — smart MV with a join + HLL_COUNT

Detailed explanation. A 2026-era MV that earlier blog posts say "is impossible." The smart MV joins events with a users dimension, then pre-aggregates daily users per country using HLL sketches so the dashboard can roll up to weekly or monthly without scanning the base table.

Question. Build a smart MV that joins events to users and pre-aggregates daily unique users per country using HLL. Show how the dashboard rolls the sketch up to weekly.

Input.

Source	Rows	Used columns
`events`	50 B	`user_id`, `event_ts`
`users`	8 M	`user_id`, `country`

Code.

-- Smart MV — join + HLL sketch
CREATE MATERIALIZED VIEW `proj.analytics.events_users_daily_country_hll`
PARTITION BY day
CLUSTER BY country
OPTIONS(max_staleness = INTERVAL 30 MINUTE)
AS
SELECT
  u.country,
  DATE(e.event_ts) AS day,
  HLL_COUNT.INIT(e.user_id) AS users_hll
FROM `proj.analytics.events` e
JOIN `proj.analytics.users` u
  ON e.user_id = u.user_id
GROUP BY u.country, day;

-- Dashboard query — weekly unique users by country
SELECT
  country,
  DATE_TRUNC(day, WEEK) AS week,
  HLL_COUNT.EXTRACT(HLL_COUNT.MERGE(users_hll)) AS unique_users
FROM `proj.analytics.events_users_daily_country_hll`
WHERE day BETWEEN '2026-05-01' AND '2026-06-30'
GROUP BY country, week
ORDER BY week, country;

Step-by-step explanation.

The MV joins events with users on user_id. Smart MVs in 2026 support this pattern when one side is a base table (here, both are). The MV stores the join's pre-aggregated rollup per (country, day).
HLL_COUNT.INIT(user_id) stores a HyperLogLog sketch — a small, mergeable structure that estimates distinct counts. The sketch is what makes weekly/monthly rollups cheap.
The dashboard selects from the MV (or from the base table — the planner can substitute either way). It calls HLL_COUNT.MERGE to combine daily sketches into a weekly sketch, then HLL_COUNT.EXTRACT to get the distinct-count estimate.
Without HLL, computing weekly unique users would require scanning the full daily user_id lists — expensive at billions of events. With HLL, each day's sketch is a few KB; the weekly merge is constant-time per row.
The accuracy trade-off: HLL gives a typical relative error of ~1.6% on the distinct count. For dashboards, this is invisible to humans; for billing or compliance, use COUNT(DISTINCT) instead.

Output.

country	week	unique_users (HLL)
US	2026-05-04	4,213,891
US	2026-05-11	4,488,033
DE	2026-05-04	1,002,114
DE	2026-05-11	1,058,890

Rule of thumb. Use HLL_COUNT-backed MVs for any dashboard that asks "distinct users in arbitrary time windows." The accuracy is fine for analytics, the storage is tiny, and the planner picks the MV automatically when the dashboard's distinct-count call matches.

Senior interview question on MV refresh + planner rewrite

A senior interviewer might ask: "Your team creates a materialized view but the dashboard's still slow and the bill is bigger than before. Walk me through the diagnostic steps and the most common root causes."

Solution Using a 5-step MV diagnostic runbook

-- Step 1 — Is the MV being used at all?
EXPLAIN
SELECT country, DATE(event_ts) AS day, SUM(revenue)
FROM `proj.analytics.events`
WHERE event_ts >= '2026-06-15'
GROUP BY country, day;
-- Look for: query_plan.materializedViewUsed

-- Step 2 — Is the MV fresh enough?
SELECT
  table_name,
  refresh_watermark,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), refresh_watermark, MINUTE) AS minutes_stale
FROM `proj.analytics.INFORMATION_SCHEMA.MATERIALIZED_VIEWS`
WHERE table_name = 'events_daily_country';

-- Step 3 — Is the MV refresh consuming all my budget?
SELECT
  job_id,
  total_bytes_billed / POW(10, 12) AS tb_billed,
  total_slot_ms / 1000 AS slot_s,
  statement_type
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = 'REFRESH_MATERIALIZED_VIEW'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY total_bytes_billed DESC LIMIT 10;

-- Step 4 — Do the MV's PARTITION/CLUSTER keys match dashboard filters?
SELECT ddl FROM `proj.analytics.INFORMATION_SCHEMA.TABLES`
WHERE table_name = 'events_daily_country';

-- Step 5 — Is there a smart MV-incompatible pattern?
-- (windowed function, correlated subquery, RIGHT JOIN, etc.)

Step-by-step trace.

Step	Diagnostic	Likely root cause if fails
1	`EXPLAIN` shows `materializedViewUsed = null`	planner is not picking the MV
2	`minutes_stale` > `max_staleness`	refresh is slower than freshness budget
3	Refresh job costs $$$	base-table micro-batches → frequent refresh
4	Dashboard `WHERE` doesn't align with MV `PARTITION BY`	partition pruning impossible on MV
5	Dashboard SQL uses `ROW_NUMBER()`	planner cannot rewrite into MV

The runbook isolates the failure mode in 5 minutes flat. Most "MV is slow" tickets come down to step 1 (wrong query shape) or step 3 (refresh thrashing on a small-batch base table).

Output:

Symptom	Step	Fix
12 TB scanned, MV exists	1	rewrite dashboard SQL to match MV `GROUP BY` shape
MV always stale	2	loosen `max_staleness`, or speed up refresh by partitioning base table
Refresh credits dwarf scan savings	3	batch base-table writes; loosen `max_staleness`
Dashboard scans whole MV	4	align MV `PARTITION BY` to dashboard's date filter
Planner ignores MV silently	5	rewrite query to MV-eligible pattern

Why this works — concept by concept:

Aggregate-awareness rewrite — the planner only rewrites when the query's GROUP BY and aggregate functions match the MV's. Verify with EXPLAIN's materializedViewUsed.
Freshness gates substitution — even if the query matches the MV, an out-of-date MV without max_staleness won't be used. The freshness clause is what allows the planner to use a slightly-stale MV.
Refresh thrash — micro-batched base-table writes can fire MV refreshes hundreds of times per day. The fix is at the producer (batch the writes) or at the MV (loosen max_staleness).
Partition / cluster alignment — the MV is itself a partitioned table; if its partitions don't match the dashboard filter, the MV scan reads the whole MV instead of pruning.
Cost — refresh is O(delta bytes per refresh tick) × (refresh ticks per day). Scan savings are O(dashboard refresh count × MV bytes vs base-table bytes). The net only works when scan savings > refresh cost.

SQL
Topic — aggregation
Aggregation problems (SQL)

Practice →

SQL Topic — optimization Query optimization problems

Practice →

3. BI Engine — in-memory acceleration layer

`bigquery bi engine` is a regional in-memory column-store cache automatically populated on hot queries — reservation-based capacity, planner-driven pinning, sub-100ms responses

The mental model in one line: BI Engine is a regional in-memory cache that BigQuery's planner automatically pins hot columns into — you buy GB-hour of reservation capacity, BigQuery decides what to cache, and your dashboard queries hit RAM instead of disk. Once you internalise "you buy capacity; the planner picks what to pin," every bigquery bi engine interview question becomes a deduction from "reservation + automatic pinning."

What BI Engine actually is.

A regional in-memory columnar cache layered on top of BigQuery storage. Each region runs its own BI Engine pool; you buy capacity per region.
The cache is automatically populated by hot queries. BigQuery's planner decides which columns (from base tables, MVs, or views) to pin based on observed query patterns.
The cache is transparent. Dashboard SQL doesn't change; the planner consults BI Engine first, then falls back to BigQuery storage if a column isn't pinned.
The cache inherits aggregate awareness. If your MV is hot, BI Engine pins the MV columns. Queries that the planner already rewrites to use the MV also hit BI Engine.

The eligibility surface.

Supported SQL. Most simple analytics SQL: SELECT, WHERE, GROUP BY, JOIN (with caveats), ORDER BY, LIMIT. Plus most scalar functions, date/time, math, regex.
Not supported. External tables (federated queries on Cloud SQL, BigQuery Omni), user-defined functions (JS UDFs), some complex array operations, materialised views with smart-MV features that exceed BI Engine's expression list.
Supported clients. Looker, Looker Studio, Tableau (BigQuery JDBC connector), Power BI, Hex, Mode, custom BigQuery REST API clients. BI Engine is transparent — clients don't know whether the cache or the storage layer served the query.

The reservation pricing model.

BI Engine is reservation-based: buy capacity in GB, billed per GB-hour in the region.
Capacity sizing. Add up the columnar size of every hot column you want cached, plus headroom. A 50 GB MV with 8 hot columns might need ~10 GB BI Engine (BigQuery dictionary-compresses heavily).
Per-region. A 50 GB reservation in us-central1 doesn't help europe-west1 queries — buy capacity per region you need.
No per-query SKU in 2026. Earlier per-query BI Engine pricing was retired; the only model is capacity-based.

What the planner decides.

Which columns to pin. BigQuery's planner tracks column frequency, query selectivity, and table popularity. The hottest columns of the hottest tables/MVs are pinned first.
When to evict. LRU eviction; cold columns are dropped when the cache is full.
When to bypass. Queries that BI Engine can't accelerate (UDFs, federated tables, smart-MV features outside the expression list) fall through to BigQuery storage.
You can override. bi_engine_table_preference table-level option to force a table to be cached or excluded.

Acceleration mode signal.

Each BigQuery job returns a biEngineStatistics.biEngineMode field: FULL (fully accelerated), PARTIAL (some operators bypass), DISABLED (no acceleration applied). Monitoring this is how teams catch BI Engine regressions.
A PARTIAL reading usually means one operator (a UDF, a federated table, an unsupported function) forced a fallback. Find it via biEngineStatistics.biEngineReasons.

Cost calibration — bi engine pricing cheat sheet.

Pricing varies by region; a typical US region runs around $0.04 per GB-hour ≈ ~$30 per GB-month.
An 8 GB reservation in us-central1 ≈ $240/month. A 100 GB reservation ≈ $3,000/month.
Hot tables that fit fully into BI Engine eliminate base-table scan costs for accelerated queries. The break-even is around 30–50 dashboard refreshes per day on the same hot column set.
BI Engine charges continuously while the reservation exists, regardless of query volume. Pause the reservation on weekends if your dashboards don't run.

Common interview probes on BI Engine.

"How do I enable BI Engine on a specific dashboard?" — you don't. You buy capacity per region; the planner auto-pins. Per-dashboard toggles don't exist.
"What does biEngineMode: PARTIAL mean?" — some operators in the query were not BI-Engine-eligible and fell through to BigQuery storage. Inspect biEngineReasons to find which.
"Can BI Engine accelerate external tables?" — no. External / federated tables (Cloud SQL, BigQuery Omni) are excluded.
"Does BI Engine work with MVs?" — yes. The planner pins MV columns the same way it pins base-table columns; aggregate-aware rewrite still applies on top.

Worked example — sizing a BI Engine reservation

Detailed explanation. A team has a 50 GB MV, 8 hot dashboards hitting 5 columns each, and a $300/month budget. Sizing the reservation is a back-of-envelope math problem: column count × average column size + headroom.

Question. Compute the right BI Engine reservation size for an 8-dashboard portfolio. Walk through the columnar-size estimate, the safety headroom, and the monthly cost.

Input.

Asset	Size
`mv_daily_country` (MV)	50 GB
Hot columns across 8 dashboards	5 distinct columns, ~20% of total bytes
`bi engine pricing` (us-central1)	$0.04/GB-hour
Budget	$300/month

Code.

# Create a BI Engine reservation via gcloud
gcloud alpha bi-reservations update \
  --location=us \
  --reservation_size=12  # GB

# Inspect the reservation
gcloud alpha bi-reservations describe --location=us

-- Verify a sample dashboard query is accelerated
SELECT
  job_id,
  bi_engine_statistics.bi_engine_mode AS mode,
  bi_engine_statistics.bi_engine_reasons AS reasons
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND query LIKE '%mv_daily_country%'
ORDER BY creation_time DESC LIMIT 10;

Step-by-step explanation.

Estimate columnar size of the hot columns. The MV is 50 GB total; 5 hot columns out of ~25 columns = roughly 20% of bytes = 10 GB. (BigQuery's columnar storage is roughly proportional to column count; dictionary compression varies but the 20% heuristic holds for most analytic columns.)
Add headroom — 20% for cache churn (other ad-hoc queries that pin temporarily). Target: 10 GB × 1.2 ≈ 12 GB reservation.
Cost: 12 GB × $0.04/GB-hour × 730 hours/month ≈ $350/month ($43/GB-month using a 730-hour month). Slightly over budget; either reduce headroom or pause the reservation overnight.
Verify with INFORMATION_SCHEMA.JOBS_BY_PROJECT: the bi_engine_mode should be FULL for the hot dashboards. If it's PARTIAL, the cache is too small and BigQuery is evicting hot columns under pressure.
The break-even on this reservation: if 8 dashboards × 20 refreshes/day each = 160 refreshes/day were previously hitting raw storage at 8 GB scanned each (i.e. via the MV), that's 1.28 TB/day × $5/TB ≈ $6.40/day = $192/month. The BI Engine reservation costs more than the pure-MV scan, but it brings sub-100ms latency that MV alone doesn't.

Output.

Reservation	Cost/month	bi_engine_mode	Latency p99
4 GB (under-sized)	~$120	PARTIAL (eviction)	200–400 ms
8 GB (right-sized for MV alone)	~$240	FULL	60–80 ms
12 GB (right-sized w/ headroom)	~$350	FULL	40–60 ms
24 GB (over-sized)	~$700	FULL	40 ms (no benefit)

Rule of thumb. Size BI Engine to ~1.5× the columnar size of your hot column set. Smaller → cache churn → PARTIAL mode and patchy latency. Larger → waste. Use biEngineMode and biEngineReasons as the live signal.

Worked example — same dashboard, MV-only vs MV + BI Engine

Detailed explanation. Quantify the marginal value of BI Engine on top of MV. The MV alone takes the dashboard from 8 s → 200 ms. BI Engine takes it from 200 ms → 50 ms. Whether the extra latency drop is worth the reservation depends on user count and dashboard QPS.

Question. A Looker Studio dashboard already uses the daily-revenue MV from Section 2. Show the latency breakdown with and without BI Engine and the cost calc for a 12 GB reservation.

Input.

Setup	MV	BI Engine
(A) MV only	yes	no
(B) MV + BI Engine	yes	12 GB

Code.

-- Setup A — dashboard query, MV only
SELECT country, day, revenue
FROM `proj.analytics.events`     -- planner rewrites to MV
WHERE event_ts >= '2026-06-15'
GROUP BY country, day;

-- Setup B — same query, BI Engine reservation now exists
-- (no DDL change; reservation is regional)
-- gcloud alpha bi-reservations update --location=us --reservation_size=12

-- Verify B's acceleration
SELECT
  bi_engine_statistics.bi_engine_mode,
  total_bytes_processed,
  total_slot_ms,
  TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS latency_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND query LIKE '%event_ts >= ''2026-06-15''%'
ORDER BY creation_time DESC LIMIT 5;

Step-by-step explanation.

Setup A: the planner rewrites the dashboard query to scan the MV. The MV is ~8 GB; BigQuery's storage layer reads the relevant partitions, decompresses, and applies the GROUP BY. Latency p99: ~200 ms.
Setup B: the planner sees the MV is hot and pins its columns into BI Engine. The dashboard query now hits the in-memory cache. Latency p99: ~50 ms.
The marginal saving from B vs A is 150 ms per refresh. For a 200-user team where each user refreshes the dashboard 5×/day, that's 1,000 refreshes/day × 150 ms saved = 150 seconds of human waiting eliminated daily.
The cost shape: A pays for MV refresh (~$8/day from Section 2 example) plus per-query scan ($0.04 × 30 refreshes ≈ $1.20/day) = ~$10/day. B adds the BI Engine reservation (12 GB × $0.04 × 24 ≈ $11.50/day) = ~$21/day total.
The verdict: B is roughly 2× the cost of A for a 4× latency improvement. Worth it for executive / customer-facing dashboards where p99 matters; not worth it for internal monitoring dashboards where 200 ms is fine.

Output (latency + cost comparison).

Metric	A (MV only)	B (MV + BIE 12 GB)
Latency p50	100 ms	30 ms
Latency p99	200 ms	50 ms
Bytes scanned / refresh	8 GB	0 (cache)
Daily refresh cost	~$10	~$21
`bi_engine_mode`	n/a	FULL

Rule of thumb. BI Engine on top of MV is a "latency premium" purchase — pay 1.5–2× the MV-only cost for 3–4× the latency reduction. Pay it only when sub-100ms matters to the consumer.

Worked example — diagnosing a `PARTIAL` acceleration

Detailed explanation. A common production debug — the dashboard worked yesterday at 50 ms, now it's 250 ms, and bi_engine_mode shows PARTIAL. Senior engineers walk through biEngineReasons to find the operator that fell through, then either fix the query, expand the reservation, or accept the partial.

Question. A dashboard regressed from 50 ms to 250 ms. The bi_engine_mode is now PARTIAL. Show the diagnostic SQL and the three most common root causes.

Input.

Reading	Yesterday	Today
Latency p99	50 ms	250 ms
`bi_engine_mode`	FULL	PARTIAL
Reservation size	12 GB	12 GB

Code.

-- Look at the bi_engine_reasons for the failing job
SELECT
  job_id,
  bi_engine_statistics.bi_engine_mode AS mode,
  bi_engine_statistics.bi_engine_reasons AS reasons,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE bi_engine_statistics.bi_engine_mode = 'PARTIAL'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
ORDER BY creation_time DESC LIMIT 10;

-- Common reason values (returned in biEngineReasons[].code):
-- - QUERY_HAS_UNSUPPORTED_FUNCTION
-- - QUERY_HAS_UNSUPPORTED_TABLE_TYPE  (e.g. external / federated)
-- - QUERY_HAS_UDF
-- - INPUT_TOO_LARGE     (reservation too small; eviction)
-- - UNSUPPORTED_JOIN

-- If INPUT_TOO_LARGE — check cache hit rate
SELECT
  COUNTIF(bi_engine_statistics.bi_engine_mode = 'FULL') / COUNT(*) AS full_rate,
  COUNTIF(bi_engine_statistics.bi_engine_mode = 'PARTIAL') / COUNT(*) AS partial_rate
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND query LIKE '%events%';

Step-by-step explanation.

Pull bi_engine_reasons from INFORMATION_SCHEMA.JOBS_BY_PROJECT. The field contains an array of (code, message) pairs explaining which operator(s) forced a fallback.
Cause 1 — new UDF. A teammate added a JavaScript UDF for a new metric. UDFs are not BI-Engine-eligible. Fix: replace the UDF with a SQL expression, or accept the partial.
Cause 2 — external table. Someone joined to a federated Cloud SQL table for a new dimension. Federated tables aren't BI-Engine-eligible. Fix: replicate the federated dimension into a BigQuery base table.
Cause 3 — reservation too small. The hot column set grew; the cache is now evicting under pressure. INPUT_TOO_LARGE will appear in biEngineReasons. Fix: expand the reservation, or trim the hot column set.
The senior pattern: alert on partial_rate > 5% over a rolling 1-hour window. Most teams catch reservation-sizing regressions this way before users complain.

Output (three diagnostic shapes).

Reason code	Root cause	Fix
QUERY_HAS_UDF	new JS UDF	rewrite as SQL or accept partial
QUERY_HAS_UNSUPPORTED_TABLE_TYPE	federated table joined in	replicate to BigQuery base table
INPUT_TOO_LARGE	reservation too small	expand BI Engine reservation
UNSUPPORTED_JOIN	smart-MV feature outside BI Engine list	revert join shape or live with partial

Rule of thumb. Treat bi_engine_mode = PARTIAL as a latency regression alert. Most fixes are query-level (UDF or federated-table swap), not reservation-level.

Senior interview question on BI Engine reservation sizing

A senior interviewer might ask: "Your team has 30 dashboards across 4 BigQuery regions and a $2,000/month BI Engine budget. Walk me through how you'd allocate the budget across regions and prove the allocation was correct."

Solution Using a region-by-dashboard-QPS allocation model

-- Step 1 — count hot queries per region last 30 days
WITH q AS (
  SELECT
    EXTRACT(LOCATION FROM job_id) AS region,
    COUNT(*) AS query_count,
    SUM(total_bytes_processed) / POW(10, 12) AS tb_scanned
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND query LIKE '%dashboard%'
  GROUP BY region
)
SELECT * FROM q;

-- Step 2 — for each region, size the reservation to ~1.5x columnar hot set
-- (back-of-envelope)
SELECT
  table_name,
  ROUND(size_bytes / POW(10, 9), 2) AS gb
FROM `proj.analytics.__TABLES__`
WHERE table_name LIKE 'mv_%'
ORDER BY size_bytes DESC LIMIT 10;

-- Step 3 — apply allocation
-- us-central1: 30 GB reservation (~$900/month)   — 60% of queries, biggest cohort
-- europe-west1: 12 GB reservation (~$360/month)  — 25% of queries
-- asia-northeast1: 8 GB reservation (~$240/month) — 10% of queries
-- us-east1: 4 GB reservation (~$120/month)        — 5% of queries

Step-by-step trace.

Region	% of queries	MV bytes	Hot columns	Reservation	Cost / mo
us-central1	60%	80 GB	20 GB	30 GB	$900
europe-west1	25%	30 GB	8 GB	12 GB	$360
asia-northeast1	10%	15 GB	5 GB	8 GB	$240
us-east1	5%	8 GB	3 GB	4 GB	$120

Total reservation: 54 GB / $1,620/month — within the $2,000 budget with $380 headroom for growth. Each region is sized to ~1.5× the hot columnar set per local trace.

Output:

Region	bi_engine_mode FULL rate	Avg latency p99
us-central1	98%	50 ms
europe-west1	96%	60 ms
asia-northeast1	94%	70 ms
us-east1	92%	80 ms

Why this works — concept by concept:

Regional reservation — BI Engine is per-region. A us-central1 reservation never accelerates europe-west1 queries. Always size per region you serve dashboards in.
QPS-weighted allocation — give the most capacity to the regions with the most queries. A 60% / 25% / 10% / 5% query split should map to roughly the same reservation split.
1.5× hot column size — the rule of thumb that keeps bi_engine_mode = FULL at ≥95% without over-provisioning. Headroom for cache churn and ad-hoc queries.
Monitor bi_engine_mode = FULL rate — the live signal that your sizing is correct. Below 90% means you're under-sized; 100% with low query volume means you're over-sized.
Cost — BI Engine cost is O(GB-hour × regions). The cost is fixed per month regardless of query volume; pause unused reservations on weekends if dashboards don't run.

SQL
Topic — optimization
Query optimization problems

Practice →

SQL Topic — sql BigQuery SQL drills

Practice →

4. MV + BI Engine + partitioning + clustering — the four-layer cake

Sub-second BigQuery dashboards come from stacking four layers in the right order — partition prune → cluster prune → MV substitute → BI Engine pin

The mental model in one line: a sub-second BigQuery dashboard is the result of four independent acceleration layers stacking — partitioning prunes scan ranges to the relevant time slice, clustering prunes within partitions for filter co-location, the MV pre-aggregates the cohort, and BI Engine pins the result in RAM. Each layer multiplies the savings of the layer above. Skip one and the stack stops compounding.

Layer 1 — partitioning.

What it does. Splits a table into physical chunks by a partition key (typically date / timestamp). BigQuery's storage engine maintains a per-partition catalogue; queries with a WHERE filter on the partition key only scan matching partitions.
How much it saves. Dashboards usually look at the last 7 / 30 / 90 days. A daily-partitioned 3-year table has ~1,095 partitions; a 7-day dashboard touches 7 partitions = 0.6% of the bytes. Two orders of magnitude alone.
Sweet spot. PARTITION BY DATE(event_ts) on any fact table with a natural time dimension. Always pair with require_partition_filter = true so accidental full-table scans error out.
Failure mode. Cardinality > 4,000 partitions per table → BigQuery hits the partition limit. Use monthly or yearly partitioning for very long-history tables.

Layer 2 — clustering.

What it does. Within each partition, sorts rows by cluster keys. BigQuery's storage engine maintains a block-level catalogue per (partition, cluster-prefix); queries with filters on cluster columns skip non-matching blocks.
How much it saves. Inside a daily partition (~1.7 TB), a WHERE country = 'US' filter on a CLUSTER BY country table prunes to roughly 1/250th of the partition = 7 GB. Another 2 orders of magnitude.
Sweet spot. Cluster by the high-cardinality filter columns most queries use. Typical pattern: CLUSTER BY user_id, country, event_type.
Failure mode. Clustering up to 4 columns; beyond that, the prefix sort doesn't help. Order matters — first column is the strongest filter.

Layer 3 — materialized view.

What it does. Pre-aggregates the base table into a smaller table; the planner rewrites base-table queries that match the MV's aggregation shape.
How much it saves. A 12 TB base table aggregated to country × day produces an ~8 GB MV — 4 orders of magnitude smaller. Combined with layers 1 + 2, the MV scan is itself partition-pruned and cluster-pruned.
Sweet spot. Stable cohorts the dashboard always groups by. Country × day, product × week, segment × month — anything where the dashboard's GROUP BY is predictable.
Failure mode. MV refresh credits dwarf scan savings on a chatty base table — fix with max_staleness or batched base-table writes.

Layer 4 — BI Engine.

What it does. Pins hot column data into in-memory columnar cache. The planner consults the cache before BigQuery storage.
How much it saves. Eliminates the bytes-scanned cost on cached queries; latency drops from MV-scan (200 ms) to RAM-lookup (50 ms).
Sweet spot. High-QPS executive dashboards on stable hot column sets; multi-dashboard portfolios where the reservation amortises.
Failure mode. Reservation under-sized → bi_engine_mode = PARTIAL → patchy latency.

Layer interactions — the multiplier effect.

Layers 1 + 2 reduce the base-table scan from 12 TB → 7 GB (10,000× less). Already a big win.
Layer 3 reduces it further to ~8 GB across the whole MV, but the MV is itself partitioned + clustered, so the dashboard scan on the MV is ~50 MB. Another 100× win.
Layer 4 collapses the 50 MB scan to a memory lookup. The latency floor is no longer storage; it's the network round-trip to the BI Engine pool.
The order of operations matters. Build partitioning and clustering on the base table first; build the MV on top (it inherits the structure); buy BI Engine last once the MV is hot.

The 5-step runbook for a sub-second dashboard.

Partition the base table by the date column the dashboard filters on. Add require_partition_filter = true.
Cluster the base table by the top 2–3 high-cardinality filter columns the dashboard uses.
Build a materialized view matching the dashboard's GROUP BY shape; partition + cluster the MV the same way.
Verify the planner uses the MV with EXPLAIN and INFORMATION_SCHEMA.JOBS_BY_PROJECT.query_info.
Buy a BI Engine reservation sized to 1.5× the hot columnar set in the dashboard's region; verify bi_engine_mode = FULL.

Common interview probes on the four-layer cake.

"Which layer do I apply first?" — partitioning, always. Without it, every other layer is patched on top of a full-table scan.
"Can I skip the MV and use BI Engine alone?" — yes for small base tables (< 50 GB), no for petabytes. BI Engine caches columns, not pre-aggregates; without the MV, it still has to scan the base columns into RAM.
"Do I need clustering if I have an MV?" — yes. The MV is itself a table; clustering it makes the MV scan faster.
"What's the typical break-even?" — partition + cluster pays back on day 1 of dashboard use. MV pays back at ~5 refreshes/day. BI Engine pays back at ~3 dashboards on the same MV.

Worked example — applying the runbook to a 12 TB events table

Detailed explanation. Walk through the four layers in order on a real fact table. Show the DDL changes, the dashboard latency at each step, and the cumulative cost shape. This is the canonical interview deep-dive on bigquery dashboard performance.

Question. Given a 12 TB events table and a daily-revenue-by-country dashboard at 8 s / $60-per-refresh, apply the 5-step runbook. Show the DDL at each step and the latency / cost after each.

Input.

Asset	Starting state
`proj.analytics.events`	12 TB, no partition, no cluster
Dashboard	8 s p99, $60/refresh, 30×/day = $1,800/day

Code.

-- Step 1 — Partition the base table
ALTER TABLE `proj.analytics.events`
  -- Note: in BigQuery, you create-as-select to add partitioning to an existing table
;
-- In practice:
CREATE TABLE `proj.analytics.events_partitioned`
PARTITION BY DATE(event_ts)
CLUSTER BY country, user_id     -- Step 2 baked in
OPTIONS(require_partition_filter = true)
AS SELECT * FROM `proj.analytics.events`;

-- Step 3 — Build the MV
CREATE MATERIALIZED VIEW `proj.analytics.events_daily_country`
PARTITION BY day
CLUSTER BY country
OPTIONS(max_staleness = INTERVAL 30 MINUTE)
AS
SELECT
  country,
  DATE(event_ts) AS day,
  SUM(revenue) AS revenue,
  COUNT(*) AS n_events
FROM `proj.analytics.events_partitioned`
GROUP BY country, day;

-- Step 4 — Verify planner uses MV
EXPLAIN
SELECT country, DATE(event_ts) AS day, SUM(revenue)
FROM `proj.analytics.events_partitioned`
WHERE event_ts BETWEEN '2026-06-15' AND '2026-06-21'
GROUP BY country, day;
-- materializedViewUsed = "events_daily_country"

-- Step 5 — BI Engine reservation (gcloud)
-- gcloud alpha bi-reservations update --location=us --reservation_size=12

Step-by-step explanation.

Step 1 + 2 — partition the base table by DATE(event_ts), cluster by (country, user_id). The dashboard's 7-day filter now scans 7 partitions × ~1.7 TB = 12 TB total, but the cluster filter on country prunes each partition to ~7 GB. Effective scan: ~50 GB across 7 partitions. Latency drops from 8 s → 2 s.
Step 3 — build the MV. The MV is ~8 GB total, ~50 MB per day-partition. The dashboard's 7-day filter now scans ~350 MB of MV. Latency drops from 2 s → 200 ms. Bytes-per-refresh: ~8 GB → $0.04.
Step 4 — verify with EXPLAIN. If materializedViewUsed is populated, the substitution worked. Run this as a CI check; alert if any production dashboard query loses the substitution.
Step 5 — buy BI Engine. 12 GB reservation costs ~$11.50/day. The MV's hot columns get pinned; the dashboard now hits RAM. Latency drops from 200 ms → 50 ms.
Cumulative cost shape: starting at $1,800/day with 30 refreshes × $60. Ending at $8 (MV refresh) + $11.50 (BIE reservation) ≈ $20/day. 90× cheaper, 160× faster.

Output (latency + cost at each step).

Step	Layers active	Latency p99	Bytes / refresh	$/refresh	$/day at 30 refreshes
Baseline	none	8 s	12 TB	$60	$1,800
After 1+2	partition + cluster	2 s	50 GB	$0.25	$7.50
After 3	+ MV	200 ms	8 GB (then 350 MB)	$0.04	$1.20 + $8 refresh
After 5	+ BI Engine	50 ms	0	$0	$11.50 fixed + $8 refresh

Rule of thumb. Apply the runbook in order; measure after each step. Most teams stop at Step 3 (MV); the marginal 200 ms → 50 ms from BI Engine is worth it only for high-traffic, latency-sensitive dashboards.

Worked example — the failure modes when layers are skipped

Detailed explanation. Show what goes wrong when each layer is added in the wrong order or skipped entirely. The "BI Engine on a non-partitioned table" anti-pattern is especially common — teams buy a 100 GB reservation and wonder why nothing speeds up.

Question. For each of the four common shortcuts (skip partitioning, skip clustering, skip MV, skip BI Engine), show what the dashboard latency and cost look like. Highlight which is the worst trade-off.

Input.

Variant	Layers
(V1)	Just BI Engine on raw base table
(V2)	Just MV, no partitioning on base
(V3)	Partition + cluster only, no MV
(V4)	Partition + cluster + MV, no BI Engine
(V5)	All four layers

Code.

-- V1 — buy BI Engine, leave base table un-partitioned (anti-pattern)
-- gcloud alpha bi-reservations update --location=us --reservation_size=100
SELECT country, DATE(event_ts), SUM(revenue)
FROM `proj.analytics.events_raw`    -- no partition, no cluster
WHERE event_ts BETWEEN '2026-06-15' AND '2026-06-21'
GROUP BY country, DATE(event_ts);
-- BI Engine cannot cache 12 TB; query falls through to storage scan

-- V2 — MV without partition on base
CREATE MATERIALIZED VIEW mv_no_partition_base AS
SELECT country, DATE(event_ts) AS day, SUM(revenue)
FROM `proj.analytics.events_raw`    -- still 12 TB, unstructured
GROUP BY country, day;
-- First MV refresh scans 12 TB; subsequent refreshes still scan whole table
-- (can't track partition delta efficiently)

-- V5 — all four layers (Section 4 worked example)

Step-by-step explanation.

V1 — BI Engine alone on a 12 TB raw table. The reservation might hold a few GB of hot columns; the rest spills back to storage. bi_engine_mode is PARTIAL constantly. Latency lifts from 8 s → 5 s; not nearly enough to justify $3,000/month BI Engine spend.
V2 — MV without partitioning. The first MV refresh scans 12 TB. Without partition tracking, every subsequent refresh re-scans nearly the whole base table — refresh credits explode to $60/day. The MV exists but is wildly more expensive than a partitioned-base MV.
V3 — partition + cluster, no MV. Dashboard scans drop to ~50 GB per refresh. At 30 refreshes/day that's $7.50/day — already 240× cheaper than baseline. Good enough for many teams; the MV is the marginal step.
V4 — all but BI Engine. Latency at 200 ms, cost at ~$9/day. The sweet spot for most internal dashboards.
V5 — full stack. 50 ms p99, ~$20/day. Pay the premium only for executive / customer-facing dashboards.

Output.

Variant	Latency	Daily cost	When it's OK
V1 (BIE alone, no partition)	5–8 s	$100+	never — wasted spend
V2 (MV, no partition on base)	200 ms (queries) but $60/day refresh	$60+	only if base writes are rare
V3 (partition + cluster only)	2 s	$8	most internal dashboards
V4 (no BI Engine)	200 ms	$9	most production dashboards
V5 (all four)	50 ms	$20	executive / customer-facing

Rule of thumb. Never buy BI Engine before partitioning and MV are in place. The reservation only accelerates hot columns; if your base table is 12 TB un-pruned, the cache is useless overhead.

Worked example — Tableau dashboard hitting all four layers

Detailed explanation. End-to-end pattern: a Tableau dashboard configured with the BigQuery JDBC connector, hitting a partitioned + clustered base table that has a materialized view, with BI Engine pinning the MV columns. Show the connection string, the Tableau filter pattern that aligns with all four layers, and the resulting performance.

Question. Configure a Tableau dashboard to hit all four layers. Show the JDBC config, the Tableau filter shelf (which must align with partition + cluster keys), and the verified bi_engine_mode = FULL.

Input.

Asset	Config
Base table	partitioned by `DATE(event_ts)`, clustered by `country, user_id`
MV	`events_daily_country`, partitioned by `day`, clustered by `country`
BI Engine	12 GB reservation in us-central1

Code.

# Tableau BigQuery JDBC connector (simplified)
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
  ProjectId=proj;
  OAuthType=0;
  OAuthServiceAcctEmail=<svc>;
  OAuthPvtKeyPath=<key-path>;
  EnableHighThroughputAPI=1;

-- The query Tableau generates (depends on filter shelf)
SELECT
  country,
  DATE(event_ts) AS day,
  SUM(revenue) AS revenue
FROM `proj.analytics.events_partitioned`
WHERE event_ts BETWEEN @start AND @end          -- aligns with partition
  AND country IN UNNEST(@countries)             -- aligns with cluster
GROUP BY country, day
ORDER BY day, country;

-- Verify all four layers fired
SELECT
  job_id,
  bi_engine_statistics.bi_engine_mode AS bie_mode,
  query_info.optimization_details AS opt,
  total_bytes_processed,
  TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS latency_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND query LIKE '%events_partitioned%'
  AND query LIKE '%SUM(revenue)%'
ORDER BY creation_time DESC LIMIT 5;

Step-by-step explanation.

Tableau's JDBC connector emits a SQL query whose WHERE clause is shaped by the filter shelf. Putting event_ts (date range) and country on the shelf is the critical move — they align with the base table's partition and cluster keys.
The query hits the partitioned base table. The planner sees the event_ts filter → prunes to the 7 day-partitions. Sees the country filter → prunes blocks within each partition. Sees the GROUP BY country, day matches the MV → rewrites to scan the MV.
The MV is partitioned by day and clustered by country — same filter alignment. Tableau's filter prunes the MV to ~50 MB.
BI Engine has the MV's hot columns pinned. The dashboard hits RAM; latency p99 = 50 ms.
Verify via INFORMATION_SCHEMA: bi_engine_mode = FULL, optimization_details shows MV_REWRITE_APPLIED, total_bytes_processed = 0 (BI Engine served). All four layers fired.

Output (verified all-four-layer hit).

Job #	bie_mode	optimization_details	latency_ms
job-001	FULL	MV_REWRITE_APPLIED	48
job-002	FULL	MV_REWRITE_APPLIED	52
job-003	FULL	MV_REWRITE_APPLIED	51
job-004	FULL	MV_REWRITE_APPLIED	49

Rule of thumb. The dashboard filter shelf must align with the table's partition + cluster keys and the MV's GROUP BY. Misaligned filters drop a layer and tank performance — train BI authors on this once and the four-layer cake stays sub-second.

Senior interview question on stacking the four layers

A senior interviewer might ask: "An exec dashboard on a 50 TB BigQuery table takes 12 seconds. Your team owns the table. Walk me through, layer by layer, how you'd get it under 100 ms — and how you'd know when to stop."

Solution Using the 4-layer cake with measure-then-add discipline

The runbook — measure, add, measure, add
========================================

0. Baseline: 12 s, $80/refresh, 50 TB scan, no acceleration

Layer 1 — Partitioning
  - Add PARTITION BY DATE(event_ts) on the base table
  - Add require_partition_filter = true
  - Re-measure: 4 s, $5/refresh, 3 TB scan (7-day filter)
  - STOP IF: budget met. Otherwise continue.

Layer 2 — Clustering
  - Add CLUSTER BY country, user_id
  - Re-measure: 1.5 s, $0.20/refresh, 100 GB scan
  - STOP IF: budget met. Otherwise continue.

Layer 3 — Materialized View
  - CREATE MATERIALIZED VIEW events_daily_country
  - Verify EXPLAIN.materializedViewUsed
  - Re-measure: 250 ms, $0.05/refresh, 350 MB scan
  - STOP IF: < 500 ms is acceptable. Otherwise continue.

Layer 4 — BI Engine
  - Reservation 16 GB in us-central1
  - Verify bi_engine_mode = FULL on hot dashboards
  - Re-measure: 60 ms, $0 scan + reservation cost
  - STOP IF: < 100 ms achieved.

Step-by-step trace.

After step	Latency p99	Daily cost	Notes
Baseline	12 s	$2,400 (30 refreshes)	full scan, no help
Layer 1	4 s	$150	partition prune saves ~94%
Layer 2	1.5 s	$6	cluster prune saves another ~95%
Layer 3	250 ms	$1.50 + $10 MV refresh	MV substitution kicks in
Layer 4	60 ms	$1.50 + $10 + $12 BIE	sub-100 ms target met

The discipline is measure after every step. Often you find Layer 2 alone gets you to "good enough" and the MV / BI Engine investments aren't justified. Don't add layers you don't need.

Output:

Layer	Marginal gain	Marginal cost	Verdict
Partition	12s → 4s (3x)	tiny	always do this first
Cluster	4s → 1.5s (2.7x)	tiny	always do this second
MV	1.5s → 250ms (6x)	$10/day refresh credits	do if dashboard refreshes > 5×/day
BI Engine	250ms → 60ms (4x)	$12/day reservation	do if p99 < 100 ms is required

Why this works — concept by concept:

Order matters — bottom layers first — partitioning is the only layer that cuts the input bytes; everything else (cluster, MV, BIE) is patched on top. Skip partitioning and the other layers cost more and gain less.
Each layer multiplies — partition prunes by 94%, cluster by 95% on top of that, MV by 95% on top of that, BI Engine by another 80%. Compounded, that's ~12 s → 50 ms.
Measure between layers — most teams over-build. Measure after each layer; stop when the budget is met. Often Layer 2 alone is enough.
Cost shape inverts — baseline is pay-per-byte-scanned (unpredictable, big); end state is pay-per-MV-refresh + per-GB-hour-BIE (predictable, small).
Cost — partition + cluster have ~zero ongoing cost. MV has O(daily delta) refresh cost. BI Engine has O(GB-hour) reservation cost. The cost ceiling shifts from variable (scan) to fixed (reservation + refresh).

SQL
Topic — optimization
Query optimization problems

Practice →

SQL Topic — aggregation Aggregation problems (SQL)

Practice →

5. Cost / decision matrix

Senior engineers pick MV, BI Engine, or both from a 5-axis decision matrix — never from "everything always"

The mental model in one line: MV and BI Engine are each a fixed-cost commitment (refresh credits, reservation $) traded against the variable cost of un-accelerated dashboard scans — the right pick depends on cohort stability, refresh frequency, dashboard count, latency budget, and total spend envelope. Once you internalise "fixed vs variable," the "should we add MV or BI Engine?" interview surface collapses to a one-page matrix.

The 5 axes in order.

Axis 1 — Cohort stability. Does the dashboard's GROUP BY shape stay the same across refreshes? Stable → MV pays. Unstable / ad-hoc → no MV can pre-aggregate.
Axis 2 — Refresh frequency. How often does the dashboard refresh per day? > 5/day → MV pays. < 1/day → on-demand scan may still be cheaper than refresh credits.
Axis 3 — Dashboard portfolio. How many dashboards share the same hot column set? 3+ → BI Engine reservation amortises. 1 → marginal benefit from cache.
Axis 4 — Latency budget. Sub-100 ms p99 required? → MV + BI Engine. 100ms–1 s OK? → MV alone. 1s+ OK? → partition + cluster only.
Axis 5 — Total spend envelope. What's the monthly budget? < $500/mo → MV only. $500–$2,000/mo → MV + small BIE. > $2,000/mo → MV + per-region BIE.

When MV pays — the four conditions.

Stable cohort (same GROUP BY shape every refresh).
High refresh frequency (> 5/day on the same cohort).
Predictable filters (the planner can prune the MV's partitions).
Base-table writes are batched (refresh credits don't thrash).

When BI Engine pays — the four conditions.

Multi-dashboard portfolio (3+ dashboards on the same hot columns).
Sub-100 ms latency budget (where MV alone wouldn't hit).
Stable hot column set (cache stays warm).
Regional consolidation (don't fan out across regions you can't afford).

When neither pays.

Ad-hoc data-science queries with ever-changing dimensions — no cohort to pre-aggregate.
One-off reports run < 1×/day — refresh credits + reservation are wasted overhead.
Tiny base tables (< 50 GB) — partition + cluster alone hits 200 ms; further layers are wasted.

The hybrid pattern — MV + BI Engine for BI, raw tables for ad-hoc.

Production dashboards hit the MV + BI Engine stack.
Data scientists hit the raw partitioned + clustered base tables directly with on-demand scans.
Two acceleration profiles, one storage layer — the team gets sub-second dashboards without paying BI Engine reservation for unpredictable exploration.

The break-even cheat sheet.

MV vs raw scan. Break-even at ~5 refreshes per day on the same cohort. Below that, refresh credits cost more than scan savings.
BI Engine vs MV alone. Break-even at ~3 dashboards on the same hot column set, or 1 dashboard with > 100 refreshes/day.
BI Engine 12 GB vs scan savings. $11.50/day vs $0.04 × scan savings/refresh — break-even at ~280 refreshes/day across all dashboards on the reservation.

Senior interview signals — what to mention unprompted.

Refresh credit attribution. Each MV refresh is billed to the table's project; high-frequency refresh thrash is a billing signal.
Eligibility regression. A teammate adds a UDF or federated table → bi_engine_mode drops from FULL to PARTIAL → silent latency regression. Catch via daily CI.
BI tool integration. Tableau / Looker / Looker Studio each surface BI Engine acceleration differently in their job-info panels; train BI authors to spot the FULL / PARTIAL / DISABLED reading.

Common interview probes on the decision matrix.

"When is MV the wrong choice?" — ad-hoc dashboards, low-refresh-frequency reports, base tables with chatty micro-batched writes.
"When is BI Engine overkill?" — single dashboard on a partitioned + MV'd table at 200 ms p99 — the latency drop to 50 ms isn't worth the reservation cost.
"What's the order of operations?" — partition → cluster → MV → BI Engine. Measure after each.
"How do you justify a BI Engine reservation to finance?" — compare reservation cost to baseline daily scan cost × 30 days; show the break-even in days.

Worked example — when MV pays back

Detailed explanation. A retail BI dashboard refreshes 50×/day showing weekly revenue by category and country. Cohort is rock-solid stable. Base table is 8 TB of orders, daily-partitioned. Compute the MV's break-even.

Question. Compute the daily $ break-even of an MV on a dashboard with 50 refreshes/day, scanning 200 GB/refresh without the MV vs 5 GB/refresh with it. Refresh credits: 200 GB/day of new base data.

Input.

Metric	No MV	With MV
Refreshes/day	50	50
Bytes/refresh	200 GB	5 GB
Daily $ scan	50 × 200 GB × $5/TB = $50	50 × 5 GB × $5/TB = $1.25
MV refresh credit	n/a	200 GB/day × $5/TB = $1

Code.

-- The MV
CREATE MATERIALIZED VIEW `proj.retail.orders_weekly_cat_country`
PARTITION BY week_start
CLUSTER BY country, category
OPTIONS(max_staleness = INTERVAL 60 MINUTE)
AS
SELECT
  country,
  category,
  DATE_TRUNC(DATE(order_ts), WEEK) AS week_start,
  SUM(amount) AS revenue,
  COUNT(*) AS n_orders
FROM `proj.retail.orders`
GROUP BY country, category, week_start;

Step-by-step explanation.

Without MV, each dashboard refresh scans 200 GB of the partitioned base table. 50 refreshes × 200 GB × $5/TB = $50/day. Latency p99 ≈ 1.5 s.
With MV, each dashboard refresh scans 5 GB of the MV (which is itself partitioned and clustered, so the dashboard's week-and-country filter prunes the MV). 50 × 5 GB × $5/TB = $1.25/day. Latency p99 ≈ 150 ms.
The MV refresh itself scans the daily delta of the base table (~200 GB/day in new orders) → $1/day refresh credit cost.
Net daily cost: with MV → $1.25 + $1 = $2.25/day. Without MV → $50/day. Savings: $47.75/day = $1,430/month.
Break-even is immediate — the MV pays back from day 1 because the refresh credit ($1/day) is < the per-refresh savings ($50 - $1.25 = $48.75/day).

Output.

Scenario	Daily scan $	Daily refresh $	Daily total	Latency p99
No MV	$50	$0	$50	1.5 s
With MV	$1.25	$1	$2.25	150 ms
Savings	—	—	$47.75/day	10× faster

Rule of thumb. When dashboard refresh × scan savings > MV refresh credit, the MV pays back from day 1. For stable-cohort, high-refresh dashboards this is almost always the case.

Worked example — when BI Engine pays back

Detailed explanation. Same retail team now has 6 dashboards all hitting the mv_orders_weekly_cat_country MV. Compute whether a 16 GB BI Engine reservation pays back compared to the 6-dashboard MV-only baseline.

Question. Six dashboards × 50 refreshes/day = 300 refreshes/day. Each refresh scans 5 GB of MV. Compute the break-even for a 16 GB BI Engine reservation in us-central1.

Input.

Metric	MV only	MV + BIE
Dashboards	6	6
Refreshes/day	300	300
Bytes/refresh	5 GB	0 (cache)
Daily $ scan	300 × 5 GB × $5/TB = $7.50	$0
BIE reservation	$0	16 GB × $0.04/h × 24 = $15.40/day

Code.

# 16 GB reservation in us-central1
gcloud alpha bi-reservations update --location=us --reservation_size=16

# Verify all 6 dashboards run at bi_engine_mode = FULL

SELECT
  query,
  bi_engine_statistics.bi_engine_mode,
  COUNT(*) AS n_runs,
  AVG(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_latency_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND query LIKE '%orders_weekly_cat_country%'
GROUP BY query, bi_engine_statistics.bi_engine_mode;

Step-by-step explanation.

MV-only baseline: 6 dashboards × 50 refreshes × 5 GB × $5/TB = $7.50/day. Latency ≈ 200 ms.
Add a 16 GB BI Engine reservation: cost is $15.40/day fixed regardless of query volume. Cache hits → 0 bytes scanned, latency drops to ~50 ms.
Net daily cost: BIE ($15.40) > MV-only ($7.50). The reservation costs $7.90/day more on this footprint.
The break-even is on latency, not cost. If the team values 200 ms → 50 ms for executive-facing dashboards, the $237/month premium is justified. Otherwise, stick with MV-only.
The break-even on cost would require either more dashboards (12+) or higher refresh frequency (1000+/day) on the same reservation. At that scale, the per-refresh saving overtakes the fixed reservation cost.

Output.

Scenario	Daily $	Latency p99	Verdict
MV only (6 dashboards × 50 refreshes)	$7.50	200 ms	cheaper
MV + BIE 16 GB	$15.40	50 ms	pays latency, not cost
MV + BIE for 12 dashboards × 50	$15.40 + $1.50 scan	50 ms	break-even on cost
MV + BIE for 1 dashboard × 30 refreshes	$15.40 + $0.05 scan	50 ms	over-provisioned

Rule of thumb. BI Engine pays back on latency when sub-100 ms is required. It pays back on cost only when ≥10 dashboards or ≥1,000 refreshes/day share the reservation. Below those thresholds, MV alone is the right answer.

Worked example — the hybrid pattern (BI vs ad-hoc)

Detailed explanation. A data platform has 10 production dashboards (high refresh, stable cohort) and 50 data scientists (ad-hoc, ever-changing queries). The hybrid pattern: MV + BI Engine on the production path, partitioned + clustered base tables for ad-hoc.

Question. Design the storage + acceleration layout for a team with both production dashboards and ad-hoc data science on the same fact table.

Input.

Workload	Refreshes/day	Cohort	Latency need
10 production dashboards	600	stable	100 ms
50 data scientists	~200 ad-hoc queries	ever-changing	5–30 s

Code.

-- Production path
CREATE MATERIALIZED VIEW `proj.analytics.events_daily_country`
PARTITION BY day CLUSTER BY country
OPTIONS(max_staleness = INTERVAL 30 MINUTE)
AS SELECT country, DATE(event_ts) AS day, SUM(revenue) AS rev
FROM `proj.analytics.events_partitioned` GROUP BY country, day;

-- BI Engine for production dashboards
-- gcloud alpha bi-reservations update --location=us --reservation_size=24

-- Ad-hoc path — hit the base table directly
SELECT * FROM `proj.analytics.events_partitioned`
WHERE event_ts BETWEEN '2026-06-01' AND '2026-06-10'
  AND <whatever-the-scientist-wants>;
-- No MV substitution; partition + cluster pruning still applies

Step-by-step explanation.

Production dashboards select from the base table; the planner rewrites to the MV; BI Engine pins the MV columns. 600 refreshes/day, 100 ms p99, ~$0 scan + $23/day reservation.
Data scientists run ad-hoc queries directly against the partitioned + clustered base table. No MV substitution (their queries don't match the MV's GROUP BY shape). They get partition + cluster pruning → 5–30 s latency on multi-TB exploration; pay-per-byte.
The hybrid means BI Engine does not pin ad-hoc columns. Predictable cache behaviour, predictable reservation cost.
The two workloads coexist on one base table without performance interference — BigQuery's slot scheduler isolates the ad-hoc scans from the BI Engine-served dashboards.
The cost shape: production = $23/day fixed (BIE + MV refresh). Ad-hoc = variable, billed by scan. The platform team can chargeback ad-hoc users by query while keeping production cost predictable.

Output.

Workload	Path	Daily $ shape	Latency
Production dashboards	MV + BIE	$23 fixed	50 ms
Ad-hoc data science	partition + cluster only	$5–$50 variable	5–30 s
Combined	hybrid	$23 + variable	both paths get their targets

Rule of thumb. Build the production stack (MV + BIE) for predictable cohorts. Leave ad-hoc on partition + cluster only. Don't over-engineer the ad-hoc path; data scientists prefer raw table access with predictable pricing.

Senior interview question on the MV / BIE cost decision

A senior interviewer might frame this as: "A finance partner asks why your team spent $4,000/month on BigQuery dashboards last quarter and what would happen if we cut the BI Engine reservation. Walk me through the answer."

Solution Using the 5-axis decision matrix + dollar attribution

The finance answer — by line item
=================================

Current state ($4,000/month)
  MV refresh credits             $400  (10% — daily delta refresh on 4 MVs)
  Dashboard query scan ($/TB)    $200  (5% — incremental scan above MV)
  BI Engine reservation (24 GB)  $720  (18% — 24 × $30/GB/month)
  Slot reservation               $2,400 (60% — committed slots for refresh + scan)
  Storage                        $280  (7%)
  -------                        -----
  TOTAL                          $4,000

If we cut BI Engine (24 GB → 0 GB)
  Save                           +$720/month
  Cost                           latency 50 ms → 200 ms on 6 dashboards
                                 — 200 ms is still under the SLA for 5 of 6
                                 — exec scorecard SLA is 100 ms; breach
  Net                            save $720, breach 1/6 dashboard SLA

If we cut to 8 GB (24 → 8)
  Save                           +$480/month
  Cost                           bi_engine_mode goes FULL → PARTIAL on 2 dashboards
                                 latency 50 ms → 70-90 ms on those
                                 — within SLA for all 6
  Net                            save $480, no SLA breach

Recommendation
  Right-size to 8 GB reservation = $480/month saving, no SLA impact.
  Hold the MV layer at full strength.

Step-by-step trace.

Decision	Monthly $ change	Latency impact	SLA impact
Cut BIE entirely	−$720	50 ms → 200 ms	exec scorecard breach
Cut BIE to 16 GB	−$240	50 ms → 55 ms	none
Cut BIE to 8 GB	−$480	50 ms → 80 ms	none
Cut to 4 GB	−$600	50 ms → 200 ms (PARTIAL)	likely breach
Cut MV layer	−$400	200 ms → 1.5 s on 6 dashboards	universal breach

The decision matrix produces an explicit, dollar-attributed answer. Finance gets a number; the team gets a defensible SLA-aware right-size; the dashboards keep their latency targets.

Output:

Line item	Now	After right-size	Save
BIE reservation	24 GB / $720	8 GB / $240	$480
MV refresh	$400	$400	$0
Scan	$200	$230	−$30
Slots	$2,400	$2,400	$0
Storage	$280	$280	$0
TOTAL	$4,000	$3,550	$450

Why this works — concept by concept:

Dollar attribution by layer — every BigQuery acceleration cost lands in one of five buckets: refresh, scan, BIE reservation, slot reservation, storage. Mapping the cost to the layer is the load-bearing finance answer.
SLA-driven right-sizing — don't cut BI Engine to zero; right-size to the smallest reservation that keeps all dashboards at bi_engine_mode = FULL within their latency SLA.
MV layer is rarely the cut — MV refresh credits are usually 5–15% of the bill; cutting them costs 5–10× more in scan billing.
Slot reservation dominates — at 60% of the bill, the slot reservation is the bigger lever. Right-size slots based on the 95th-percentile concurrent slot demand, not the peak.
Cost — the right answer is "right-size, don't eliminate." Cutting layers entirely breaks SLAs; right-sizing each layer to its real footprint trims 10–15% with zero performance impact.

SQL
Topic — etl
ETL design problems

Practice →

SQL
Topic — optimization
Query optimization problems

Practice →

Cheat sheet — MV + BI Engine recipes

The six-line MV. CREATE MATERIALIZED VIEW … PARTITION BY … CLUSTER BY … OPTIONS(max_staleness = INTERVAL N MINUTE, enable_refresh = true) AS SELECT … FROM base GROUP BY …. Anything beyond six lines is configuration, not DDL.
Aggregate-awareness rule. The planner rewrites a base-table query into an MV scan when the query's GROUP BY and aggregates match the MV's. Verify with EXPLAIN.query_plan.materializedViewUsed — never trust by inspection alone.
max_staleness is freshness vs cost. Tight max_staleness → fresh MV, frequent refresh, high refresh credits. Loose max_staleness → stale MV that the planner merges with base delta at query time. Pick the freshest the dashboard actually needs, not the freshest you can imagine.
MV refresh monitor query. SELECT table_name, refresh_watermark, TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), refresh_watermark, MINUTE) AS minutes_stale FROM <dataset>.INFORMATION_SCHEMA.MATERIALIZED_VIEWS. Alert when minutes_stale > max_staleness × 1.5.
Smart MV eligibility. GROUP BY + SUM/COUNT/AVG/MIN/MAX → always. HLL_COUNT.INIT for distinct counts → smart MV. Two-table JOIN + GROUP BY → smart MV (one base table on each side). ROW_NUMBER() → not eligible; use a scheduled query instead.
BI Engine reservation create. gcloud alpha bi-reservations update --location=<region> --reservation_size=<GB>. Per-region; no per-query SKU in 2026.
BI Engine right-size rule. ~1.5× the columnar size of the hot column set. Monitor bi_engine_mode = FULL rate; if below 90%, expand. If always 100% with low volume, shrink.
biEngineMode triage. FULL → all good. PARTIAL → look at biEngineReasons; usually a UDF, federated table, or under-sized reservation. DISABLED → no reservation in the query's region.
The four-layer cake order. Partition → Cluster → MV → BI Engine. Always partition first; always measure between layers; stop when the latency budget is met.
Sub-second runbook (5 steps). (1) PARTITION BY DATE(<ts>) + require_partition_filter = true. (2) CLUSTER BY <hot filters>. (3) CREATE MATERIALIZED VIEW matching the dashboard's GROUP BY. (4) Verify EXPLAIN.materializedViewUsed. (5) BI Engine reservation = 1.5× hot columnar set; verify bi_engine_mode = FULL.
MV break-even rule. > 5 refreshes/day on a stable cohort → MV pays back from day 1. < 1 refresh/day → on-demand scan may be cheaper than refresh credits.
BI Engine break-even rule. > 3 dashboards on the same hot column set or > 100 refreshes/day → reservation amortises. Below that, MV-alone is the right answer.
The hybrid pattern. MV + BIE on the production dashboard path; partition + cluster only on the ad-hoc data-science path. Two acceleration profiles, one storage layer.
Cost-attribution checklist. MV refresh credits + per-query scan + BIE reservation + slot reservation + storage. Right-size each layer independently; never cut layers entirely without re-measuring SLAs.

Frequently asked questions

When should I use a BigQuery materialized view?

Use a bigquery materialized view when (1) the dashboard's GROUP BY shape is stable, (2) the dashboard refreshes more than ~5 times per day on the same cohort, (3) the base table is large enough that scanning it directly is expensive (typically > 100 GB), and (4) the dashboard's filters align with columns you can PARTITION BY or CLUSTER BY on the MV. The MV pays back its refresh credits within a week for high-frequency stable-cohort dashboards. Avoid MVs for ad-hoc data-science queries where the GROUP BY changes every refresh — the planner can't pre-aggregate something it can't predict. Always pair the MV with max_staleness to control the freshness vs refresh-credit trade-off, and verify the planner is using your MV via EXPLAIN.query_plan.materializedViewUsed — silent fallback to base-table scan is the #1 reason teams say "MV didn't help."

BI Engine vs cached query result — what's the difference?

BigQuery's classic query result cache is a 24-hour key-value cache of full query result strings — identical queries return cached results for 24 hours, zero scan cost. BI Engine is a regional in-memory columnar cache that pins hot columns from hot tables, and accelerates queries with different filters on the same hot columns. The query-result cache only helps when the dashboard refreshes the exact same query repeatedly; the moment a filter changes, the cache misses. BI Engine survives filter changes — it pins columns, not queries — and accelerates any query whose columns are in the cache. The other big difference: query-result cache is free and automatic; BI Engine is a paid reservation. Together they're complementary — the result cache catches dashboards refreshing without parameter changes, BI Engine catches the rest.

How much does BI Engine cost?

bi engine pricing in 2026 is reservation-based, per GB-hour in the region. A typical US region runs around $0.04 per GB-hour ≈ $30 per GB-month. An 8 GB reservation in us-central1 ≈ $240/month; a 24 GB reservation ≈ $720/month. The reservation is billed continuously while it exists, regardless of query volume — pause the reservation on weekends if your dashboards don't run. There is no per-query SKU in 2026 (the older per-query pricing was retired). To right-size: estimate the columnar size of your hot column set (~20% of the MV's bytes is a good first cut), multiply by 1.5 for headroom, then verify bi_engine_mode = FULL on your top dashboards. If FULL rate drops below 90%, expand the reservation; if always 100% with low volume, shrink.

Can MVs replace incremental tables?

For aggregate dashboards — yes, often. A materialized view replaces the classic pattern of "build a daily roll-up table via an Airflow DAG, write it to a result table, point the dashboard at the result table" with a declarative CREATE MATERIALIZED VIEW. The MV refreshes automatically (no DAG), the planner rewrites base-table queries to use it (no dashboard change), and max_staleness is the freshness knob. Where MVs don't replace incremental tables: (1) windowed transforms (ROW_NUMBER(), ranking) that aren't smart-MV-eligible, (2) complex multi-step pipelines that the MV's single SELECT can't express, (3) cases where you want the result in a separate dataset for sharing or governance. The senior pattern: replace easy aggregate roll-ups with MVs, keep complex multi-step incremental tables as scheduled-query result tables.

Does my dashboard auto-use my MV?

Yes — that's the point of bigquery aggregate awareness. You do not change the dashboard's SQL after creating an MV. The dashboard continues to SELECT … FROM the_base_table; BigQuery's query planner compares the query's GROUP BY and aggregate functions to the catalogue of MVs on that base table and rewrites the query to scan the MV if there's a match. The substitution is silent and automatic. To verify: run EXPLAIN on the dashboard's query and look for query_plan.materializedViewUsed. If populated, the planner picked your MV. If null, either the query shape doesn't match (different GROUP BY keys, different aggregate functions, or a smart-MV-incompatible pattern), the MV is too stale (no max_staleness set or stale beyond it), or the MV's partition/cluster keys can't be pruned for the query's filters. Diagnose with INFORMATION_SCHEMA.JOBS_BY_PROJECT.query_info and adjust either the MV's keys or the dashboard's filter pattern.

How do I prevent MV refresh from blowing up credits?

bigquery mv refresh credits explode in two scenarios: (1) base table is appended in many tiny micro-batches throughout the day, firing the MV refresh hundreds of times; (2) max_staleness is too tight, forcing constant refresh ticks. Both are fixed by widening the refresh cadence. The simplest fix is OPTIONS(max_staleness = INTERVAL 30 MINUTE) — the planner will use the MV even at 30-minute staleness, with a small on-the-fly merge of the un-refreshed base delta for queries that need fresher answers. For very chatty base tables, also batch the producer's writes (DLT / MERGE every N minutes instead of streaming row-by-row). Monitor refresh cost via INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE statement_type = 'REFRESH_MATERIALIZED_VIEW' and alert when daily refresh credits exceed your scan-savings break-even. The break-even rule: refresh credits should stay below ~20% of the scan savings the MV provides — above that, widen max_staleness or fix the producer.

Practice on PipeCode

Drill the SQL practice library → for the SELECT / GROUP BY / WHERE / JOIN family that every BigQuery interview probes.
Sharpen the cohort-aggregate axis with the aggregation problem set → — the exact shape MVs pre-compute and BI Engine pins.
Pressure-test the dashboard-rewrite muscle on query optimization problems →, where partition pruning, cluster pruning, and plan-shape questions live.
Stack the ETL system design problem set → for the end-to-end pipeline questions that surround BI Engine acceleration.

Lock in MV + BI Engine muscle memory

BQ docs explain the DDL. PipeCode drills explain the decision — when MV pays back the refresh credits, when BI Engine replaces an extract, when all four layers stack into sub-second. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice aggregation problems →

1. Why MV + BI Engine are the BigQuery dashboard combo

Dashboards on raw fact tables scan the world; pre-aggregated MV + in-memory BI Engine collapses the scan to a memory lookup

Worked example — same dashboard, three execution shapes

Worked example — what counts as "MV-eligible" SQL

Worked example — verifying the planner used your MV

Senior interview question on the MV + BI Engine decision

Solution Using a 4-question MV / BI Engine framework

2. Materialized views — declarative pre-aggregates

bigquery mv is a stored aggregate the planner rewrites your base-table query into — six lines of DDL, automatic refresh, aggregate-aware substitution

Worked example — daily-revenue MV with planner rewrite

Worked example — max_staleness and the freshness vs cost knob

Worked example — smart MV with a join + HLL_COUNT

Senior interview question on MV refresh + planner rewrite

Solution Using a 5-step MV diagnostic runbook

3. BI Engine — in-memory acceleration layer

bigquery bi engine is a regional in-memory column-store cache automatically populated on hot queries — reservation-based capacity, planner-driven pinning, sub-100ms responses

Worked example — sizing a BI Engine reservation

Worked example — same dashboard, MV-only vs MV + BI Engine

Worked example — diagnosing a PARTIAL acceleration

Senior interview question on BI Engine reservation sizing

Solution Using a region-by-dashboard-QPS allocation model

4. MV + BI Engine + partitioning + clustering — the four-layer cake

Sub-second BigQuery dashboards come from stacking four layers in the right order — partition prune → cluster prune → MV substitute → BI Engine pin

Worked example — applying the runbook to a 12 TB events table

Worked example — the failure modes when layers are skipped

Worked example — Tableau dashboard hitting all four layers

Senior interview question on stacking the four layers

Solution Using the 4-layer cake with measure-then-add discipline

5. Cost / decision matrix

Senior engineers pick MV, BI Engine, or both from a 5-axis decision matrix — never from "everything always"

Worked example — when MV pays back

Worked example — when BI Engine pays back

Worked example — the hybrid pattern (BI vs ad-hoc)

Senior interview question on the MV / BIE cost decision

Solution Using the 5-axis decision matrix + dollar attribution

Cheat sheet — MV + BI Engine recipes

Frequently asked questions

When should I use a BigQuery materialized view?

BI Engine vs cached query result — what's the difference?

How much does BI Engine cost?

Can MVs replace incremental tables?

Does my dashboard auto-use my MV?

How do I prevent MV refresh from blowing up credits?

Practice on PipeCode

Lock in MV + BI Engine muscle memory

`bigquery mv` is a stored aggregate the planner rewrites your base-table query into — six lines of DDL, automatic refresh, aggregate-aware substitution

Worked example — `max_staleness` and the freshness vs cost knob

`bigquery bi engine` is a regional in-memory column-store cache automatically populated on hot queries — reservation-based capacity, planner-driven pinning, sub-100ms responses

Worked example — diagnosing a `PARTIAL` acceleration