BigQuery: too many subqueries or query is too complex

Question

Often times when working with BigQuery source, I face this error:

Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex.

What can I do?

Answer

The query you runs seems to be too complex. It's most likely that you will have a query with WITH clause here. The solution is to simplify your query.

You can consider replacing the WITH clause (subqueries) with temporary tables. That way, the subquery won't have to be executed again (taking up resources).

There's a few ways to do this:

• Using BigQuery console to create materialized view
• Using Holistics Modeling UI to create transform models in Holistics.

Note: When using Transform Model, remember to turn on Persisted Storage setting so that the resultset is written back into a BigQuery table.