Compute Now or Compute Later: A Data Engineer's Guide to Temp Tables, Views, and CTEs

A nightly pipeline job used to finish in twenty minutes. This week it's been timing out, and nobody touched the data volume — the input tables grew at their usual, unremarkable pace. What changed was smaller than that: someone added one more downstream step, and for convenience, it referenced an existing CTE that was already being used twice elsewhere in the same query.

That CTE wasn't materialized. So instead of computing its expensive aggregation once, the engine quietly recomputed it a third time — same logic, same cost, paid again. Three references, three executions, and a job that used to comfortably fit its window now doesn't.

Nobody wrote a slow query. Someone just picked the wrong tool for "I need this result more than once" — which is a decision every data engineer makes constantly, usually without thinking about it, between four constructs that all look similar on the surface and behave very differently underneath: temp tables, views, materialized views, and CTEs.

Contents

• What these four things actually are, together
• Meet the pipeline
• A framework for the decision
• Temp tables
• Views
• Materialized views
• CTEs
• Honorable mentions
• Choosing the right tool
• Wrapping up

A quick note on scope: code examples and default-behavior claims here are PostgreSQL-primary. SQL Server and Snowflake come up briefly where their behavior genuinely diverges — table variables, micro-partition isolation, automatic incremental refresh — but not as full three-way comparisons at every step.

What these four things actually are, together

Before the individual mechanics, it's worth seeing all four on one spectrum, because the differences between them come down to a single question: is the result computed fresh on every read, or computed once and read from storage?

Views and CTEs sit at the "recomputed" end — there's nothing to go stale, because nothing is stored. Temp tables and materialized views sit at the "stored" end — fast to read, but only as current as the last time they were built or refreshed. Every trade-off in this article is a variation on that one axis: freshness against speed, simplicity against reuse.

Meet the pipeline

One running example, used in every section below: raw event logs land in raw_events, and get aggregated into user_activity.

CREATE TABLE raw_events (
    user_id     INT,
    event_time  TIMESTAMP,
    event_type  TEXT,   -- 'login', 'click', 'purchase'
    page_url    TEXT
);

CREATE TABLE user_activity (
    user_id                INT PRIMARY KEY,
    last_active_timestamp  TIMESTAMP,
    total_clicks           INT
);

A pipeline turning raw, high-volume logs into a clean, queryable summary is one of the most common shapes in data engineering — and a good stand-in for why these four constructs exist at all: the raw table is too large and too messy to query directly every time someone wants an answer.

A framework for the decision

Rather than treating "which construct do I use" as a rule to memorize, it helps to run it through a repeatable process — the same five-step path works whether you're debugging a slow query or, as here, an oddly slow pipeline: Symptom → Diagnose → Candidates → Decide → Verify. Applied to the incident above:

1. Symptom — the nightly job blew past its usual runtime.
2. Diagnose — the query plan (or a simple EXPLAIN) shows the same CTE's definition appearing three separate times in the plan, each one re-running the full aggregation.
3. Candidates — force materialization with MATERIALIZED, or promote the intermediate result to an actual temp table.
4. Decide — if the result is only needed within this one query, forcing materialization is the smaller change. If later steps also need to inspect or index the intermediate result, a temp table is the better fit.
5. Verify — re-run EXPLAIN and confirm the aggregation appears once, not three times.

That's the lens for the rest of this article: each construct is a different answer to "how much do I want to pay, and when, for a result I need more than once?"

Temp tables

A temp table is a real, physical table — it just lives in a session-scoped or transaction-scoped namespace instead of your permanent schema.

How it works: the engine materializes the result set to disk or memory (engine-dependent), gives it a name, and drops it automatically at the end of the session or transaction. Behavior varies slightly by database — Postgres drops at session end by default, SQL Server's #temp tables behave similarly, Snowflake temp tables persist for the session.

Why data engineers use them:

• You need the intermediate result more than once, and recomputing it each time is expensive — exactly the failure mode from the hook above.
• You want to index or analyze the intermediate step — you can add indexes, run EXPLAIN, or check row counts against a temp table like any other table.
• You want accurate query plans. Once data is sitting in a real temp table, ANALYZE gives the optimizer real row counts and distributions to work from instead of guessing — the difference between an index scan and a sequential scan on everything downstream that joins against it.
• Multi-step transformation pipelines where each step depends on the last and the logic is too gnarly to express in a single query.
• Debugging — materializing an intermediate step to disk lets you inspect it directly instead of re-running a 200-line query to see one CTE's output.

Downsides: they take up actual storage and I/O, and they're session-scoped — not shareable across connections in most engines. Cleanup, though, is usually a non-issue: a session-scoped temp table and any indexes on it are dropped automatically when the session (or, with ON COMMIT DROP, the transaction) ends. No traces are left in permanent storage.

Vendor aside — Snowflake: it doesn't use traditional indexes at all, but a TEMPORARY TABLE isolates the data into its own micro-partitions, so staging work never touches your permanent tables' storage until you explicitly write to them.

A worked example: staging and merging with a temp table

Landing raw data, indexing and analyzing it, then merging the cleaned result into a permanent table — all inside one stored procedure, with zero manual cleanup:

CREATE OR REPLACE PROCEDURE stage_and_merge_events()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Drops automatically when the transaction commits
    CREATE TEMP TABLE tmp_raw_events (
        user_id    INT,
        event_time TIMESTAMP,
        event_type TEXT
    ) ON COMMIT DROP;

    -- In practice this would be a COPY or an insert from an external source
    INSERT INTO tmp_raw_events (user_id, event_time, event_type) VALUES
        (1, '2026-07-17 10:00:00', 'login'),
        (1, '2026-07-17 10:05:00', 'click'),
        (2, '2026-07-17 11:00:00', 'login'),
        (1, '2026-07-17 10:02:00', 'click');

    -- Index + ANALYZE so the optimizer has real stats for the merge below
    CREATE INDEX idx_tmp_user_id ON tmp_raw_events(user_id);
    ANALYZE tmp_raw_events;

    INSERT INTO user_activity (user_id, last_active_timestamp, total_clicks)
    SELECT user_id, MAX(event_time), COUNT(*) FILTER (WHERE event_type = 'click')
    FROM tmp_raw_events
    GROUP BY user_id
    ON CONFLICT (user_id)
    DO UPDATE SET
        last_active_timestamp = EXCLUDED.last_active_timestamp,
        total_clicks = user_activity.total_clicks + EXCLUDED.total_clicks;

    -- No DROP TABLE or DROP INDEX needed — ON COMMIT DROP handles both
END;
$$;

CALL stage_and_merge_events();

When the procedure finishes, tmp_raw_events and its index are gone — nothing to clean up, nothing left bloating temp storage.

Views

A view is a saved query — not data. It's a name for a SELECT statement that gets substituted in at query time.

CREATE VIEW active_users_last_30d AS
SELECT user_id, MAX(event_time) AS last_seen
FROM raw_events
WHERE event_time > CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id;

Every time someone queries active_users_last_30d, the database re-runs the underlying query against live data — including re-scanning all of raw_events for the last 30 days, every single time.

Why data engineers use them:

• Abstraction and reuse — hide a messy join or business logic (like "what counts as active") behind a clean, well-named interface so analysts don't need to memorize it.
• Access control — expose a subset of columns or rows (e.g., hide PII) without duplicating data or managing separate tables.
• Always fresh — since it queries live data, there's no staleness to worry about.

Downsides: no performance benefit — a view over an expensive aggregation is exactly as expensive as running that aggregation directly, every single time. Stacking views on views on views ("view soup") can produce query plans that are painful to optimize or even to read, since the database has to unravel several layers of substitution before it can plan anything.

Materialized views

A materialized view is the middle ground: defined like a view (a saved query), but stores its result physically, like a temp table.

CREATE MATERIALIZED VIEW daily_active_users AS
SELECT DATE(event_time) AS activity_date, COUNT(DISTINCT user_id) AS dau
FROM raw_events
GROUP BY 1;

-- Refresh on a schedule or after a load job
REFRESH MATERIALIZED VIEW daily_active_users;

How it works: the query runs once, the results are written to disk, and subsequent reads hit that stored data instead of re-running the query. It does not auto-update when raw_events changes — you, a scheduler, or a trigger have to explicitly refresh it.

Refresh caveat: Postgres supports REFRESH MATERIALIZED VIEW ... CONCURRENTLY, which avoids blocking reads during a refresh — but it requires a unique index to already exist on the materialized view, or the command fails outright. It's easy to add the materialized view first and only discover this the first time a refresh is attempted.

Why data engineers use them:

• Expensive aggregations that don't need to be real-time — dashboards, daily rollups, anything where "as of last night's refresh" is good enough.
• Serving layer for BI tools — analysts hit a fast, pre-computed table instead of hammering raw_events with the same heavy GROUP BY over and over.
• Decoupling compute from serving — run the expensive computation once during an off-peak batch window, then serve cheap reads all day.

Downsides: staleness (only as fresh as the last refresh), refresh cost (a full refresh on a huge table can be as expensive as the original query), and storage overhead. You're trading freshness and simplicity for speed.

Vendor aside: Snowflake and BigQuery both offer platform-managed automatic incremental refresh for materialized views — the staleness window still exists, it's just measured in the platform's refresh latency rather than a schedule you configure yourself.

CTEs (Common Table Expressions)

A CTE is a named, temporary result set scoped to a single query, defined with WITH.

WITH recent_activity AS (
    SELECT user_id, event_time
    FROM raw_events
    WHERE event_time > CURRENT_DATE - INTERVAL '30 days'
),
per_user_counts AS (
    SELECT user_id, COUNT(*) AS event_count
    FROM recent_activity
    GROUP BY user_id
)
SELECT ua.user_id, puc.event_count
FROM per_user_counts puc
JOIN user_activity ua ON ua.user_id = puc.user_id
ORDER BY puc.event_count DESC;

How it works: a CTE is essentially syntactic sugar for a subquery — it makes multi-step logic readable by letting you name each stage instead of nesting subqueries five levels deep. The part that actually matters for performance is this: a CTE is often closer to a macro than a materialized result. Many engines take the CTE's definition and copy it inline everywhere it's referenced.

That's the exact mechanism behind the article's opening incident: reference the same CTE three times, and the engine may re-run that logic three separate times rather than computing it once and reusing the result. Whether it inlines or materializes depends on the engine and, in Postgres 12+, on whether you force it with the MATERIALIZED keyword:

WITH recent_activity AS MATERIALIZED (
    SELECT user_id, event_time
    FROM raw_events
    WHERE event_time > CURRENT_DATE - INTERVAL '30 days'
)
SELECT * FROM recent_activity WHERE event_time > CURRENT_DATE - INTERVAL '7 days'
UNION ALL
SELECT * FROM recent_activity WHERE event_time <= CURRENT_DATE - INTERVAL '7 days';

This also affects the optimizer's accuracy. A temp table's data physically exists, so the engine can gather real statistics on it via ANALYZE and plan subsequent joins accordingly. A CTE has no such stored statistics to fall back on — the optimizer has to estimate what the intermediate result will look like, and a bad estimate can lead to a bad plan further down the query.

Recursive CTEs are the other major use case — hierarchical or graph-like data (org charts, bill-of-materials, category trees):

WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

Why data engineers use them:

• Readability — breaking a complex transformation into named, sequential steps within one query.
• One-off logic — nothing to persist or clean up; the CTE exists only for the duration of the query.
• Recursive/hierarchical traversal — there's really no other clean way to walk a tree in standard SQL.

Downsides: you generally can't index a CTE or gather statistics on it, and if the optimizer inlines a non-materialized CTE that's referenced multiple times, you can end up recomputing the same expensive logic more than once — the root cause of this article's opening incident. This is exactly why a temp table often outperforms a CTE for large, reused intermediate results: the temp table computes the data once, ANALYZE gives the optimizer real numbers to plan against, and every subsequent query hits pre-built indexes instead of re-deriving the same rows.

Honorable mentions

Derived tables / subqueries — an unnamed, inline version of a CTE (SELECT * FROM (SELECT ...) x). Same execution characteristics as a non-materialized CTE, just less readable once you nest more than one or two.

Table variables (SQL Server) — similar to temp tables but typically held in memory for smaller result sets, with different locking and statistics behavior. Good for small lookup sets inside a stored procedure, not for large intermediate results.

Window functions — not a storage construct at all, but worth mentioning because they replace a lot of the self-joins and correlated subqueries that used to require temp tables (running totals, rankings, LAG/LEAD for period-over-period comparisons). If you're building a temp table just to compute a rank or a running sum, a window function on the original query is usually the leaner option.

Choosing the right tool

Construct	Persists?	Indexable?	Refresh model	Best for
Temp table	Session/transaction	Yes	Manual (you rebuild it)	Multi-step ETL, debugging, reused intermediate results
View	No (logic only)	No (view itself)	N/A — always live	Abstraction, access control, always-fresh logic
Materialized view	Yes, until refreshed	Yes	Manual or scheduled	Expensive aggregates for dashboards/BI, non-real-time serving
CTE	Query duration only	No	N/A — recomputed each run	Readability, one-off logic, recursive/hierarchical queries

A rough decision process that holds up on most jobs:

1. Is this logic just for readability within one query, and cheap to compute? Use a CTE.
2. Do other people or tools need to query this same logic repeatedly, and it must always be current? Use a view.
3. Is the underlying computation expensive, and slightly stale data is acceptable? Use a materialized view with a sensible refresh schedule.
4. Are you inside a pipeline, need to reuse an expensive intermediate result multiple times in one session, or want to inspect/debug a step? Use a temp table.

None of these are mutually exclusive — a real pipeline often uses all four: temp tables to stage a multi-step transform, CTEs inside each step for readability, a materialized view to serve the final aggregate to a dashboard, and a view on top of that to control which columns downstream teams can see.

Wrapping up

That nightly job from the opening didn't need a faster server or a smarter query planner. It needed one keyword — MATERIALIZED — or one temp table standing between the raw logic and the third reference to it. The underlying tension across all four constructs is the same one that decision would have been weighing: compute now vs. compute later, readable vs. reusable. Views and CTEs favor simplicity and freshness at the cost of recomputation. Temp tables and materialized views favor speed at the cost of storage and staleness. Once you see it through that lens, picking the right tool for a given step in a pipeline becomes a lot more intuitive than memorizing rules.

---

Have a favorite use case for one of these that didn't make the list? Drop it in the comments.