BQ pain points: performance & cost optimization

[dataders]

background

our friends at BigQuery are very interested to hear how they can improve the product for users of dbt. They'd like to target specific pain points. The more context you can share on your request the better. Think about including things along the lines of:

• scenario -- a clear and concise description of what you do or don't want to happen.
• minimum reproducible example -- I know this is hard, but makes it so easy to have the problem fixed!
• workarounds-- A clear and concise description of any alternative solutions or features you've considered.
• benefit/impact -- what kind of use case would this improve? What dbt workflows get better?

focus area for this discussion:

dbt on BigQuery starts great, but when you start getting chunky data, costs and performance can change/increase sometimes unpredictably

We've heard of a number of opportunities in this area. Here's a few examples (feel free to grab one and flesh it out more!)

• for incremental models, column-based partitioning is less performant than _ingestion_time partitions Support for ingestion time partition table on BigQuery as incremental materialization #75
• issues where partitions are not pruned correctly
• dbt's opinionated paradigms for snapshots and incremental models are not comparable to non-idiotmatic, BQ-specific methods
• query complexity:

  Complexity limitations including CTEs causing query to error out (and long time out): error message: Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex.

• Timeout issues on longer-running queries and ability to cancel them

[github-christophe-oudar]

Since you're quoting #75, we should definitely quote #77 as well that's really impactful for partition overwrite performance.
#205 definitely worth a look though as well as quoted by Jeremy.
I also reported to Google other use cases where my team could leverage some smart SQL addition (ie some kind of reduce or at least count instead of using an ARRAY_AGG and then a subquery from another CTE on top of the UNNEST.

In my opinion, for query complexity, the biggest gain dbt can provide is to build a materialization that can create temporary tables for ephemeral tables as most queries that are too complex involves, as you say, CTEs. It could be done either through using BQ scripts or expiration date on tables.

Regarding canceling queries, we've merged for next release the log to the console that let you cancel the query (or if you prefer you can cancel them using bq CLI)

[MilesTrevethan-Datatonic]

Hello :) Would it be possible to allow replacing the NULL partition in an insert overwrite statement?

Currently it throws an error because the dbt_partitions_for_replacement won't hold null and timestamp data.

Think I'll need to use a custom materialization for now but let me know what your thoughts are on this or if I'm missing something.