An e-commerce enterprise generates several terabytes of transactional sales data daily. The data analytics team needs to execute complex SQL queries and aggregate calculations across specific data columns to generate daily business intelligence reports. The workload has the following requirements: The most recent 90 days of sales records require high-performance, ad hoc analytical SQL queries. Sales data older than 90 days is queried infrequently but must be preserved durably for audit and disaster recovery compliance. The architecture must minimize ongoing storage costs without manual administrative intervention. Which storage and analytics architecture should you recommend?

Load data into date-partitioned BigQuery tables for columnar SQL analytics, utilize BigQuery's automatic transition to long-term storage pricing for unmodified partitions older than 90 days, and export compliance backups to Cloud Storage Coldline with Object Lifecycle Management.: ### What BigQuery and Cloud Storage Lifecycle Management Provide **BigQuery** is an enterprise, serverless cloud data warehouse built for large-scale analytics, utilizing a proprietary **columnar storage format (Capacitor)** and a distributed query execution engine (**Dremel**). **Cloud Storage** provides scalable, highly durable object storage with distinct storage classes—such as **Coldline** and **Archive**—governed by automated **Object Lifecycle Management** policies. ### How it Meets the Stated Requirements - **Ad Hoc Analytical SQL Performance:** BigQuery stores table data in columnar format, scanning only the specific columns referenced in aggregation queries and leveraging **date partitioning** to prune unnecessary data partitions. - **Cost-Effective In-Place Storage:** BigQuery automatically transitions any table partition that has not been modified for **90 consecutive days** into **long-term storage**, lowering storage pricing by approximately 50% with zero degradation in query performance. - **Durable Compliance Backups:** Exporting batch snapshots or backup partitions to **Cloud Storage Coldline** satisfies regulatory archive needs at a low cost, while **Object Lifecycle Management** automates file transitions and eventual deletions without operational overhead. ### Key Advantages and Capabilities - **Separation of Compute and Storage:** Allows analytical datasets to scale to petabytes seamlessly without paying for idle compute nodes. - **Automated Storage Tiering:** BigQuery manages the lifecycle between active and long-term storage automatically per partition. - **Predictable Query Costs:** Partitioning and clustering minimize the physical bytes scanned during analytical execution. - **Flexible Disaster Recovery:** Direct table exports to Cloud Storage enable interoperable long-term backup archives. ### Why This Is Optimal This architecture directly addresses high-throughput columnar analytics while fully utilizing native Google Cloud billing models and storage lifecycle rules, eliminating manual maintenance scripts and compute provisioning.

StudyCerts

Bigquery Analytical Storage Lifecycle Optimization | Intrigued by the art of cloud architecture? Discover how to design, develop, and manage robust, secure, scalable, and dynamic solutions on Google Cloud as you prepare for the Professional Cloud Architect exam! | StudyCerts

Professional Cloud Architect

Intrigued by the art of cloud architecture? Discover how to design, develop, and manage robust, secure, scalable, and dynamic solutions on Google Cloud as you prepare for the Professional Cloud Architect exam!

Intermediate

Prepare and test your skills

Track Readiness

Set Exam Goal

Intermediate

Prepare and test your skills

Track Readiness

Set Exam Goal

Bigquery Analytical Storage Lifecycle Optimization

Section: Choosing appropriate storage types (e.g., object, file, and databases)

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 e-commerce enterprise generates several terabytes of transactional sales data daily. The data analytics team needs to execute complex SQL queries and aggregate calculations across specific data columns to generate daily business intelligence reports.

The workload has the following requirements:

The most recent 90 days of sales records require high-performance, ad hoc analytical SQL queries.
Sales data older than 90 days is queried infrequently but must be preserved durably for audit and disaster recovery compliance.
The architecture must minimize ongoing storage costs without manual administrative intervention.

Which storage and analytics architecture should you recommend?

Ingest transactional records into Cloud Bigtable with single-row key indexing, execute analytical SQL aggregation jobs via Bigtable compute nodes, and configure table garbage collection policies to export historical rows to Cloud Storage Standard.

Store raw transaction files exclusively in Cloud Storage Standard, create BigQuery external tables to query the data in place, and apply bucket lifecycle rules to transition objects to Nearline storage after 90 days.

Deploy a Cloud SQL for PostgreSQL database instance with regional Persistent Disk SSD storage, perform analytical queries directly in the database, and configure scheduled SQL scripts to archive older tables to Filestore.

Load data into date-partitioned BigQuery tables for columnar SQL analytics, utilize BigQuery's automatic transition to long-term storage pricing for unmodified partitions older than 90 days, and export compliance backups to Cloud Storage Coldline with Object Lifecycle Management.

Professional Cloud Architect

Bigquery Analytical Storage Lifecycle Optimization

Related questions