Usage of incremental mode with insert_overwrite on a range of dates

[vladislav-bakanov]

Hi everyone!

I have following setup:

dbt --version
Core:
  - installed: 1.8.8
  - latest:    1.8.8 - Up to date!

Plugins:
  - bigquery: 1.8.3 - Up to date!

My model config look like:

{{
    config(
        materialized="incremental",
        incremental_strategy="insert_overwrite",
        partition_by={
            "field": "reporting_date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=["UTI"],
        partitions=dates_in_range,
    )
}}

I have 2 variables that I want to use in my partition recalculation: start and end
They look like:

{% set var_load_date_from = var('start', (modules.datetime.datetime.utcnow() - modules.datetime.timedelta(days=1)).strftime('%Y-%m-%d')) %}
{% set var_load_date_to = var('end', modules.datetime.datetime.utcnow().strftime('%Y-%m-%d')) %}

Now I want to overwrite every run specific amount of partitions that are located between start and end.

When I try to generate dates range with following macro:

{% set dates_range = dates_in_range(var_load_date_from, var_load_date_to, '%Y-%m-%d', '%Y-%m-%d') %}
{% do log("Generated dates are: " ~ dates_range, info=True) %}

The result of logging is:

Generated dates are: ['2024-11-14', '2024-11-15']

But when I try to dbt run --vars '{"start": "2024-11-14", "end": "2024-11-15"}' it fails, because a code, generated by bigquery module do following:

...
when not matched by source
         and date(DBT_INTERNAL_DEST.reporting_date) in (
              2024-11-14, 2024-11-15
          ) 
        then delete

I already tried to wrap this values with quotes, but nothing helps.

Do you have any ideas how to work with it?

Thank you in advance !

[vladislav-bakanov]

Seems I messed up with quotes.
Was fixed by:

{% set var_load_date_from = var('start', (modules.datetime.datetime.utcnow() - modules.datetime.timedelta(days=1)).strftime('%Y-%m-%d')) %}
{% set var_load_date_to = var('end', modules.datetime.datetime.utcnow().strftime('%Y-%m-%d')) %}

{% set dates_range = dates_in_range(var_load_date_from, var_load_date_to, '%Y-%m-%d', "'%Y-%m-%d'") %}