An enterprise is architecting a multi-tiered data storage and analytics platform on Google Cloud to handle hundreds of terabytes of transactional log data. The solution must satisfy the following architectural requirements: Raw Data Ingestion & Archival: Store immutable raw logs, transitioning them across storage tiers based on age, and retain them for 7 years for compliance at the lowest possible cost. Data Warehouse Performance: Provide sub-second query performance in BigQuery for analytical workloads that consistently filter by event timestamp and frequently slice or filter across customer_id, store_id, and product_category. Executive Dashboarding: Deliver frequently accessed, low-latency aggregated metrics with minimal compute scan cost and automatic incremental background updates without managing manual batch recomputation pipelines. Which multi-tiered storage and processing architecture should you implement?

Ingest raw logs into Cloud Storage Standard and apply Object Lifecycle Management to transition objects to Nearline, Coldline, and Archive storage over time. Ingest analytical data into a BigQuery table partitioned by day on the event timestamp and clustered by `customer_id`, `store_id`, and `product_category`. Create BigQuery Materialized Views for executive summary metrics.: ### What the Solution Is This architecture establishes an end-to-end multi-tiered storage strategy by integrating **Cloud Storage** for scalable, tiered data lifecycle management with **BigQuery** native storage optimized through date partitioning, multi-column clustering, and **materialized views**. ### How It Meets the Requirements - **Raw Data Ingestion & Archival**: Storing initial ingest files in **Cloud Storage Standard** enables high-throughput data pipelines. Configuring an **Object Lifecycle Management** policy transitions data into **Nearline** (30+ days), **Coldline** (90+ days), and **Archive** (365+ days) classes to minimize long-term cold retention costs while fulfilling compliance regulations. - **Data Warehouse Performance**: Partitioning the native BigQuery table by day on the **event timestamp** eliminates full table scans by pruning unneeded partitions. Adding **clustering** on `customer_id`, `store_id`, and `product_category` (up to four columns) collocates related data into organized storage blocks, dramatically improving filter and aggregation performance. - **Executive Dashboarding**: **Materialized views** precompute and cache aggregate query results in BigQuery managed storage. When underlying base tables are updated, BigQuery automatically updates the materialized view incrementally and transparently rewrites incoming queries to leverage precomputed results without requiring scheduled batch pipelines. ### Key Advantages - **Cost Optimization**: Transitions archival data to the lowest-cost object storage classes automatically. - **Partition Pruning & Block Elimination**: Maximizes slot efficiency and minimizes bytes scanned during ad hoc queries. - **Automatic Maintenance**: Eliminates maintenance overhead of custom recomputation pipelines by using native materialized view refresh. - **High Durability**: Retains raw immutable data across Google Cloud's highly resilient object storage architecture. ### Why This Is Optimal This architecture pairs the ideal services for each access pattern: Cloud Storage provides unbounded, tiered, cost-effective object storage, while BigQuery native tables with partitioning, clustering, and materialized views deliver maximum query performance and minimal query execution overhead.

Multi Tiered Storage Bigquery Cloud Storage Design | professional-cloud-data-engineer

Multi Tiered Storage Bigquery Cloud Storage Design

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 is architecting a multi-tiered data storage and analytics platform on Google Cloud to handle hundreds of terabytes of transactional log data. The solution must satisfy the following architectural requirements:

Raw Data Ingestion & Archival: Store immutable raw logs, transitioning them across storage tiers based on age, and retain them for 7 years for compliance at the lowest possible cost.
Data Warehouse Performance: Provide sub-second query performance in BigQuery for analytical workloads that consistently filter by event timestamp and frequently slice or filter across customer_id, store_id, and product_category.
Executive Dashboarding: Deliver frequently accessed, low-latency aggregated metrics with minimal compute scan cost and automatic incremental background updates without managing manual batch recomputation pipelines.

Which multi-tiered storage and processing architecture should you implement?

Ingest raw logs into BigQuery directly using logical storage billing. Export older data annually to Cloud Storage Standard using manual BigQuery extract jobs. Create a non-partitioned BigQuery table clustered by customer_id, and build BigQuery BI Engine SQL views for real-time aggregation.

Ingest raw logs into Cloud Storage Standard without lifecycle policies. Query the raw files directly in BigQuery using external tables, partitioning the external tables by customer_id and clustering by event timestamp. Create BigQuery standard (logical) views to serve executive dashboard aggregations.

Ingest raw logs into Cloud Storage Standard and apply Object Lifecycle Management to transition objects to Nearline, Coldline, and Archive storage over time. Ingest analytical data into a BigQuery table partitioned by day on the event timestamp and clustered by customer_id, store_id, and product_category. Create BigQuery Materialized Views for executive summary metrics.

Ingest raw logs into Cloud Storage Archive storage directly. Query the archive files using BigLake external tables. In BigQuery, create a table partitioned on customer_id and clustered by event timestamp, and use scheduled queries to refresh a summary aggregation table every 5 minutes.

Professional Cloud Data Engineer

Multi Tiered Storage Bigquery Cloud Storage Design

Related questions