Bigquery Partition Expiration Sql Configuration

What partition_expiration_days is:

In Google BigQuery, partition_expiration_days is a table-level option that defines the lifetime of individual partitions within a partitioned table. When configured, BigQuery automatically tracks the date or timestamp associated with each partition and deletes partitions that exceed the defined age limit.

How it meets the requirements:

• Automated granular cleanup (✓): Setting partition_expiration_days = 60 ensures that BigQuery automatically removes partition slices older than 60 days without requiring scheduled manual DELETE jobs or custom scripts.
• Preserves table structure (✓): Only expired partitions are removed; the table itself, its schema, and newer partitions remain completely intact and queryable.
• Cost reduction (✓): Automatically purging historical partitions decreases the active storage volume in BigQuery, directly lowering long-term storage expenses.

Key capabilities of partition expiration:

• Continuous evaluation: BigQuery continuously checks the partition date against UTC time and removes outdated partitions automatically.
• Table-level override: Applying partition expiration directly to a table overrides any default dataset-level partition expiration settings.
• SQL DDL support: The configuration can be updated dynamically at any time using standard ALTER TABLE ... SET OPTIONS syntax.
• Support for various partition types: Works seamlessly on ingestion-time partitioned, date/timestamp partitioned, and integer-range partitioned tables.

Why this is optimal:

Modifying the table options via SQL DDL provides a native, serverless, and declarative approach to lifecycle management. It eliminates the computational costs of running daily deletion queries and ensures reliable enforcement of organizational retention policies.