BigQuery tables don’t actually "delete" themselves when their expiration is hit; they become inaccessible and are eventually purged by Google’s internal processes.
Let’s see this in action. Imagine you have a table, my_dataset.my_table, and you want it to disappear after 24 hours. You can set this up directly in the BigQuery UI or via the bq command-line tool.
Here’s how you’d set an expiration of 24 hours (86400 seconds) on a table named my_dataset.my_table using bq:
bq update \
--time_partitioning_expiration 86400 \
my_dataset.my_table
Now, if you query my_dataset.my_table after 24 hours, you’ll get an error like Not found: Table my_project:my_dataset.my_table. The table data is gone, and the table schema is also removed. It’s as if it never existed. This automatic cleanup is managed by BigQuery’s underlying infrastructure, ensuring that storage costs don’t balloon with stale data.
The primary problem this solves is data lifecycle management and cost control. Without expiration policies, you’d manually need to track and delete old tables, which is error-prone and time-consuming, especially in large-scale data warehousing environments.
Internally, BigQuery uses a metadata store to track table properties, including the time_partitioning_expiration field. When you set this field, BigQuery registers a background process that monitors the table’s creation or last modification timestamp against the specified expiration duration. Once the duration is exceeded, the table is marked for deletion. This is not an immediate, synchronous operation. Instead, it’s a background cleanup task that gradually reclaims resources.
The "expiration" is actually a time_partitioning_expiration setting. This means it applies to tables that are partitioned by time (e.g., daily, hourly). If you’re not using time-based partitioning, you’d set a different property called default_table_expiration_days at the dataset level, or expiration_timestamp at the table level for non-partitioned tables. The time_partitioning_expiration is measured in seconds.
The most common way to manage table expiration is at the dataset level. This sets a default expiration for all tables created within that dataset, unless overridden by a specific table’s setting. This is incredibly useful for ensuring consistency across a data processing pipeline where you might be generating many intermediate tables.
Here’s how to set a default expiration of 7 days (604800 seconds) for all tables in my_dataset:
bq update \
--default_table_expiration 604800 \
my_dataset
When you set default_table_expiration on a dataset, any new tables created in that dataset will inherit this expiration. Existing tables are not affected. If you then create a table my_dataset.new_table after this setting, it will automatically expire in 7 days. If you later set a specific expiration on my_dataset.new_table using bq update --time_partitioning_expiration 86400 my_dataset.new_table, this specific setting will override the dataset’s default.
A subtle but important point is how expiration interacts with table updates. If a table has an expiration policy set, and you perform an operation that modifies the table (like inserting new data, or running a query that overwrites it), BigQuery often resets the expiration timer. This is because the system interprets the update as a signal that the table is still actively being used and its data is relevant. This behavior can be surprising if you expect a table to expire exactly 24 hours after its initial creation, regardless of subsequent activity.
The next concept you’ll encounter is the difference between table expiration and partition expiration, especially when dealing with large, time-partitioned tables where you might want to expire individual partitions rather than the entire table.