Unable to run Multiple CTEs in BigQuery

Question

I am trying to run Multiple CTEs and I am getting this error that says "Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex"

I had a look at articles/solutions for this error but none of them are applicable to my code as I am not doing self-joins/creating complex recursions.

My CTEs are something like this.

WITH CTE1 AS
(
  SELECT "COLS"
  FROM "BLAH" 
)
, CTE2 AS
(
  SELECT *
  FROM CTE1
)
, CTE3 AS
(
  SELECT *
  FROM CTE2
)
, CTE4 AS 
(
  SELECT *
  FROM CTE3
)
, CTE5 AS
(
  SELECT *
  FROM CTE4
)
SELECT *
FROM CTE5

Usually around CTE4 I start to receive the error mentioned above. This code gets used in a view which is then used elsewhere in another BigQuery script.

Do you have any ordering in your CTEs? That is a frequent culprit for eating up unnecessary compute power. — rtenha

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2020-07-10T05:30:31

If the query gets too complex with too many CTEs and views, it might be time to materialize part of your query.

It's not ideal, but consider encapsulating the CTEs or VIEWs behind a CREATE OR REPLACE TABLE. Tools like dbt can help a lot with this.

Unable to run Multiple CTEs in BigQuery

1 Answers