CTEs in BigQuery
A CTE is a named subquery in a WITH clause. BigQuery runs a non-recursive CTE once per reference, reads nothing for a CTE that is never used, and pushes outer filters into it, so partition pruning still works.
Practice this in free lessons on learn.querylab.io: Name a step with WITH and 1 more.
A common table expression (CTE) is a subquery with a name, written in a WITH clause in front of the query that uses it. It exists only for that one statement. BigQuery doesn’t store the result of a non-recursive CTE: Google’s reference says it runs once for each reference and is “useful primarily for readability”. You don’t pay for a CTE you never read, and a filter written outside a CTE still prunes partitions inside it.
The shape
Each CTE can read the ones defined before it, so a long query becomes a list of named steps:
WITH orders AS ( SELECT * FROM UNNEST([ STRUCT(1 AS id, 'ana' AS customer, 30 AS amount), (2, 'ana', 25), (3, 'ben', 15), (4, 'cy', 80) ]) ), per_customer AS ( SELECT customer, SUM(amount) AS spent FROM orders GROUP BY customer ) SELECT customer, spent FROM per_customer WHERE spent >= 50 ORDER BY spent DESC
| Row | customer | spent |
|---|---|---|
| 1 | cy | 80 |
| 2 | ana | 55 |
per_customer reads orders, and the final SELECT reads per_customer. Separate the CTEs with commas. There is no comma before the final query, and the final query is required.
What a CTE costs
Bytes are counted on the tables the whole statement reads. A CTE that nothing reads is removed, so this estimate is 0 bytes even though it says SELECT *:
WITH all_words AS ( SELECT * FROM `bigquery-public-data.samples.shakespeare` ) SELECT 'all_words is never read' AS answer
This query will process 0 B when run.
Reading a CTE twice doesn’t double the estimate. Both of these queries process 1,317,248 bytes:
WITH words AS ( SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` ) SELECT SUM(word_count) AS total FROM words
This query will process 1.3 MiB when run.
WITH words AS ( SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` ) SELECT (SELECT SUM(word_count) FROM words) AS total, (SELECT MAX(word_count) FROM words) AS biggest
This query will process 1.3 MiB when run.
The bill stays the same, but the work doesn’t. With two references, the CTE runs twice and uses slot time twice. If the CTE is an expensive join and several steps read it, compute it once in a temp table instead. On capacity pricing, where you pay for slots, that matters more than the bytes.
Two references can disagree
Because each reference runs separately, a non-deterministic function gives each one its own value. Both sides of this comparison read the same CTE, one row, and the values differ:
WITH picked AS (SELECT RAND() AS value) SELECT a.value = b.value AS same_value FROM picked AS a CROSS JOIN picked AS b
| Row | same_value |
|---|---|
| 1 | false |
The same applies to GENERATE_UUID(). If two parts of a query must see the same random values, write them to a temp table first.
Filters reach inside a CTE
A CTE is not a barrier for the optimizer. pageviews_2020 is partitioned by datehour, and these two queries process the same 5.5 GiB:
WITH views AS ( SELECT title, views, datehour FROM `bigquery-public-data.wikipedia.pageviews_2020` ) SELECT title, views FROM views WHERE datehour >= '2020-06-01' AND datehour < '2020-06-02'
This query will process 5.5 GiB when run.
WITH views AS ( SELECT title, views FROM `bigquery-public-data.wikipedia.pageviews_2020` WHERE datehour >= '2020-06-01' AND datehour < '2020-06-02' ) SELECT title, views FROM views
This query will process 5.5 GiB when run.
So you can keep a general CTE at the top and filter later. Column pruning works the same way: only the columns the final query needs are read, so SELECT * inside a CTE costs nothing extra when the outer query names its columns. Check this with the estimate rather than assuming it, because a filter can’t move past an aggregation or a window function that needs the other rows. In Querylab.io the status bar estimate updates as you type.
On a table with require_partition_filter, every reference needs its own filter. A self-join of a CTE was refused with WHERE a.d = '2024-01-05' and passed with AND b.d = '2024-01-05' added.
Order and scope
A CTE can only read CTEs defined above it. Referring to a later one fails, and the message talks about a table, because BigQuery looks for a real table with that name:
WITH totals AS (SELECT SUM(amount) AS total FROM orders), orders AS (SELECT 10 AS amount UNION ALL SELECT 20) SELECT total FROM totals
Table "orders" must be qualified with a dataset (e.g. dataset.table).
Adding the RECURSIVE keyword changes that. With WITH RECURSIVE, CTEs in the clause can see each other in any order, even when none of them is recursive:
WITH RECURSIVE totals AS (SELECT SUM(amount) AS total FROM orders), orders AS (SELECT 10 AS amount UNION ALL SELECT 20) SELECT total FROM totals
| Row | total |
|---|---|
| 1 | 30 |
Each name can appear once per WITH clause:
WITH orders AS (SELECT 1 AS id), orders AS (SELECT 2 AS id) SELECT id FROM orders
Duplicate alias orders for WITH subquery at [2:1]
A subquery can have its own WITH, and its names hide the outer ones inside that subquery only:
WITH totals AS (SELECT 'outer' AS source) SELECT (WITH totals AS (SELECT 'inner' AS source) SELECT source FROM totals) AS in_subquery, (SELECT source FROM totals) AS at_top_level
| Row | in_subquery | at_top_level |
|---|---|---|
| 1 | inner | outer |
A CTE also hides a real table with the same unqualified name, which is handy for testing a query against inline data. A CTE ends with its statement, so the second query in a script can’t see it:
WITH orders AS (SELECT 1 AS id) SELECT id FROM orders; SELECT id FROM orders
Invalid value: Table "orders" must be qualified with a dataset (e.g. dataset.table). at [3:1]
Syntax from other databases
Three habits from Postgres and SQL Server fail here. BigQuery has no column list after the name, so alias the columns inside the query:
WITH totals (customer, spent) AS (SELECT 'ana', 55) SELECT customer, spent FROM totals
Syntax error: Expected ")" but got identifier "customer" at [1:14]
It has no MATERIALIZED hint, because it never stores a non-recursive CTE:
WITH totals AS MATERIALIZED (SELECT 'ana' AS customer, 55 AS spent) SELECT customer, spent FROM totals
Syntax error: Expected "(" but got keyword MATERIALIZED at [1:16]
And WITH can’t sit in front of INSERT:
CREATE TEMP TABLE big_orders (id INT64); WITH orders AS (SELECT 1 AS id) INSERT INTO big_orders SELECT id FROM orders; SELECT id FROM big_orders
Syntax error: Unexpected keyword INSERT at [3:1]
It belongs to the query, so it goes after INSERT INTO. The same placement applies to CREATE TABLE ... AS WITH ... and to the source of a MERGE:
CREATE TEMP TABLE big_orders (id INT64); INSERT INTO big_orders WITH orders AS (SELECT 1 AS id) SELECT id FROM orders; SELECT id FROM big_orders
| Row | id |
|---|---|
| 1 | 1 |
CTE, view or temp table
| You need | Use |
|---|---|
| Named steps in one statement | CTE |
| A result read by several statements in a script | Temp table |
| Expensive work reused inside one statement | Temp table, so it runs once |
| The same logic in many queries | View |
| A precomputed result that stays fresh | Materialized view |
| Hierarchies of unknown depth | Recursive CTE |
Recursive CTEs are the exception to the storage rule: BigQuery materializes their results. Pipe syntax is another way to write a query as steps, and it can follow a WITH clause.