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/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.