# 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.

Source: https://docs.querylab.io/bigquery/table-expiration/

Updated: 2026-10-14

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:

```sql
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](https://docs.querylab.io/schema-browser/ddl-operations/#alter-table-options)). 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

```sql
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

```sql
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](https://docs.querylab.io/bigquery/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:

```sql
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](https://docs.querylab.io/bigquery/snapshots-and-clones/) 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:

```sql
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:

| Table             | Options the table got                                      |
| ----------------- | ---------------------------------------------------------- |
| plain table       | `expiration_timestamp` = creation + 1 day                  |
| partitioned table | `partition_expiration_days = 3`, no `expiration_timestamp` |

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](https://docs.querylab.io/bigquery/time-travel/) window:

```sh
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:

**a temp table inside a script**

```sql
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
```

Result in BigQuery (2 rows):

| status | orders |
| --- | --- |
| paid | 2 |
| refunded | 1 |

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](https://docs.querylab.io/bigquery/temporary-tables/).

Check `expiration_timestamp` in `INFORMATION_SCHEMA.TABLE_OPTIONS` before you rely on a table.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Manage partitioned tables](https://cloud.google.com/bigquery/docs/managing-partitioned-tables)

- [Storage best practices](https://cloud.google.com/bigquery/docs/best-practices-storage)

- [Create table DDL and dataset options](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language)

- [Multi-statement queries](https://cloud.google.com/bigquery/docs/multi-statement-queries)

- [Data retention with time travel and fail-safe](https://cloud.google.com/bigquery/docs/time-travel)