BigQuery CTEs: The Cost-Free Abstraction Nobody Understands

The Mental Model That’s Costing You Money

Most data engineers carry around an intuition inherited from traditional databases. In PostgreSQL or SQL Server, CTEs often act as optimization fences, the database materializes the result of each WITH clause into an internal temp table before moving to the next step. Chain three CTEs on a 30GB table, and you’re potentially storing 90GB of intermediate data, thrashing your buffer pool, and watching your query time balloon.

That mental model is dangerous when applied to BigQuery.

BigQuery does not materialize CTEs. Period. A CTE in BigQuery is syntactic sugar, a way to name a subquery for readability and organization. When the optimizer builds the physical execution plan, it inlines the CTE definition into every place it’s referenced. The same logic applies whether you write a chain of three CTEs or a single nested subquery.

One commenter on the original Reddit thread put it concisely: “CTEs don’t add cost, consider them a syntactic sugar. What does add cost is * instead of a small list of columns.”

This is correct, but it’s also incomplete. Let’s dig into the nuance.

What the Optimizer Actually Does With Your Query

When BigQuery receives your SQL, it doesn’t execute what you wrote. It compiles it. The process goes through two distinct phases:

1. Logical plan generation: The optimizer parses your SQL into an abstract representation of what needs to happen, joins, filters, aggregations, projections.
2. Physical plan generation: The optimizer figures out how to execute that logical plan, which shuffle strategies to use, how to parallelize operations across slots, and crucially, how to minimize data movement.

CTEs live entirely in the logical plan phase. By the time BigQuery builds the physical plan, your CTE has been inlined and optimized away. There is no intermediate materialization unless the optimizer explicitly decides it’s beneficial, and in the vast majority of cases, it doesn’t.

The query execution graph provided by Google Cloud shows you the physical plan stages. If you look at the execution graph for a query with five chained CTEs versus the same logic rewritten as nested subqueries, the physical plans will be identical. The stages, the bytes shuffled, the slot consumption, all the same.

The Real Cost Drivers (Hint: It’s Not CTEs)

So if CTEs are free, what’s actually burning your budget? BigQuery’s on-demand pricing charges based on bytes processed, the amount of data read from storage. This is where the real optimization opportunity lives.

Consider the junior engineer’s query pattern:

WITH temp AS (
  SELECT
    *,
    (some transformation) AS converted_date
  FROM source_table  -- 30GB table
),
temp2 AS (
  SELECT
    *,
    (using converted_date) AS converted_date1
  FROM temp
),
temp3 AS (
  SELECT
    *,
    (using converted_date1) AS converted_date2
  FROM temp2
)
SELECT * FROM temp3;

The optimizer sees SELECT * and reads all columns from source_table. Every column. Every byte. Even if the final output only uses six columns, BigQuery scanned them all. That * propagation through the CTE chain doesn’t make it worse, the optimizer sees through the chain and scans the source table once, but it does mean you’re paying to read data you don’t need.

The fix isn’t to eliminate CTEs. The fix is to stop using * in production queries.

WITH temp AS (
  SELECT
    id, customer_id, transaction_amount,
    PARSE_TIMESTAMP('%Y-%m-%d', raw_date) AS converted_date
  FROM source_table
),
...

By explicitly listing the columns you need, you reduce the bytes scanned. On a wide table with 200 columns where you only need 10, that’s a 95% reduction in billed bytes. That’s real money.

The SELECT * Debate: Nuance for the Nuance-Hungry

Now, the waters get muddy. A thread respondent pushed back: “This is not 100% correct, column pruning does not work perfectly in BigQuery on large tables with transformations. Explicitly name the columns you need and don’t use *.”

This is accurate. BigQuery’s column pruning (the optimization that eliminates unnecessary column reads) works reliably on simple projections but can break down when you introduce:

• Complex nested transformations (JavaScript UDFs, complex regex operations)
• Functions that depend on multiple columns (e.g., STRUCT constructors, ARRAY_AGG with complex expressions)
• SELECT DISTINCT or ORDER BY on specific columns that trigger full row reads

The behavior is inconsistent enough that another user reported: “I’ve run series of cost tests and concluded that there is some magic that triggers in the background that is not properly documented.”

The safest play: always be explicit about your columns in the final SELECT, even if you use * in CTEs during development. The optimizer is better at pruning in the final projection than in intermediate steps, and if the final output only needs a subset, the scanner can short-circuit reads earlier in the pipeline.

But here’s the kicker, BigQuery is generally more affected by partitions and clustered column ranges than by raw column count on narrow-to-medium tables. If your table has 30 columns and you’re reading all of them, worrying about * is premature optimization. If you’re reading 200 columns when you need 5, that’s a problem.

When CTEs Actually Hurt You (It’s Not Cost)

The biggest misconception is that CTEs only matter for cost. They matter for execution time and concurrency, especially under the capacity-based (slot reservation) pricing model.

Because BigQuery doesn’t materialize CTEs, referencing the same CTE multiple times in a query causes the optimizer to execute the underlying logic once per reference. There is no caching of intermediate results.

WITH expensive_agg AS (
  SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(amount) AS lifetime_value
  FROM massive_sales_table
  GROUP BY customer_id
)
SELECT
  a.customer_id,
  a.lifetime_value,
  b.total_orders_high_value
FROM expensive_agg a
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS total_orders_high_value
  FROM massive_sales_table
  WHERE amount > 1000
  GROUP BY customer_id
) b ON a.customer_id = b.customer_id;

In this pattern, massive_sales_table is scanned twice: once for the CTE and once for the self-join subquery. The CTE didn’t cause a materialization, but it also didn’t prevent the redundant scan. On a 30GB table, this doubles the bytes read.

Under on-demand pricing, this doubles your cost. Under capacity pricing, it doubles the slot time consumed, making your query slower and reducing available capacity for concurrent workloads.

The fix: use a materialized view or temporary table if you genuinely need to reuse expensive computations. BigQuery’s materialized views are incremental and automatically maintained, making them the right tool for caching aggregations that are referenced across multiple query paths.

The Execution Graph: Your Window Into the Physical Plan

If you want to see what’s actually happening, stop guessing and read the query plan. The execution graph provides stage-by-stage breakdowns:

• Input stages: bytes read from storage, which columns were accessed
• Shuffle stages: bytes transferred between workers (the expensive part of joins and aggregations)
• Output stages: bytes written to the result set

Key metrics to watch:

A CTE that’s properly inlined will show zero additional I/O compared to the flat query. If you see a stage reading the same table multiple times, the optimizer failed to eliminate redundancy, and you need to restructure your query.

The History of This Misunderstanding

The confusion around CTEs isn’t new. It dates back to the early days of SQL Server (2005), where CTEs were implemented as spools, the engine would write the CTE results to tempdb before proceeding. This created a persistent belief that CTEs are inherently materialized, a belief that carried over into PostgreSQL (CTEs as optimization fences until PG12) and even some modern warehouses.

BigQuery’s architecture is fundamentally different. It’s a distributed, columnar, disaggregated compute-and-storage system. Materializing intermediate results would require writing data back to Colossus (Google’s distributed filesystem), shuffling it across the network, and reading it again, a pattern that’s antithetical to BigQuery’s design goal of minimizing I/O.

Google’s Dremel paper (the research precursor to BigQuery) describes an execution model where operators are “pipelined” through the distributed tree as much as possible, with materialization happening only at stage boundaries when absolutely necessary (typically for shuffle operations in joins and aggregations).

Practical Takeaways for Data Engineers

1. Stop fearing CTEs for cost

They don’t add cost. They don’t multiply your data scan. Use them freely for organizing complex SQL logic, they make your queries readable, testable, and maintainable.

2. Fear SELECT * instead

The single biggest cost optimization for most BigQuery queries is explicit column lists. On tables with hundreds of columns, this is a 10x cost reduction. On narrow tables, the impact is minimal, test before optimizing.

3. Fear duplicate CTE references

If you reference the same CTE more than once, you’re scanning the underlying data multiple times. Use materialized views or temporary tables for truly reusable aggregations.

4. Read your query plan

The execution graph is free, accurate, and gives you stage-by-stage breakdowns. If a CTE is causing problems, the plan will show you exactly where. If it’s not (the usual case), the plan will confirm that.

5. Understand your billing model

Under on-demand pricing, bytes read is the only cost metric. CTEs don’t affect this. Under capacity pricing, slot time consumed matters, and redundant CTE references can degrade concurrency. Your optimization strategy changes based on how you’re paying.

The Bottom Line

The junior engineer who posted that Reddit thread was right to be thoughtful about query performance. That instinct, questioning whether abstractions have hidden costs, is what separates good data engineers from bad ones.

But the real lesson isn’t about CTEs. It’s about understanding the execution model of the tool you’re using. PostgreSQL’s engine, DuckDB’s engine (which follows a very different execution model), Snowflake’s engine, and BigQuery’s engine all handle CTEs differently. The assumptions you carry from one don’t transfer to another.

BigQuery’s approach is simple: CTEs are free until you reference them twice. Write clean, readable SQL. Be explicit about columns. Read the execution plan. And stop worrying about whether your fifth WITH clause is the one that breaks the bank.

It’s not.