Table and partition expiration in BigQuery

BigQuery can delete tables and partitions on a schedule. How a dataset's default table expiration, a table's expiration_timestamp and partition_expiration_days interact, what happens to rows in old partitions, and how long temporary tables live.

BigQuery has three expiration settings. A table’s expiration_timestamp deletes the whole table at a point in time. A table’s partition_expiration_days deletes each partition that many days after the partition’s period ends. A dataset can hold a default for each, which new tables inherit. If you set nothing, nothing expires.

Expire a table

Set it when you create the table, or later:

CREATE TABLE sales.scratch_orders
OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
AS SELECT * FROM sales.orders;

ALTER TABLE sales.scratch_orders
SET OPTIONS (expiration_timestamp = NULL);

NULL removes the expiration, and the table stays until you drop it. In Querylab.io, a table’s SQL Templates include ALTER TABLE OPTIONS, filled in with the table’s current description, labels and expiration (DDL operations). The value is a timestamp, not a duration, and the table is deleted with all its data when that moment passes.

A timestamp in the past is not an error. In our test, ALTER TABLE ... SET OPTIONS (expiration_timestamp = TIMESTAMP '2020-01-01 00:00:00+00') returned success, and the next query on the table said Not found. The table was gone at once.

Set a default for a dataset

CREATE SCHEMA sandbox
OPTIONS (default_table_expiration_days = 1);

ALTER SCHEMA sandbox
SET OPTIONS (default_table_expiration_days = 5);

Every table created in the dataset afterwards gets expiration_timestamp = creation time plus the default. Tables that already exist are not touched. We changed a dataset’s default from 1 day to 5, and the old table kept its 1-day expiration while a new table got 5.

Raising the default later doesn’t save a table that is about to expire, and lowering it doesn’t shorten one. The value doesn’t have to be a whole number: default_table_expiration_days = 3.75 is valid.

Use a short default on datasets for scratch work and query destination tables. Google’s storage guide recommends it for destination tables you don’t need to keep, so results don’t pile up in storage.

Expire partitions

CREATE TABLE sales.page_views (
  view_date DATE,
  url STRING
)
PARTITION BY view_date
OPTIONS (partition_expiration_days = 90);

Each partition expires on its own clock. The clock starts at the end of the partition, which for a daily partition is midnight UTC after that day. With partition_expiration_days = 90, the partition for 2026-10-01 expires at 00:00 UTC on 2026-12-31. A fractional value works too: 0.25 is 6 hours, so each daily partition expires at 06:00 UTC the next day. When the last partition goes, the table stays, empty.

The end-of-partition rule is easy to get wrong by a day. On a table with partition_expiration_days = 5, checked at 21:25 UTC on 2026-10-06, a row dated 2026-09-30 was gone (expired at 00:00 on 10-06) and a row dated 2026-10-01 was still there (expires at 00:00 on 10-07).

The setting works on ingestion-time and time-unit column partitions. On an integer-range partitioned table it fails with partition_expiration_days in table OPTIONS is only supported for time partitioned tables. See Partitioning.

Two of its behaviors surprise people.

Old rows disappear as you insert them

The expiration applies to every partition at all times, including rows you are loading now. A table with partition_expiration_days = 10 got three rows in one INSERT, dated 35 days ago, 5 days ago and today:

INSERT INTO sales.page_views_test VALUES
  (DATE '2026-09-01', 1), (DATE '2026-10-01', 2), (DATE '2026-10-06', 3);
-- Number of affected rows: 3

SELECT * FROM sales.page_views_test ORDER BY view_date;
-- 2026-10-01  2
-- 2026-10-06  3

The statement reported 3 affected rows, but the 35-day-old row was gone. A backfill into an expiring table silently drops everything older than the limit.

Shortening the expiration deletes now

Changing partition_expiration_days applies to every partition, whatever its age. Lowering it to 1 day on the table above left only the 2026-10-06 row. The partitions older than the new limit are expired straight away. So the change is a bulk delete. Check what it will remove first, and consider a snapshot beforehand.

Setting it to NULL removes the setting, and partitions then never expire. Negative values are rejected with partition_expiration_days value in table OPTIONS must be positive.

Expired partitions leave slowly

An expired partition is marked for deletion and a background process removes it, which Google says can take days. Until then it counts toward the table’s partition quota. To delete a partition immediately, remove it yourself: bq rm --table 'mydataset.mytable$20260901'.

Which setting wins

Setting both on the same table is allowed:

CREATE TABLE sales.events (event_date DATE)
PARTITION BY event_date
OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 5 DAY),
  partition_expiration_days = 7
);

Both options are stored, and the table expires after 5 days with all its partitions, since a table expiration takes precedence over the partition expiration. Anything the shorter limit deletes first is gone.

For defaults at the dataset level there is one more rule. We made a dataset with default_table_expiration_days = 1 and default_partition_expiration_days = 3, and created two tables in it:

With both defaults on the dataset, a partitioned table gets only the partition default. In a dataset that has just the table default, a partitioned table and a clustered table both got an expiration_timestamp for the whole table, one day out. Google’s storage guide says the table default “applies to the individual partitions” of a date-partitioned table. In our test it expired the table as a whole, and the partition default is what applies to partitions.

An explicit option on the table always overrides the dataset default.

Restoring an expired table

An expired table is recoverable like a dropped table, for the duration of the dataset’s time travel window:

bq cp mydataset.scratch_orders@-3600000 mydataset.scratch_orders_back

The copy is a new table, so it takes the dataset’s current default expiration, not the old table’s. In our test the restored table had an expiration 5 days out, the dataset’s default at that moment. Remove or reset the expiration on the copy if you want it to stay.

After a partition expires, its data is held in time travel and then fail-safe, and on physical billing that is storage you pay for.

Temporary tables

A CREATE TEMP TABLE in a script needs no expiration setting:

CREATE TEMP TABLE staging_orders AS
SELECT * FROM UNNEST([
  STRUCT(1 AS order_id, 'paid' AS status),
  STRUCT(2, 'refunded'),
  STRUCT(3, 'paid')
]);

SELECT status, COUNT(*) AS orders
FROM staging_orders
GROUP BY status
ORDER BY status

The table lives in a hidden dataset BigQuery manages (the name starts with _script). It is available for the duration of the script, and exists for up to 24 hours after the script finishes. Don’t put a dataset in its name. In a session, temporary tables can be used for as long as the session lasts, and BigQuery ends a session after 24 hours of inactivity or 7 days after it started. You pay for the storage of temporary tables while they exist. See Temporary tables.

In Querylab.io

The cost estimate in the editor counts bytes processed, not expiry, so a table that is about to expire reads at the usual price. Check expiration_timestamp in INFORMATION_SCHEMA.TABLE_OPTIONS before you rely on a table.