Question

A retail analytics team runs a dashboard that refreshes every few minutes. The underlying query aggregates daily sales totals by store and product category from a large, continuously appended BigQuery fact table (billions of rows). The same aggregation is executed hundreds of times per hour, and the team is concerned about repeatedly scanning the full table and the associated query cost. They want the aggregated results to stay automatically up to date as new rows arrive, with minimal maintenance overhead. What should they do?

Accepted Answer

Create a BigQuery materialized view over the fact table that pre-aggregates sales by store and category, letting BigQuery incrementally maintain and automatically use it.

Answer

Create a standard (logical) view with the aggregation query so results are always computed against the latest data.

Answer

Schedule a query with Cloud Scheduler and Cloud Composer to rebuild an aggregate table from scratch every few minutes.

Answer

Export the fact table to Cloud Storage and use a BigQuery external table so the dashboard reads pre-aggregated files.