A CTE referenced more than once may be evaluated more than once

What it is

A common table expression is a name for a query, not a temporary table. Each reference may be expanded and run on its own. The optimizer can reuse a result, but nothing guarantees it, so it is not something to rely on.

Why it costs money

• The mental model most people bring is "compute once, use many". BigQuery does not promise that.
• It can compound: a CTE reading a large table, referenced four times, may read that table four times. A dry run of the query shows whether it does.
• Query length hides it. Nobody counts CTE references in a 300-line query.

Worked example

WITH base AS (
  SELECT * FROM `analytics.events` WHERE event_date = CURRENT_DATE()
)
SELECT (SELECT COUNT(*) FROM base) AS total,
       (SELECT COUNT(DISTINCT user_id) FROM base) AS users,
       (SELECT SUM(revenue) FROM base) AS revenue

-- One pass:
SELECT COUNT(*) AS total,
       COUNT(DISTINCT user_id) AS users,
       SUM(revenue) AS revenue
FROM `analytics.events`
WHERE event_date = CURRENT_DATE()

How to fix it

1. 1Aggregate in a single pass where the query allows it.
2. 2If the intermediate result is genuinely needed several times, write it to a temporary table and query that.
3. 3For a repeated, expensive intermediate, a materialized view pays for itself quickly.