An enterprise analytics team is designing a BigQuery data architecture for high-volume streaming telemetry events. The design must meet the following technical and compliance requirements: Telemetry data must be retained for exactly 90 days, after which expired data must be automatically and permanently removed without incurring continuous query scanning costs. Frequent downstream dashboard queries aggregate telemetry metrics by event_type and filter on specific user_id values over varying daily date ranges. The team wants to use a materialized view to accelerate precomputed aggregations while avoiding expensive full cache invalidations when data reaches the retention limit. How should you configure the base table and materialized view architecture to meet these requirements?

Create the base table partitioned daily by the event timestamp column with a 90-day partition expiration, clustered by user_id. Create a materialized view that groups by the daily date and event_type, partitioned by the same daily date column and clustered by user_id.: ### What Partitioned Tables and Aligned Materialized Views Are **Partitioned tables** in BigQuery physically segment data into distinct storage blocks based on time-unit columns, ingestion time, or integer ranges. When paired with **clustered columns**, BigQuery co-locates related data based on user-defined sort properties. A **materialized view** is a precomputed, cached result of a query that automatically stays synchronized with base table modifications and accelerates read performance via smart query rewrites. ### How It Meets the Requirements - **Automated 90-day compliance retention without query costs**: Configuring a **partition expiration** of 90 days on a daily partitioned base table instructs BigQuery to automatically delete expired daily partition blocks. This deletion occurs at the storage metadata level without executing `DELETE` DML queries, avoiding query compute charges. - **Optimized dashboard filtering and aggregations**: Daily partitioning segments the data by date, while **clustering by `user_id`** allows the query engine to prune unrelated storage blocks when filtering for specific users. Aggregating by `event_type` in the precomputed view satisfies frequent dashboard calculations. - **Avoiding full cache invalidations upon partition expiration**: Materialized views cannot have independent partition expiration settings; they implicitly inherit partition expiration from the partitioned base table. Because the materialized view is explicitly partitioned on the exact same daily date column and granularity as the base table, its partitions align and expire synchronously. This prevents full materialized view invalidation and eliminates expensive full refreshes. ### Key Advantages and Capabilities - **Zero-maintenance lifecycle management**: Base table and materialized view partitions expire in tandem at midnight UTC boundaries without administrative intervention. - **Granular block-level pruning**: Combining daily date partitioning with clustering on high-cardinality keys like `user_id` provides dual-layer data skipping during analytical query execution. - **Incremental refresh efficiency**: Materialized views partitioned identically to their base tables only need to incrementally process new partition inserts rather than recomputing the full dataset. - **Predictable cost governance**: Automated partition expiration avoids the continuous compute cost and slot consumption associated with recurring scheduled deletion jobs. ### Why This Is Optimal This architecture leverages native BigQuery storage features to enforce governance and compliance while maximizing query speed and minimizing query processing costs. Aligning the partition schemes between the base table and the materialized view is the only architecture that preserves materialized view cache stability under partition expiration policies.

Bigquery Table Architecture Lifecycle Partitioning | professional-cloud-data-engineer

Bigquery Table Architecture Lifecycle 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 analytics team is designing a BigQuery data architecture for high-volume streaming telemetry events. The design must meet the following technical and compliance requirements:

Telemetry data must be retained for exactly 90 days, after which expired data must be automatically and permanently removed without incurring continuous query scanning costs.
Frequent downstream dashboard queries aggregate telemetry metrics by event_type and filter on specific user_id values over varying daily date ranges.
The team wants to use a materialized view to accelerate precomputed aggregations while avoiding expensive full cache invalidations when data reaches the retention limit.

How should you configure the base table and materialized view architecture to meet these requirements?

Create a non-BigLake external table referencing Parquet files stored in Cloud Storage with an object lifecycle rule configured to delete objects after 90 days. Create an unpartitioned materialized view over the external table.

Create the base table partitioned daily by the event timestamp column with a 90-day partition expiration, clustered by user_id. Create an unpartitioned materialized view that aggregates by event_type and user_id, setting an explicit 90-day partition expiration in the view's DDL options.

Create an unpartitioned standard table clustered by event_type and user_id. Configure a daily Scheduled Query executing DML DELETE statements for rows older than 90 days, and create a logical view on top of the table.

Create the base table partitioned daily by the event timestamp column with a 90-day partition expiration, clustered by user_id. Create a materialized view that groups by the daily date and event_type, partitioned by the same daily date column and clustered by user_id.

Professional Cloud Data Engineer

Bigquery Table Architecture Lifecycle Partitioning

Related questions