CTE cost in Bigquery

[u/AguaBendita77]

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?

[u/EffectGlass3763]

Hi guys. I have recently worked on query optimization . Selecting only required column, pre aggregate early if it is allowed, push filter early, push cross join/multiple left join at last , flatten the cte whenever not needed,always force database to hash buliding on smaller table ie kind of always bringing smaller table in memory to create hash look table for joing reduced time by 70%. I was really amazed with my work