Bigquery Expiration And Tiered Storage Lifecycle

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.