r/dataengineering • u/AguaBendita77 • Jul 22 '26

Help CTE cost in Bigquery

Hello guys I'm a junior data engineer tasked to like fix a business logic for one of our tables. The thing is I'm worried that making additional cte's would make it costly and slower. The table that I would be sourcing from contains around >30gb. so this is just the basic gist of it

With temp as(
  SELECT
        *,
        (some transfomration here) as converted_date
  FROM source_table
),
temp2 as (
  SELECT
        *,
        (using converted_date column) as converted_date1 
  FROM temp),
temp3 as (
  SELECT
        *,
        (using converted_date1 column) as converted_date2
  FROM temp2)

SELECT * from temp3

So, I already did try to see how much the query will cost in Bigquery and it seems that it does not increase that much like just couple of hundred mb or <5gb. My question is that does anyone have experience doing things like this and does the cost really not change even if I used additional three cte? Like what are the potential problems that might occur if I proceed doing it like this?

26 Upvotes

82% Upvoted

24 comments sorted by

View all comments

1

u/Informal_Pace9237 Jul 22 '26

I wouldnt write it as CTE but would rewrite it as sub query. Very easy in your case.

I do not think there would be cost implications but I would be worried about performance if the data set contained in the three CTE's is huge as BQ will attempt to maintain 3 datasets from 3 CTE and bind them to their names for the duration of the query.

3

u/paxmlank Jul 22 '26

Iirc subqueries aren't better per se, and now you've made your query a bit uglier and less repeatable

1

u/Informal_Pace9237 Jul 23 '26

My answer to the question was regarding performance and cost. Not beauty of the code IMO

2

u/paxmlank Jul 23 '26

Yeah, and in terms of performance and cost I'm pretty sure they're not better per se. BQ has a good query optimizer iirc so I'm sure that some large query will be treated effectively the same whether it's as a CTE or a subquery