# google cloud bigquery DDL costs\\runtime - partitions\_to\_replace vs \_dbt\_max\_partition vs copy\_partitions

**URL:** <https://discourse.getdbt.com/t/google-cloud-bigquery-ddl-costs-runtime-partitions-to-replace-vs-dbt-max-partition-vs-copy-partitions/9838>\
**Category:** Help\
**Tags:** bigquery, dbt-cloud\
**Created:** [September 5, 2023, 9:08am UTC](https://discourse.getdbt.com/t/google-cloud-bigquery-ddl-costs-runtime-partitions-to-replace-vs-dbt-max-partition-vs-copy-partitions/9838 "2023-09-05T09:08:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![eli.sigal](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/eli.sigal/32/2603_2.png) [@eli.sigal](https://discourse.getdbt.com/u/eli.sigal)\
**Post date:** [September 5, 2023, 9:08am UTC](https://discourse.getdbt.com/t/google-cloud-bigquery-ddl-costs-runtime-partitions-to-replace-vs-dbt-max-partition-vs-copy-partitions/9838/1 "2023-09-05T09:08:16Z")

</div>

I am trying to figure out how do costs are calculated correctly when using dbt partitions\_to\_replace vs \_dbt\_max\_partition vs copy\_partitions

Please note when I say costs i look it up under **results** for every query that is created by dbt and look at MIB processed.

**Did tests and finding may vary of course. on a 44 GIB table:**

1. **\_dbt\_max\_partition**

month\_start\_date \>= date\_sub(\_dbt\_max\_partition, interval N day) logic costs overall 27.07 GIB to process which is quite a lot.

1. **partitions\_to\_replace**

month\_start\_date in ({{ partitions\_to\_replace | join(‘,’) }}) - did the the exact same GIB and running time which is disappointing (im using the latest dbt version 1.6.1 -config and is\_incremental below)

```auto
{{
    config(
        materialized='incremental',
        partition_by = { 'field': 'created_date', 'data_type': 'date',"granularity": "month" },
        cluster_by = ["first_line_item","is_first_order","customer_id","transaction_id"],
        incremental_strategy = 'insert_overwrite',
        on_schema_change='sync_all_columns',
        partitions = partitions_to_replace
    )
}}

{% if is_incremental() %}
where month_start_date in ({{ partitions_to_replace | join(',') }})
{% endif %}

```

1. **copy\_partitions**

It ran twice the amount of time and took twice the amount of $.

**Questions regarding the findings**

a. Do I look at costs correctly?  
b. why does the partitions\_to\_replace vs \_dbt\_max\_partition takes the exact same time?  
c. why does the copy\_partitions runs twice the query and twice the COPY, is this expected??  
d. copy\_partitions runs twice the time - is it due to the twice running logic above or because the copy for each partition is done sequential?  
e. did I do correct the config above for partitions\_to\_replace?

Any help would be appreciated

---

<div class="post-metadata">

**Author:** ![eli.sigal](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/eli.sigal/32/2603_2.png) [@eli.sigal](https://discourse.getdbt.com/u/eli.sigal)\
**Post date:** [September 5, 2023, 3:59pm UTC](https://discourse.getdbt.com/t/google-cloud-bigquery-ddl-costs-runtime-partitions-to-replace-vs-dbt-max-partition-vs-copy-partitions/9838/2 "2023-09-05T15:59:02Z")

</div>

So to add regarding the **copy\_partitions**

There is a current bug and PR on github for this

> <https://github.com/dbt-labs/dbt-bigquery/commit/6a2a90ccfa2a705672ddaccc74f050c09e45cff6>
>
> \* avoid creating twice the temp table in dynamic insert overwrite
> 
> \* add tests… for copy\_partitions & time ingestion on on\_schema\_change
> 
> \---------
> 
> Co-authored-by: colin-rogers-dbt \<111200756+colin-rogers-dbt@users.noreply.github.com\>

so the questions are only about the other 2 ways