[Feature] Microbatch should allow copy_partitions

[jschintz-nytimes]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-bigquery functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Merging in BQ is great - but not always the most performant

Allow for the existing feature "copy_partitions" to be available in the microbatch incremental strategy

Describe alternatives you've considered

Write a custom strategy

Who will this benefit?

Anyone using microbatch

Are you interested in contributing this feature?

Maybe!

Anything else?

No response

[borjavb]

If I'm not mistaken I think copy_partitions is already working with microbatch.

microbatch under the hood relies on insert_overwrite and the copy_partitions config is being set through all the new microbatch logic.

dbt-bigquery/dbt/include/bigquery/macros/materializations/incremental_strategy/microbatch.sql

Lines 20 to 28 in 8c2d9bb

	{% macro bq_generate_microbatch_build_sql(
	tmp_relation, target_relation, sql, unique_key, partition_by, partitions, dest_columns, tmp_relation_exists, copy_partitions
	) %}
	{% set build_sql = bq_insert_overwrite_sql(
	tmp_relation, target_relation, sql, unique_key, partition_by, partitions, dest_columns, tmp_relation_exists, copy_partitions
	) %}
	{{ return(build_sql) }}
	{% endmacro %}

[jschintz-nytimes]

If I'm not mistaken I think copy_partitions is already working with microbatch.

microbatch under the hood relies on insert_overwrite and the copy_partitions config is being set through all the new microbatch logic.

dbt-bigquery/dbt/include/bigquery/macros/materializations/incremental_strategy/microbatch.sql

Lines 20 to 28 in 8c2d9bb

	{% macro bq_generate_microbatch_build_sql(
	tmp_relation, target_relation, sql, unique_key, partition_by, partitions, dest_columns, tmp_relation_exists, copy_partitions
	) %}
	{% set build_sql = bq_insert_overwrite_sql(
	tmp_relation, target_relation, sql, unique_key, partition_by, partitions, dest_columns, tmp_relation_exists, copy_partitions
	) %}
	{{ return(build_sql) }}
	{% endmacro %}

@borjavb I think that's just passing a required parameter with the default, false.
I'm seeing merge strategy being used in the microbatch run code.

    merge into `dev`.`dbt_cloud_pr_00000_000`.`microbatch` as DBT_INTERNAL_DEST
        using (
        select
        * from `dev`.`dbt_cloud_pr_00000_000`.`microbatch`
      ) as DBT_INTERNAL_SOURCE
        on FALSE

    when not matched by source
         and date(DBT_INTERNAL_DEST.partitiontime) in unnest(dbt_partitions_for_replacement) 
        then delete

    when not matched then insert
        (`fields`)
    values
        (`fields`)

;

[borjavb]

You are right, is not working. Sorry!

I think I found the issue, basically there's a conditional check where copy_partitions can only be set on insert-overwrite, and it was not updated to accept microbatch. Let me open a PR.

---

How to reproduce:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='session_start',
    begin='2024-12-02',
    batch_size='day',
    lookback = 1,
    partition_by={
      'field': 'session_start',
      'granularity': 'day',
      "copy_partitions": true
    }
) }}
select CURRENT_DATE() as session_start

That trigger this line

dbt-bigquery/dbt/include/bigquery/macros/materializations/incremental.sql

Lines 98 to 102 in 62695c9

	{% if partition_by.copy_partitions is true and strategy != 'insert_overwrite' %} {#-- We can't copy partitions with merge strategy --#}
	{% set wrong_strategy_msg -%}
	The 'copy_partitions' option requires the 'incremental_strategy' option to be set to 'insert_overwrite'.
	{%- endset %}
	{% do exceptions.raise_compiler_error(wrong_strategy_msg) %}

[borjavb]

https://github.com/dbt-labs/dbt-bigquery/pull/1421/files This should do it!

But I wonder if we should default copy_partitions as true for microbatch... It might be the best option for this operation really...

[MichelleArk]

Closed by #1421, this will go out in the next 1.9 patch (1.9.1)