A lead data engineer is designing a data lifecycle and cost-optimization architecture for an enterprise BigQuery data warehouse. The requirements are: All newly created partitioned event tables in the telemetry_events dataset must automatically delete individual daily partitions once they are older than 60 days, while retaining the base table structure for ongoing streaming ingestion. Any non-partitioned lookup tables created in the same dataset must be preserved indefinitely by default and automatically transition to BigQuery's lower-cost long-term storage tier after remaining unmutated for 90 days. High-priority analytics tables must be able to override the default partition retention to keep partitions for 180 days upon table creation. Which configuration should the data engineer implement?

Set default_partition_expiration_days = 60 on the dataset using ALTER SCHEMA SET OPTIONS, leave default table expiration unset at the dataset level, and set partition_expiration_days = 180 on high-priority partitioned tables upon creation.: ### What the Solution Is This configuration applies **automatic partition expiration** at the BigQuery dataset level via SQL DDL (`ALTER SCHEMA SET OPTIONS`) while allowing **table-level overrides** and relying on BigQuery's native, zero-maintenance **long-term storage pricing** model. ### How It Meets the Requirements * **Automatic partition deletion after 60 days**: Setting `default_partition_expiration_days = 60` on the dataset ensures every newly created partitioned table inherits a 60-day Time-To-Live (TTL) for each individual partition without dropping the table itself or interrupting streaming writes. * **Indefinite non-partitioned tables & long-term storage**: Leaving the dataset's **default table expiration** (`default_table_expiration_ms`) unset guarantees that standard, non-partitioned tables never expire. When any table or partition in BigQuery is left unedited for **90 consecutive days**, BigQuery automatically transitions that data to **long-term storage**, reducing the storage price by approximately 50% with zero read performance degradation. * **Granular table-level override**: Setting `partition_expiration_days = 180` in the `OPTIONS` clause during table creation explicitly overrides the dataset-level default for those specific tables. ### Key Advantages and Capabilities * **Hierarchical precedence**: BigQuery evaluates expiration settings in a defined hierarchy where table-level partition settings always supersede dataset-level defaults. * **Continuous ingestion**: Partition expiration drops aged slices of data automatically in the background without dropping table metadata or schemas. * **Automated cost reduction**: Inactive historical tables automatically transition from active to long-term storage pricing without operational overhead or manual ETL export pipelines. * **Declarative infrastructure**: Expiration rules can be managed entirely through SQL DDL, `bq` CLI, Terraform, or client libraries. ### Why This Is Optimal This approach leverages native BigQuery metadata lifecycle policies and automated storage tiering, requiring zero scheduled queries, external workflow triggers, or Cloud Storage export processes.

Bigquery Expiration And Tiered Storage Lifecycle | professional-cloud-data-engineer

Bigquery Expiration And Tiered Storage Lifecycle

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.

A lead data engineer is designing a data lifecycle and cost-optimization architecture for an enterprise BigQuery data warehouse. The requirements are:

All newly created partitioned event tables in the telemetry_events dataset must automatically delete individual daily partitions once they are older than 60 days, while retaining the base table structure for ongoing streaming ingestion.
Any non-partitioned lookup tables created in the same dataset must be preserved indefinitely by default and automatically transition to BigQuery's lower-cost long-term storage tier after remaining unmutated for 90 days.
High-priority analytics tables must be able to override the default partition retention to keep partitions for 180 days upon table creation.

Which configuration should the data engineer implement?

Set default_table_expiration_ms to 60 days and default_partition_expiration_days to 60 on the dataset, and configure partition_expiration_days = 180 on high-priority partitioned tables upon creation.

Configure a scheduled Dataform workflow running daily SQL DML DELETE statements for partitions older than 60 days, and export inactive tables to Cloud Storage Coldline buckets after 90 days.

Set the dataset storage_billing_model to PHYSICAL and configure max_time_travel_hours = 48 to enforce partition drops, and create snapshots of historical tables to transition them to long-term storage.

Set default_partition_expiration_days = 60 on the dataset using ALTER SCHEMA SET OPTIONS, leave default table expiration unset at the dataset level, and set partition_expiration_days = 180 on high-priority partitioned tables upon creation.

Professional Cloud Data Engineer

Bigquery Expiration And Tiered Storage Lifecycle

Related questions