BigQuery CTEs that scan the same table twice

A WITH recent AS (SELECT * FROM events WHERE event_date = @d) referenced in two branches looks like one scan. BigQuery may inline the CTE twice, and if both inlined reads miss a storage-level partition filter, or if the CTE itself was SELECT * FROM events with the filter applied later, you pay for events twice in one job even though the query text mentioned events only once.

WITH recent AS (
  SELECT * FROM `proj.analytics.events`
)
SELECT
  (SELECT COUNT(*) FROM recent WHERE event_name = 'view') AS views,
  (SELECT COUNT(*) FROM recent WHERE event_name = 'buy') AS buys;
-- Two READs of events in job_stages. Partition filter never pushed.

This is why SlashPeak looks at source-table READ stages in job_stages, not at whether the string events appears once in the query text. Counting aliases in SQL is how you miss the second read, and how you flag queries the optimizer actually scanned once; the plan is the only layer that matches the invoice.

DRY is still a good idea for humans, but it is not a cache. A CTE is a named subquery, and the optimizer is free to inline it, combine it, or, sometimes, materialize it; using the WITH keyword does not let you pick. You pick by CREATE TEMP TABLE, by a real table, or by writing a single aggregation that does not fork. The expensive shape is always the same: a wide base table, a CTE that does not prune or project, and two or more consumers, whether scalar subqueries, UNION branches, self-joins, or a join of the CTE to itself for a before-and-after. Each consumer can become a READ, so two READs of 4 TB is 8 TB of the same bytes. If the job is expensive, look at how many times the base table was read before you argue about style.

SELECT
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'buy') AS buys
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();
-- One storage READ. Filter on the partition column.
-- No CTE to inline, no second pass over 4 TB.

Cache hits are skipped. A second run that hits the result cache is not a re-scan. The finding is about one job's plan, grouped with other jobs that share the same normalized pattern. Recurring dashboards that inline twice a day are the ones that matter. A one-off notebook that inlined twice is a lesson, not always a sprint.

If the CTE is expensive and referenced more than once, materialize it: CREATE TEMP TABLE recent AS SELECT … WHERE event_date = @d, then query the temp table. Temp tables in BigQuery are still billed on the write, once. Two later reads of a 40 GB temp table beat two reads of a 4 TB heap. The write is the price of a contract the optimizer cannot inline away.

CREATE TEMP TABLE recent
PARTITION BY event_date
AS
SELECT event_date, event_name, account_id
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();

SELECT
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'buy') AS buys
FROM recent;

If you can keep it as a CTE, put the partition filter inside the CTE and reference only the columns you need. SELECT * in the CTE is how the second read stays wide even when the consumers only COUNT. Projection is part of the materialization decision. A thin CTE that still inlines twice may be cheap enough to leave alone. A fat CTE that inlines twice is a temp table.

PARTITION BY on the temp table is allowed and useful when the materialized result is still large and the downstream queries filter a date; do not copy 200 columns into the temp table out of habit, since the whole point is to pay for a narrow, pruned snapshot. If the temp table is 4 TB, you materialized the bug. Session temp tables die with the session, and scheduled queries and BI tools that cannot share a session cannot reuse yours; for those producers, the durable version of the same idea is a daily mart or a materialized view, not a WITH clause you hoped they would cache. Match the materialization lifetime to the consumers.

WITH recent AS (
  SELECT event_name
  FROM `proj.analytics.events`
  WHERE event_date = CURRENT_DATE()
)
SELECT
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'buy') AS buys
FROM recent;
-- Still may inline. If it does, each READ is one day and one column.
-- Check job_stages. If two fat READs remain, go back to TEMP TABLE.

When SlashPeak flags a pattern, the recommendation is materialize once so the source is scanned once. That might be a temp table in a script, a table in dbt, or a rewrite to a single pass with COUNTIF. The right shape is the one whose plan shows one READ of the heap. Do not stop at prettier SQL.

A reporting view that joins events to itself through two aliases is the same pattern with a friendlier name. The plan still shows two READs. The SQL the analyst wrote selected from v_daily_kpis and looked tiny. Expand the view in the console once, then decide whether a mart built once per day is cheaper than a view that re-scans 4 TB on every dashboard refresh.

Nested views compound this. v_kpis selects from v_events_star which selects star from events, twice, through a funnel CTE. Nobody in the Looker model owns that stack. job_stages still names events. SlashPeak still flags the pattern. The fix might be in a view you do not edit every day. Attribution to Looker tells you who runs it; the expanded SQL tells you what to break apart.

Authorized views and Looker derived tables are not exempt: they are SQL, and if they inline, they inline. If you need share-once semantics, you need a table, since views are aliases and should be treated as aliases when you read the invoice. A view that is a thin column contract (named columns, partition filter in the view body, no self-join) can still be the right BI interface. The dangerous views are the ones that encode business logic as multiple passes over the fact table; encode that logic in a scheduled model instead, and let the view read the model.

Count READ stages for the same table_id, not appearances of the name in SQL. A view that mentions events once and joins to a dimension that also sits on events under another dataset name is two tables in metadata and maybe two READs. The plan decides. Your grep does not.

Inlining is an optimizer default for a reason. Materializing a CTE always has a write cost and a memory footprint. For a 10 MB filter that is referenced twice, inlining can be cheaper than writing a temp table. For a 4 TB SELECT *, inlining is a disaster. The optimizer does not know your invoice anxiety. It knows cardinalities and slots.

You cannot reliably hint BigQuery to materialize a CTE the way some engines allow WITH … AS MATERIALIZED. The portable contract is CREATE TEMP TABLE or a durable table. Teams that copy Snowflake materialized-CTE folklore into BigQuery scripts learn this from the plan, usually after a spike.

Scalar subqueries are inlining magnets. Two (SELECT COUNT(*) FROM cte WHERE …) almost always become two consumers. Rewrite to one pass with COUNTIF or SUM(IF). That rewrite is smaller than a temp table and often faster. Reach for TEMP TABLE when the shared result is a chunky set that several joins need, not when you needed two counters.

-- Avoid: two consumers of an inlined CTE
SELECT
  (SELECT COUNT(*) FROM recent WHERE event_name = 'view'),
  (SELECT COUNT(*) FROM recent WHERE event_name = 'buy');

-- Prefer: one aggregation
SELECT
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'buy') AS buys
FROM recent;

UNION ALL of two queries that each select from the same CTE can double the read. UNION ALL of two queries that each select from events with different filters can also be two READs, because they genuinely are two scans. The CTE version is worse only because it looks like one; honest two-scan SQL at least warns the next reader.

When you are unsure, EXPLAIN or the job details plan in the console is the experiment. If you see two Input stages against the same table_id, you have the bug regardless of how DRY the SQL felt. SlashPeak automates that inspection across 30 days of jobs so you do not EXPLAIN every dashboard by hand.

N in the savings math is the max effective read count of a base table in the job. Two READs means about half the bytes were the extra pass; three READs means about two thirds. A funnel query that reads events for views, then for carts, then for purchases as three CTEs chained off SELECT * FROM events is an N=3 invoice in analytics language.

WITH base AS (
  SELECT * FROM `proj.analytics.events`
),
views AS (SELECT user_id FROM base WHERE event_name = 'view'),
carts AS (SELECT user_id FROM base WHERE event_name = 'cart'),
buys AS (SELECT user_id FROM base WHERE event_name = 'buy')
SELECT
  (SELECT COUNT(*) FROM views),
  (SELECT COUNT(*) FROM carts),
  (SELECT COUNT(*) FROM buys);
-- N≈3 READs if inlined. Savings ≈ 2/3 of bytes billed.

BigQuery sometimes reports similar tables omitted on a READ step instead of listing every clone. SlashPeak treats that omission as extra reads. The finding still names the table. You still materialize once. Do not argue that the plan only showed one READ if the omitted-similar count is sitting on that step. That is the engine telling you it folded the diagram, not the bill.

UNION ALL across days should prune each branch. UNION ALL across event types that each do FROM events WHERE event_date = @d AND event_name = … can be one scan with OR or IN; if each branch is a full copy of the CTE, you paid N times for the same day, so rewrite as one filtered scan and a CASE or COUNTIF, and keep UNION ALL for genuinely different sources. Self-joins for sessionization (events a JOIN events b ON user and time window) are structurally two READs, or a read plus a self-join that looks like two; sometimes that is the correct algorithm, but often a window function on one ordered scan is cheaper. If CTE_RESCAN_CANDIDATE fires on a sessionizer, do not blindly temp-table both sides. Consider whether one scan with LAST_VALUE, a MATCH_RECOGNIZE-style rewrite, or a mart is the actual fix.

SELECT
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'cart') AS carts,
  COUNTIF(event_name = 'buy') AS buys
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE()
  AND event_name IN ('view', 'cart', 'buy');

Cap the obsession. A 50 MB table read three times is not a sprint. The rule has a bytes floor so you do not drown in tiny double reads. When it fires, the heap is large enough that N matters. Believe N, then pick one pass, a temp table, or a mart.

Grep for WITH is how you get a list of every readable query in the project, not how you get the billed double read: some WITH clauses materialize in practice, and some queries without WITH still read the same table twice through views or UNIONs. job_stages is the detector; query text is the exhibit you attach after. Jobs without plan data are skipped, the same conservative rule as partition filters and MERGE rewrites: a missing plan is not a re-scan, and if a lot of expensive jobs lack stages, fix the scan's jobs query rather than lowering the rule to grep.

-- Pseudocode for the signal, not a JOBS column you SELECT as text:
-- source READ table_id = events   count >= 2
-- OR a READ substep: "(N similar tables omitted)" with N >= 1
-- bytes_processed >= 1 GiB
-- cache_hit is false
-- group by query_hash_normalized_literals

Grouping by normalized query hash means a Looker explore that inlines twice every refresh is one finding, not 400. The representative job is the one to EXPLAIN. Details include the table keys that were repeatedly read and the max N. That is enough to know whether you are fixing events or events plus orders.

FULL_TABLE_SCAN can fire on the same job if those READs had no partition filter. That is not double-counting of the same recommendation. One finding says prune. The other says stop reading the pruned result twice. Do both. A temp table of 4 TB unpruned events, read once, is still a 4 TB write. Put the date filter in the materialization.

SlashPeak never opens the table. Referenced tables plus minimized READ stages plus bytes billed. If the engine omitted similar reads, that signal counts. If the engine truly scanned once, the rule stays quiet, even if the SQL has three CTEs. That silence is a feature.

Temp tables are for a script or a session that will make several passes and then throw the snapshot away. They are the right fix for a stored procedure, a dbt-ephemeral replacement in a single job, or a notebook you insist on keeping. They are the wrong fix for Looker. Looker cannot see your temp table.

Scheduled marts are for many consumers. Build daily_kpis once with a partition filter and named columns, and point every explore at it; the CTE that used to fork inside each explore disappears because the fork was pre-paid. This is the same problem as the BI over-fetch post from the opposite side: here the cost is extra reads, there it is extra width, and real jobs often have both.

CREATE OR REPLACE TABLE `proj.marts.funnel_daily`
PARTITION BY metric_date
AS
SELECT
  event_date AS metric_date,
  COUNTIF(event_name = 'view') AS views,
  COUNTIF(event_name = 'cart') AS carts,
  COUNTIF(event_name = 'buy') AS buys
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1;
-- Looker reads funnel_daily. Heap is scanned once per day, not once per explore.

Materialized views can sit in the middle: BigQuery maintains them, queries can rewrite to them. They have constraints. They are not a dump for SELECT * FROM events. Use them for stable aggregations. Do not expect a materialized view to rescue an explore that still joins the heap to itself.

dbt ephemeral models are CTEs. If you chain three ephemeral models on events, you compiled a nested WITH, and that can be the N-read job. Incremental or table materialization for the shared grain is the dbt-native temp table. Ephemeral is DRY for compile time; it is not a storage contract.

Pick lifetime first, then syntax. Session: TEMP TABLE. Day: scheduled table or incremental model. Query rewrite: materialized view if the shape fits. WITH: only when EXPLAIN shows one cheap READ or the bytes are small. SlashPeak will keep flagging the patterns whose plans still show N greater than 1 on a large table. Let that list choose the lifetime, not the other way around.

Open the representative job. Count READs of the named table. Note whether each READ has a partition filter. Note the column width. You now know if you are fixing N, prune, projection, or all three.

If two consumers are aggregations of the same grain, rewrite to one pass. If two consumers need a shared filtered set for joins, TEMP TABLE or a mart. If the second READ is a view, expand it. If the second READ is a MERGE target, that is the wrong post; go to MERGE_REWRITE.

Ship the rewrite in the producer that owns the pattern: dbt model, Looker derived table, scheduled query, or notebook. Then run it once in prod and compare bytes billed and READ count. Style-only changes that leave N=2 are not done.

Re-scan. CTE_RESCAN_CANDIDATE should drop for that fingerprint. If it remains, another producer still compiles the old fork, perhaps a second explore or a copy-pasted scheduled query. Attribution tells you which. Fix the remaining source rather than tuning the one you already like.

Do not add a reservation to make the double scan faster; that is peak-hour advice applied to the wrong problem. N=2 on 4 TB is 8 TB, and halving N is the only real speedup that also halves the on-demand bytes billed.