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.

A temporary table holds an intermediate result for the rest of a script, or for the rest of a session. 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:

CREATE TEMP TABLE recent AS SELECT 1 AS id

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

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

No dataset in the name

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

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

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:

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

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:

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

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:

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

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