dbt vars error -- insert_overwrite strategy with static partitions

Help
, ,
1

The problem I’m having

Date variable provided in command line is not flowing through to code, resulting in braced constructors are not supported error in BigQuery.

The context of why I’m trying to do this

Refrencing dbt insert_overwrite strategy with static partitions. I want to add an if else block to set the partitions_to_replace.

What I’ve already tried

{% if var("date", false) != false %}
    {% set partitions_to_replace = [
    '{{ var("date") }}',
    ] %}
{% else %}
    {% set partitions_to_replace = [
    'current_date',
    'date_sub(current_date, interval 1 day)',
    'date_sub(current_date, interval 2 day)',
    'date_sub(current_date, interval 3 day)'
    ] %}
{% endif %}

dbt command line

dbt run -s test_model --vars '{date: 2023-07-03}'

Error

WHERE 
	DATE(timestamp_modified) IN ({{ var("date") }})
) as DBT_INTERNAL_SOURCE
        on FALSE

    when not matched by source
         and date(DBT_INTERNAL_DEST.date_modified) in (
              {{ var("date") }}
          ) 
        then delete

    when not matched then insert
        (`timestamp_modified`, `date_modified`, `reference`)
    values
        (`timestamp_modified`, `date_modified`, `reference`)
2

https://docs.getdbt.com/docs/building-a-dbt-project/dont-nest-your-curlies

Note: @Johann De Wet originally posted this reply in Slack. It might not have transferred perfectly.

3

How can I ensure that the variable outputs a string when using formatting recommended in docs above?
Currently outputs

Code Used

{% if var("start_date", false) != false %}
    {% set partitions_to_replace = [
    var("start_date")
    ] %}
{% else %}
    {% set partitions_to_replace = [
    'current_date',
    'date_sub(current_date, interval 1 day)',
    'date_sub(current_date, interval 2 day)',
    'date_sub(current_date, interval 3 day)'
    ] %}
{% endif %}

Command Line

dbt run -s test_partition --vars '{start_date: "2023-07-03"}'

Error

    when not matched by source
         and date(DBT_INTERNAL_DEST.date_modified) in (
              2023-07-03
          ) 
        then delete
4

Can you try var("start_date") | string

Note: @Johann De Wet originally posted this reply in Slack. It might not have transferred perfectly.

5

Persists as integer. Any additional thoughts?

6

Works with the following in the command line.
dbt run -s model_name --vars ‘{‘start_date’: cast(“2023-07-14” as date)}’

7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.