Decide When to Materialize a DuckDB Intermediate Result

Version scope: DuckDB 2.0. The SQL in this article was run on DuckDB 2.0.0 against small synthetic fixtures.

Breaking a query into named steps makes it easier to read. It does not automatically tell you which intermediate results the engine will store or recompute. When a transformation appears several times, materialization becomes a question about work reuse, memory and optimization opportunities.

DuckDB documents CTE materialization and inlining behavior, as well as explicit MATERIALIZED and NOT MATERIALIZED hints. The optimizer's choice depends on the query, and a hint is not a universal performance improvement. DuckDB WITH clause

Start with the repeated business calculation

Imagine several downstream summaries that all begin with the same normalization of raw events. Materializing that normalized population may avoid repeating the transformation. It also creates an intermediate result that needs memory or storage and may include rows that a downstream filter would otherwise eliminate earlier.

Write down whether the intermediate result exists for readability, reuse within one statement or reuse across separate statements. These are different lifetimes. A CTE, a temporary table and a durable table should not be treated as interchangeable simply because each can give a subquery a name.

Here is an illustration using a temporary synthetic table named events with columns account_id, event_kind and amount_minor.

WITH account_totals AS MATERIALIZED (
  SELECT account_id, sum(amount_minor) AS amount_minor
  FROM events
  WHERE event_kind = 'purchase'
  GROUP BY account_id
)
SELECT 'positive' AS category, count(*) AS accounts
FROM account_totals WHERE amount_minor > 0
UNION ALL
SELECT 'nonpositive', count(*)
FROM account_totals WHERE amount_minor <= 0;

The intended intermediate has one row per account represented by a purchase event. An account with no purchases is absent. If the product needs all accounts, this query needs a separate account population and an explicit join. Materialization cannot repair that semantic omission.

Compare plans rather than assuming reuse

Create a baseline without the hint and inspect both plans. Then execute the variants safely on the same immutable fixture and compare results. Save the plans and record whether the expected intermediate is actually present.

Measure both elapsed time and resource use. A change that reduces repeated computation but increases temporary storage may be valuable for one batch worker and harmful for a highly concurrent service. If the result difference is small relative to normal variation, retain the simpler query unless there is another clear operational benefit.

Be cautious with volatile functions and external reads. Repeated evaluation can change values or observe different external state. The equivalence claim must cover semantics, not just matching results on one convenient fixture.

Materialize across statements only with a refresh contract

A temporary table can make several validation queries and reports use a captured set of rows. A durable table can support reuse across jobs. Both need explicit population and cleanup behavior; the durable version also needs freshness ownership.

Record the input snapshot, transformation revision, creation time and validation outcome. Decide whether a failed refresh leaves the prior accepted table available or blocks consumers. Do not publish a half-built intermediate under the same name as an accepted dataset.

Avoid using materialization as a default response to every slow query. First remove unnecessary columns, clarify filters and investigate join cardinality. Storing an unnecessarily large intermediate can preserve the very problem you wanted the optimizer to eliminate.

The decision is justified when you can name the work being reused, the lifetime of the result and the resource tradeoff. A CTE name improves explanation; a measured plan comparison justifies a materialization choice.

One grouped intermediate feeds two consumers. The diagram describes intended reuse, not a measured optimizer plan.