An enterprise data lake ingests several terabytes of transactional logs daily into Cloud Storage before loading them into BigQuery for analytics. The engineering team has established the following operational and cost requirements: Raw files in Cloud Storage are actively analyzed for 30 days, infrequently accessed between 31 and 90 days, and accessed less than once a year after 90 days for regulatory compliance. Analysts primarily run ad-hoc analytics filtering by specific transaction date (event_timestamp) and slicing by merchant identifier (merchant_id). Accidental full-table scans from unconstrained user queries must be strictly prevented at the table level to eliminate runaway query processing costs. Which architectural strategy should you implement to optimize storage and query costs?

Configure Cloud Storage lifecycle rules to transition objects to Nearline at 30 days and Archive at 90 days. In BigQuery, create a table partitioned by day on `event_timestamp`, clustered by `merchant_id`, and configure the `require_partition_filter` option to true.: ### What the Recommended Architecture Is This solution combines **Cloud Storage Object Lifecycle Management** for automated storage class tiering with **BigQuery table partitioning**, **clustering**, and **partition filter enforcement** to minimize both storage and query compute expenditures across data lake environments. ### How It Meets the Requirements - **Cloud Storage Tiering**: Objects transition to **Nearline storage** at 30 days (matching infrequent access patterns) and **Archive storage** at 90 days (providing lowest-cost long-term retention for compliance). - **Query Optimization**: Partitioning by **`event_timestamp`** prunes scanned partitions to specific days, while clustering by **`merchant_id`** colocates related records within those partitions, significantly reducing scanned byte volume. - **Prevent Runaway Query Costs**: Enabling **`require_partition_filter = true`** enforces a mandatory predicate on the partition column at the table level, causing any ad-hoc query without a partition filter to fail immediately before scanning data. ### Key Advantages and Capabilities - **Automated Lifecycle Transitions**: Reduces data storage cost over time without requiring manual scripts or operational intervention. - **Compound Data Pruning**: Partitioning combined with clustering provides two-dimensional query pruning for fine-grained analytical queries. - **Guaranteed Cost Guardrails**: Table-level partition enforcement protects against inadvertent, costly full-table scans in on-demand billing models. - **Preserves Durability**: Transitioning storage classes preserves 11 9's of durability while cutting at-rest storage expenses by up to 80-90% in Archive class. ### Why This Is Optimal This design precisely aligns each storage lifecycle stage to access frequency thresholds while using native BigQuery features to minimize byte scans and enforce query governance directly at the storage schema boundary.

Optimize Cloud Storage Lifecycle Bigquery Cost Controls | professional-cloud-data-engineer

Optimize Cloud Storage Lifecycle Bigquery Cost Controls

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 data lake ingests several terabytes of transactional logs daily into Cloud Storage before loading them into BigQuery for analytics. The engineering team has established the following operational and cost requirements:

Raw files in Cloud Storage are actively analyzed for 30 days, infrequently accessed between 31 and 90 days, and accessed less than once a year after 90 days for regulatory compliance.
Analysts primarily run ad-hoc analytics filtering by specific transaction date (event_timestamp) and slicing by merchant identifier (merchant_id).
Accidental full-table scans from unconstrained user queries must be strictly prevented at the table level to eliminate runaway query processing costs.

Which architectural strategy should you implement to optimize storage and query costs?

Configure Cloud Storage lifecycle rules to transition objects to Nearline at 30 days and Archive at 90 days. In BigQuery, create a table partitioned by day on event_timestamp, clustered by merchant_id, and configure the require_partition_filter option to true.

Configure Cloud Storage lifecycle rules to delete raw files after 30 days and rely on BigQuery Time Travel for historical compliance. In BigQuery, create an unpartitioned table with search indexes and set project-level daily query quotas.

Configure Cloud Storage lifecycle rules to transition objects to Nearline after 90 days. In BigQuery, create an ingestion-time partitioned table with BI Engine reservations, and enforce the maximum_bytes_billed limit across all user sessions.

Use Storage Batch Operations to transition Cloud Storage objects directly to Coldline immediately upon arrival. In BigQuery, partition the table by integer range on merchant_id and cluster by event_timestamp, setting require_partition_filter to false.

Professional Cloud Data Engineer

Optimize Cloud Storage Lifecycle Bigquery Cost Controls

Related questions