CTE vs Subquery: When the WITH Clause Is a Materialization Barrier

Someone on every team eventually says “CTEs are slower than subqueries” or the exact opposite, and both people have production evidence. They are describing different engines, or different versions of the same engine, and neither has looked at a query plan recently.

I have been on both sides of this. I once rewrote a chain of eight CTEs into nested subqueries on Postgres 11 and cut a report from 90 seconds to 4. Two years later I did the reverse migration on Postgres 14 and the plans were byte-for-byte identical, because the optimizer fence I had been working around no longer existed.

The question that actually matters is not syntax. It is whether the engine evaluates the named query once and stores the result, or folds its definition into the outer query and optimizes the whole thing as one statement. Everything else — pushdown, repeated scans, spill behavior — follows from that single decision.

This is a per-engine answer, and the engines disagree. Here is what each one does, how to check, and the three cases where I override the default.

Inlining versus materialization

Two strategies exist and the names are worth being precise about.

Inlining substitutes the CTE’s definition into the referencing query, then optimizes the combined statement. Filters from the outer query can push down into the CTE body. Column pruning applies. If the CTE is referenced twice, the definition appears twice in the plan and executes twice.

Materialization evaluates the CTE once into a temporary result — memory or spill — and every reference reads that result. No predicate can push into it, because it was computed before the outer filter existed. Referencing it five times costs one evaluation and five cheap reads.

flowchart TD
  cte["WITH big AS (SELECT ... FROM events)"] --> choice{"Engine strategy"}
  choice -->|Inlined| sub["Definition folded into outer query"]
  sub --> push["Filters and column pruning push down"]
  sub --> twice["Two references means two scans"]
  choice -->|Materialized| tmp["Evaluated once into temp result"]
  tmp --> fence["No predicate pushdown"]
  tmp --> once["Two references share one scan"]

Neither strategy wins universally. Cheap CTE referenced many times wants materialization; expensive CTE referenced once wants inlining.

Note what is missing from that diagram: any mention of subqueries. A derived table in the FROM clause is inlined on every engine I know of. So “CTE vs subquery” only becomes a real performance question on engines where CTEs are materialized. Everywhere else they compile to the same plan and you should pick the one that reads better.

Postgres: the version 12 dividing line

Postgres is the engine with actual history here.

Through Postgres 11, every WITH clause was an unconditional optimization fence. It was documented behavior, not a bug — the CTE was evaluated to completion, and no outer predicate reached inside. On a 500M-row events table, this pattern was a disaster:

-- Postgres 11 and earlier: scans all of events, then filters.
with recent as (
  select * from events        -- fully materialized, all rows
)
select * from recent
where event_ts >= current_date - 7;

Postgres 12 changed the default. A CTE is now inlined when it is non-recursive, side-effect free, and referenced exactly once. Reference it twice and Postgres still materializes it. You can also override in both directions:

-- Force materialization: evaluate once, share across references.
with expensive as materialized (
  select customer_id, sum(amount) as ltv
  from fct_orders
  group by customer_id
)
select a.customer_id, a.ltv, b.ltv
from expensive a
join expensive b on b.customer_id = a.parent_id;

-- Force inlining: let the outer WHERE push down.
with recent as not materialized (
  select * from events
)
select * from recent
where event_ts >= current_date - 7;

EXPLAIN tells you which happened. A CTE Scan node above a CTE recent subplan means materialized. If the CTE name does not appear at all and you see the base table scan with your filter attached, it was inlined. That check takes ten seconds and settles most arguments.

The migration lesson: if your codebase predates Postgres 12, somewhere in it is a CTE that was written as a deliberate fence to stop the planner from making a bad choice. Those become slow after the upgrade, and the fix is AS MATERIALIZED rather than reverting the whole rewrite.

Snowflake: always inlined, and it bites on reuse

Snowflake has no materialization hint for CTEs. A WITH clause is a named subquery that the optimizer inlines, and there is no syntax to tell it otherwise.

For a single reference this is ideal: filters push down, columns prune, and the plan is the same one you would get from a derived table. The trouble starts with reuse.

-- Looks like one scan. Is three.
with base as (
  select
    user_id,
    event_type,
    event_ts,
    properties:plan::string as plan
  from raw.app_events              -- 4 TB table
  where event_ts >= dateadd(day, -30, current_date)
)
select 'signup'   as k, count(*) from base where event_type = 'signup'
union all
select 'purchase' as k, count(*) from base where event_type = 'purchase'
union all
select 'churn'    as k, count(*) from base where event_type = 'churn';

The query profile shows three TableScan nodes on raw.app_events. Snowflake is smart enough to push each branch’s event_type filter down, so the scans are pruned, but you still pay three passes over the partition set that the date filter selected. On a table that size, that is three times the credits.

The fixes, in the order I try them:

-- 1. Conditional aggregation: one scan, no reuse needed.
select
  count_if(event_type = 'signup')   as signups,
  count_if(event_type = 'purchase') as purchases,
  count_if(event_type = 'churn')    as churns
from raw.app_events
where event_ts >= dateadd(day, -30, current_date);

-- 2. Explicit temp table when the shape genuinely needs reuse.
create or replace temporary table tmp_base as
select user_id, event_type, event_ts,
       properties:plan::string as plan
from raw.app_events
where event_ts >= dateadd(day, -30, current_date);
-- Then reference tmp_base as many times as you like.

Conditional aggregation solves this specific case and is almost always the better answer. When the reuse is structurally necessary — a self-join, a fan-out comparison, a cohort matrix — a temporary table is the honest tool. In dbt, that is the difference between ephemeral (inlined as a CTE, so reuse repeats work) and table or view materialization. I have seen teams stack five ephemeral models and wonder why the compiled SQL scans a source four times; the compiled SQL is right there in target/, and reading it is the fastest way to find out. The dbt incremental models post gets into when a boundary is worth persisting.

The general rule I use on Snowflake: a CTE referenced more than once over a table larger than a few hundred million rows should be a temp table. Below that, the inlining is free and the readability is worth more.

BigQuery: inlined, and the bytes tell you

BigQuery also inlines CTEs with no materialization hint. Same consequence — a CTE referenced twice is scanned twice — but here the feedback loop is better, because the UI shows bytes billed before you run and bytes processed after.

That number is the ground truth. If a CTE over a 900 GB table is referenced twice and the estimate says 1.8 TB, you have your answer without reading a plan.

BigQuery’s escape hatches are different from Snowflake’s:

-- Script-scoped temp table inside a multi-statement query.
create temp table base as
select user_id, event_type, event_ts
from `proj.raw.app_events`
where date(_PARTITIONTIME) between '2026-04-01' and '2026-04-30';

select event_type, count(*) from base group by event_type;
select user_id, count(*) from base group by user_id having count(*) > 100;

Two BigQuery-specific notes. First, WITH inside a scripted statement still inlines; only CREATE TEMP TABLE materializes. Second, materializing costs storage and a write, so for a CTE over an already-pruned partition it is often a loss. Check that your partition filter is doing its job before you reach for a temp table — a well-pruned scan repeated twice can be cheaper than one unpruned scan plus a materialization. That tradeoff is the partitioning and clustering conversation, not really a CTE conversation.

The pushdown case people miss

Inlining is what makes predicate pushdown possible, and pushdown is usually worth far more than avoiding a repeated scan. A materialized CTE that computes an aggregate over an entire table so the outer query can filter to one customer is the worst plan in this whole space.

-- If this materializes, it aggregates every customer,
-- then throws away all but one.
with ltv as (
  select customer_id, sum(amount) as total
  from fct_orders
  group by customer_id
)
select * from ltv where customer_id = 42;

-- Inlined, the engine pushes the filter into the scan
-- and aggregates one customer's rows.

On Snowflake and BigQuery this is fine automatically. On Postgres it depends on the reference count, and on Postgres 11 it never happened. This is exactly the case where an old AS MATERIALIZED left in place after an upgrade turns a millisecond query into a full-table aggregate.

Aggregates and window functions inside a CTE are also pushdown blockers on their own terms. A filter cannot generally push below a window function, because the window’s result depends on which rows are present. If you need WHERE customer_id = 42 to reach the scan, put it inside the CTE, not outside.

Choosing, in practice

My decision procedure, in order:

1. Write CTEs. Readability compounds across a codebase and most CTEs are referenced once, where the plans are identical.
2. Count references. One reference: stop, you are done. Two or more: continue.
3. Estimate the repeated work. Small source or well-pruned scan: still fine. Large scan or expensive aggregate repeated: continue.
4. Try to eliminate the reuse. Conditional aggregation, a window function, or a single pass with CASE often removes the second reference entirely and beats both alternatives.
5. Materialize deliberately. AS MATERIALIZED on Postgres, a temp table on Snowflake or BigQuery, a table materialization in dbt.

Step 4 is the one people skip. Most “I need this CTE twice” situations are a self-join or a filtered aggregate that a window function handles in one pass — the patterns in the window frames deep dive cover the common shapes.

Pitfalls

Assuming a CTE is a temp table. This is the single most common misconception in analytics SQL. On Snowflake and BigQuery it is not, and referencing it four times means four evaluations.

Carrying Postgres 11 rewrites into Postgres 14. Nested subqueries written to dodge the old fence are now just harder to read. And an explicit AS MATERIALIZED that was added as a workaround now blocks pushdown that would otherwise happen.

Materializing to fix a plan you never read. Adding a temp table because a query is slow, without checking the profile, moves the cost rather than removing it. You now pay a write plus the original scan.

A SELECT * CTE at the base of the stack. Inlining prunes columns, so a wide base CTE is harmless when inlined and expensive the moment you materialize it. The anti-patterns post covers what width costs on columnar storage.

Deep ephemeral chains in dbt. Five stacked ephemeral models compile into one enormous statement with the source repeated. Read the compiled SQL in target/ before you assume it is one scan.

Filtering outside a CTE that contains a window function. The predicate cannot push below the window, so you scan and sort everything, then discard. Move the filter inside.

FAQ

Is a CTE always slower than a subquery?

No. On every engine where CTEs are inlined — Snowflake, BigQuery, DuckDB, Postgres 12+ with a single reference — they compile to identical plans. The performance difference exists only when materialization behavior differs, which means Postgres 11 and earlier, or a CTE with multiple references.

How do I check what my engine did?

Postgres: EXPLAIN and look for a CTE Scan node, which means materialized. Snowflake: open the query profile and count TableScan nodes for the same table. BigQuery: compare bytes billed against the table size. All three take under a minute.

Should I use AS MATERIALIZED by default in Postgres?

No. The default heuristic — inline when referenced once — is right most of the time. Use the hint when you have measured a repeated expensive subplan, or when you need a fence around something with volatile functions.

Does dbt’s ephemeral materialization help performance?

It reduces object clutter, not work. Ephemeral models compile to CTEs and inherit the engine’s inlining behavior, so an ephemeral model referenced by three downstream models is inlined into each of them separately. For anything expensive, use table or view.

What about recursive CTEs?

Different animal entirely. WITH RECURSIVE is always materialized iteratively, because each step reads the previous step’s output. None of the inlining discussion applies — see the recursive CTE post for how those actually execute.

Do CTEs affect incremental model performance in dbt?

Only through the same inlining rules. What matters far more is whether your incremental predicate reaches the source scan. If a CTE blocks that pushdown, your incremental run scans the full table and the incremental strategy is decorative.

What this means for your pipelines

The framing “CTE or subquery” is the wrong axis, and arguing about it wastes review cycles. The axis that matters is how many times the engine evaluates a given piece of work, and you find that out by reading a plan, not by picking a syntax.

Default to CTEs, because a model that reads top to bottom is worth real money in maintenance and every engine that matters inlines a single-reference CTE into the same plan. When you reference one twice over a large source, treat that as a signal to look at the profile — and try to eliminate the second reference before you reach for a temp table, since conditional aggregation and window functions dissolve most of these cases entirely.

Keep one engine-specific fact in your head per warehouse you run. Postgres inlines single-reference CTEs since 12 and gives you both hints. Snowflake and BigQuery always inline and make you build the temp table yourself. That is the entire decision surface, and everything else is measuring.