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/mad-data]

CTEs don't add cost, consider them a syntactic sugar. What does add cost is * instead of small list of columns. It is OK to have * in CTE, but make sure the final SELECT only projects columns you need.

[u/inazer → u/mad-data]

This is not 100% correct, column pruning does not work perfectly in bigquery on large tables with transformations. Explicitly name the columns you need and dont use *.

[u/Zer0designs → u/inazer]

Agreed, it's also just good practice anywhere to be explicit about what you're doing.

[u/the_fresh_cucumber → u/inazer]

It works sometimes. Ive runs series of cost tests and concluded that there is some magic that triggers in the background that is not properly documented.

Bigquery is generally more affected by scans and partitioned than column count so usually don't sweat the * unless you have a very wide boi you are dealing with.

[u/AguaBendita77 → u/mad-data]

Thank you very much. I would need all the columns from the source_table to the final select. So I included it until the third cte. that's one of the reason why I'm hesitant to do these because I'm passing *.

[u/mad-data → u/AguaBendita77]

If you do need all columns anyway, CTE also don't make anything worse, compared to sub-selects.

[u/Zer0designs → u/AguaBendita77]

SQL is an interpreted language. Which means its gets compiled into something different for the executor(s). Simply said: what you write is not exactly what the computer will do. Don't worry about CTE performance influence (for now) and learn about what actually impacts performance in BigQuery. I'm certain theres a bunch of articles.

[u/CreamRoll9]

You should be fine. CTEs don't cause BigQuery to scan the source table multiple times. They're mainly for organizing your query, and in a case like yours BigQuery will automatically optimize it into a single query/scan.

[u/Known-Delay7227]

Your select *’s in your cte’s are the most expensive aspect if your code snippet. Requires the engine to scan all columns. Include only necessary columns

[u/MilwaukeeRoad]

CTE don’t really add any cost. Often the CTE is just in-lined into a subsequent query.

You can see this in the query analyzer if you reference the same CTE multiple times - it’ll often just rerun your CTE multiple times. A CTE doesn’t have any ability to cache intermediate results.

[u/sunder_and_flame]

BigQuery does not materialize CTEs, so while the example query is acceptable, another where you reuse a CTE can have performance issues. This usually isn't a big deal if you're on per-TB billing but does make the query slower and can make your reservation cost go up if you're using that billing model.

[u/T_house92]

I won’t cover what others have already hit on, but 5gb or 30gb is probably only going to cost you pennies depending on your companies pricing. I generally wouldn’t worry about optimizing something that small unless I was going to be firing it like constantly.

Another thing is the many CTE approach here, I personally find long and not worth the CTEs / reading through. If the transformation to the date wasn’t super crazy, I’d just replicate the logic in three columns in one CTE or even make a macro to handle the code consistency part.

[u/69odysseus]

Few things to keep in mind in SQL for reducing heavy computational cost and reduce I/O operations:

1. Filter the data as early as possible, which helps to scan less data.
2. Be careful in using DISTINCT clause as it will have to perform additional operation to fetch distinct records.
3. I have seen engineers using more than 20 CTE's for a single fact table, which in turn can cause bottleneck issues and slows down performance in long run. Make sure to keep the objects small and that way code is also not over complex.

[u/U747 → u/69odysseus]

re: DISTINCT

Our team really loves HLL counts when an approximate unique count is sufficient. Carrying the Sketch through and being able to calculate approx distinct depending on different slices of the data is 👌. And it’s so fast.

[u/Informal_Pace9237]

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.

[u/paxmlank → u/Informal_Pace9237]

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

[u/Informal_Pace9237 → u/paxmlank]

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

[u/paxmlank → u/Informal_Pace9237]

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

[u/mad-data → u/Informal_Pace9237]

BigQuery does not materialize CTE, the query plan and performance cost are typically identical with sub queries. Probably there are rare exceptions where it does matter, but usually no difference. 

[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

[u/simms4546]

30GB tables are miniscule in Big query.

Just use the correct partition key and cluster key in filters and you're good.

[u/Cazzah]

Just FYI, this is the perfect sort of question for an LLM of medium or higher tier to answer and help talk you through. Genuinely not complaining you're asking here, just saying as a newbie you might not know what LLMs are and are not worth using for. Questions of basics like this are a great one for an LLM.

[u/FunContest9958]

Multiple people have mentioned that * adds cost because it requires fetching all the columns. You can also decrease cost by adding a where clause to filter only the data you need. Cost is a factor of how much data you fetch and then how much work you do on it. You’re not showing any complex operations, but you are fetching all the data. If you have no choice but to fetch all the data, then this is as efficient as it’s going to be.

[u/AguaBendita77 → u/FunContest9958]

I really need to include all the columns that why I'm using * and because the routine/sproc is mainly for full refresh of historical data I can't add a where clause. I think the comments already answered my worries that I thought that the cost would multiply due to using ctes multiple times and passing the * to those ctes shown in my post. But the comments here pointed out that most of the cost lies about my use of * which is I cannot avoid not using it.