# Temporary tables in BigQuery

> CREATE TEMP TABLE works only in a script or a session, takes an unqualified name, and keeps the table (and its storage bill) for up to 24 hours unless you drop it. When to use one instead of a CTE.

Source: https://docs.querylab.io/bigquery/temporary-tables/

Updated: 2026-10-14

A temporary table holds an intermediate result for the rest of a script, or for the rest of a [session](https://docs.querylab.io/bigquery/sessions/). You create it with `CREATE TEMP TABLE`, give it a bare name, and BigQuery puts it in a hidden dataset that only your script or session can see. Temp tables are the usual way to break a long transformation into steps that each read the result of the previous one.

## Only inside a script or a session

A single `CREATE TEMP TABLE` statement on its own is rejected:

**a temp table outside a script**

```sql
CREATE TEMP TABLE recent AS SELECT 1 AS id
```

BigQuery rejects this query:

```text
Use of CREATE TEMPORARY TABLE requires a script or session
```

Add another statement after it, and it becomes a script:

**two steps, each a temp table**

```sql
CREATE TEMP TABLE recent_orders AS
SELECT * FROM UNNEST([STRUCT('o1' AS id, 30 AS amount), ('o2', 75), ('o3', 120)]);
CREATE TEMP TABLE big_orders AS
SELECT * FROM recent_orders WHERE amount > 50;
SELECT * FROM big_orders ORDER BY id
```

Result in BigQuery (2 rows):

| id | amount |
| --- | --- |
| o2 | 75 |
| o3 | 120 |

## No dataset in the name

The name must be a single identifier. BigQuery chooses the dataset:

**a dataset-qualified temp table**

```sql
CREATE TEMP TABLE mydataset.recent AS SELECT 1 AS id;
SELECT * FROM mydataset.recent
```

BigQuery rejects this query:

```text
Query error: Temporary tables may not be qualified at [1:19]
```

Behind the scenes the table goes into a hidden dataset whose name starts with `_script`. You can write `_SESSION.my_table` to make it clear you mean the temporary table; the qualifier works in scripts as well as sessions.

A temp table with the same name as a table in the default dataset hides it: an unqualified name means the temporary table if one exists. `CREATE TABLE` without `TEMP` always creates a permanent table, even if a temporary one has that name.

## Dropping and replacing

`DROP TABLE` removes a temp table during the script, and the name is free again:

**drop and recreate under the same name**

```sql
CREATE TEMP TABLE scratch AS SELECT 1 AS id;
DROP TABLE scratch;
CREATE TEMP TABLE scratch AS SELECT 'new' AS label;
SELECT * FROM scratch
```

Result in BigQuery (1 row):

| label |
| --- |
| new |

`CREATE OR REPLACE TEMP TABLE` does the same in one statement.

## How long it lives and what it costs

The script can use the table until it ends. After that, nothing can query it by name, but BigQuery keeps the table for up to 24 hours and you can open it from the job's details in the console. You pay for its storage at the active storage rate until it is deleted, so drop large temp tables at the end of the script:

```sql
DROP TABLE IF EXISTS big_intermediate;
```

In Querylab.io, a script that creates a temp table and never drops it gets a warning on the `CREATE`, with a quick fix that adds the `DROP TABLE IF EXISTS` at the end ([Multi-statement queries](https://docs.querylab.io/writing-sql/multi-statement-queries/#using-temp-tables-across-statements)).

Writing a temp table costs nothing beyond the query that fills it. Reading it in later statements is billed like any table, with the 10 MB minimum per table per statement.

## Temp table or CTE

A `WITH` clause (a CTE) names a subquery inside one statement. BigQuery doesn't store a CTE's result: a non-recursive CTE that is referenced twice is computed twice. The bytes it reads from real tables are still billed once per statement, but the slot time is spent twice:

**one CTE used in two subqueries**

```sql
WITH expensive AS (
  SELECT x, x * x AS squared FROM UNNEST(GENERATE_ARRAY(1, 5)) AS x
)
SELECT
  (SELECT SUM(squared) FROM expensive) AS total,
  (SELECT MAX(squared) FROM expensive) AS biggest
```

Result in BigQuery (1 row):

| total | biggest |
| --- | --- |
| 55 | 25 |

Use a CTE when the steps fit in one statement. Use a temp table when:

- a later statement needs the result, for example a loop, an `IF` on its row count, or a `MERGE` from it;
- the intermediate result is expensive and several statements read it, so it is computed once instead of in each one;
- you want to check the intermediate data while debugging.

## Temp tables vs a staging dataset

Temp tables disappear on their own and can't be shared, which is what you want inside one job. When several jobs, or a scheduled query and its consumers, need the intermediate data, write it to a real table in a staging dataset with a [default table expiration](https://docs.querylab.io/bigquery/table-expiration/) instead.

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:

- [Work with multi-statement queries, temporary tables](https://cloud.google.com/bigquery/docs/multi-statement-queries#use_temporary_tables_in_a_multi-statement_query)

- [Introduction to sessions](https://cloud.google.com/bigquery/docs/sessions-intro)

- [BigQuery pricing, storage](https://cloud.google.com/bigquery/pricing#storage)