An enterprise retail analytics platform stores multi-terabyte transactional order data in Google Cloud BigQuery. Historical workload analysis reveals two primary query patterns: Ad-hoc analysis: Analysts frequently filter queries across multi-year histories by order_date, with selective filtering on customer_id and store_id. Executive BI dashboards: Dashboards repeatedly execute aggregation queries (COUNT and SUM of sales_amount) grouped by order_date, store_id, and product_category throughout the business day, causing slot contention. The base table is configured with daily partition expiration to automatically purge data older than two years. You need to design a storage and query acceleration architecture that optimizes query pruning, accelerates dashboard rendering, minimizes slot consumption, and ensures analytical caches do not get completely invalidated when base table partitions expire. Which architecture should you implement?

Partition the base table by `order_date` at daily granularity and cluster it by `customer_id` and `store_id`. Create a materialized view that precomputes the aggregated metrics grouped by `order_date`, `store_id`, and `product_category`, partitioned by `order_date` and clustered by `store_id`.: ### What the Solution Is This architecture combines **table partitioning**, **multi-column clustering**, and an **aligned partitioned materialized view** in BigQuery to optimize both granular ad-hoc filtering and recurring aggregate dashboard queries while preserving compute resources. ### How It Meets the Requirements - **Optimizes query pruning & ad-hoc performance**: Partitioning the base table by `order_date` allows BigQuery to prune unneeded partitions during scans. Clustering by `customer_id` and `store_id` sorts the data blocks within each partition, allowing fine-grained block skipping during selective filtering. - **Accelerates BI dashboards & reduces slot usage**: The **materialized view** precomputes the `COUNT` and `SUM` aggregations grouped by `order_date`, `store_id`, and `product_category`. When dashboards run, BigQuery uses smart tuning and incremental delta computation to serve results from precomputed storage rather than scanning raw base tables, drastically reducing slot consumption. - **Prevents full cache invalidation on partition expiration**: By configuring the materialized view with identical daily partitioning on `order_date`, the materialized view partitions **align synchronously** with base table partitions. When a base table partition expires, only that specific partition is dropped in the view, avoiding full view cache invalidation and expensive full refreshes. ### Key Advantages and Capabilities - **Automatic Query Rewrite**: BigQuery automatically routes matching queries to the materialized view even if the query explicitly references the underlying base table. - **Smart Incremental Refresh**: Materialized views read only recent delta changes from base tables instead of reprocessing entire datasets. - **Collocated Storage Blocks**: Clustering ensures co-located physical storage blocks for high-cardinality keys like `customer_id`. - **Aligned Partition Lifecycle**: Inherits base table partition expiration policies seamlessly without extra maintenance jobs. ### Why This Is Optimal This design targets both access tiers: selective base table scans benefit from partition pruning and clustering block-skipping, while repeated dashboard aggregations leverage zero-maintenance, partitioned materialized views.

Optimize Bigquery Schema Materialized Views Partitioning | professional-cloud-data-engineer

Optimize Bigquery Schema Materialized Views Partitioning

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

An enterprise retail analytics platform stores multi-terabyte transactional order data in Google Cloud BigQuery. Historical workload analysis reveals two primary query patterns:

Ad-hoc analysis: Analysts frequently filter queries across multi-year histories by order_date, with selective filtering on customer_id and store_id.
Executive BI dashboards: Dashboards repeatedly execute aggregation queries (COUNT and SUM of sales_amount) grouped by order_date, store_id, and product_category throughout the business day, causing slot contention.

The base table is configured with daily partition expiration to automatically purge data older than two years. You need to design a storage and query acceleration architecture that optimizes query pruning, accelerates dashboard rendering, minimizes slot consumption, and ensures analytical caches do not get completely invalidated when base table partitions expire.

Which architecture should you implement?

Implement integer range partitioning on customer_id with daily clustering on order_date. Create a standard logical view that encapsulates dashboard queries and allocate a BI Engine reservation to the dataset.

Partition the base table by order_date at daily granularity and cluster it by customer_id and store_id. Create a materialized view that precomputes the aggregated metrics grouped by order_date, store_id, and product_category, partitioned by order_date and clustered by store_id.

Implement daily date-sharded tables (orders_YYYYMMDD) clustered by customer_id and store_id. Configure a Cloud Composer DAG to execute scheduled queries that populate summary aggregation tables for dashboards.

Partition the base table by order_date and cluster by customer_id. Create a non-partitioned materialized view for the aggregate dashboard queries and configure a manual 730-day partition expiration policy directly on the materialized view.

Professional Cloud Data Engineer

Optimize Bigquery Schema Materialized Views Partitioning

Related questions