An organization ingests streaming telemetry data into a Google Cloud BigQuery dataset for real-time monitoring and reporting. The incoming records are stored in a table named telemetry_logs partitioned daily by ingestion timestamp. The data architecture team establishes the following retention and operational requirements: Telemetry data older than 60 days must automatically be purged to reduce ongoing storage costs. The telemetry_logs table structure, schema, and current partition data must remain continuously available for analytical queries and streaming ingestion. The configuration must be applied directly to the existing table using GoogleSQL Data Definition Language (DDL) without scheduled maintenance scripts. Which statement should you execute to satisfy these requirements?

ALTER TABLE myproject.mydataset.telemetry_logs SET OPTIONS (partition_expiration_days = 60);: ### What partition expiration is **BigQuery partition expiration** defines a time-to-live (TTL) for individual partitions within an ingestion-time or column-partitioned table. When partition expiration is configured, BigQuery automatically evaluates the age of each partition and deletes partitions whose age exceeds the specified threshold, without affecting the table schema or newer partitions. ### How it meets the requirements - **Automated purging of historical data:** Setting `partition_expiration_days = 60` ensures that any daily partition older than 60 days from its UTC date is automatically removed by BigQuery background processes. - **Table availability:** Unlike table-level expiration, partition expiration deletes only the aged data segments. The `telemetry_logs` table metadata, schema definition, permissions, and active partitions remain completely intact and available for ongoing streaming inserts and queries. - **Declarative DDL update:** Using `ALTER TABLE ... SET OPTIONS` applies the change directly to an existing table in standard GoogleSQL DDL without requiring scheduled queries, cloud functions, or pipeline orchestrators. ### Key advantages and capabilities - **Granular lifecycle control:** Manages retention at the partition level rather than forcing all-or-nothing table deletions. - **Cost efficiency:** Automatically shifts storage out of active billing without manual intervention or compute overhead for `DELETE` operations. - **Seamless schema continuity:** Ongoing streaming buffers and write pipelines do not experience schema recreation outages. - **Override hierarchy:** Explicit table-level partition expiration settings take precedence over dataset-level defaults. ### Why this is optimal This approach natively achieves partition-level lifecycle management directly within BigQuery's storage engine, minimizing administrative overhead, avoiding query slot consumption for manual deletion queries, and preventing accidental destruction of the underlying table entity.

StudyCerts

Bigquery Configure Partition Expiration Ddl | 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 Configure Partition Expiration Ddl

Section: Data retention and data lifecycle management

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 organization ingests streaming telemetry data into a Google Cloud BigQuery dataset for real-time monitoring and reporting. The incoming records are stored in a table named telemetry_logs partitioned daily by ingestion timestamp.

The data architecture team establishes the following retention and operational requirements:

Telemetry data older than 60 days must automatically be purged to reduce ongoing storage costs.
The telemetry_logs table structure, schema, and current partition data must remain continuously available for analytical queries and streaming ingestion.
The configuration must be applied directly to the existing table using GoogleSQL Data Definition Language (DDL) without scheduled maintenance scripts.

Which statement should you execute to satisfy these requirements?

ALTER SCHEMA myproject.mydataset SET OPTIONS (max_time_travel_hours = 1440);

ALTER TABLE myproject.mydataset.telemetry_logs DROP PARTITION WHERE _PARTITIONDATE < DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY);

ALTER TABLE myproject.mydataset.telemetry_logs SET OPTIONS (partition_expiration_days = 60);

ALTER TABLE myproject.mydataset.telemetry_logs SET OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 60 DAY));

Professional Cloud Architect

Bigquery Configure Partition Expiration Ddl

Related questions