Bigquery Configure Partition Expiration Ddl

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.