Quoting for incremental model partitions to replace - dbt v0.17.2

Question

I'm writing an incremental model for BigQuery using the insert_overwrite strategy and attempting to set the partitions_to_replace using a variable:

{% set partitions_to_replace = [var('execution_date')] %}

Just to test compilation, I'm compiling with a variable in my dbt_project.yml that looks like execution_date: '2020-01-01'. However, it seems like in the merge statement generated by the materialization, the date isn't quoted, so it fails with the error No matching signature for operator IN for argument types DATE and {INT64}. Here's the relevant snippet of generated SQL:

when not matched by source
         and DBT_INTERNAL_DEST.visit_date in (
              2020-01-01
          ) 

Is there a way to ensure quotes around the variable? When using a variable in SQL that I've written, I know I can just wrap the var function in quotes, but in this case the SQL is being generated by the materialization.

Answer 1

It's a fair question. For flexibility, the materialization does not try to wrap the partitions values in quotes, as a way of supporting both SQL expressions and literals as potential inputs.

i.e. You may want the merge predicate to be:

when not matched by source
         and DBT_INTERNAL_DEST.visit_date in (
              '2020-01-01'
          ) 

but you might equally want it to be:

    when not matched by source
         and DBT_INTERNAL_DEST.visit_date in (
              date_sub(current_date, interval 1 day)
          ) 

As such, you'll need to either:

• pass a string literal into your var by wrapping in double quotes:

vars:
  execution_date: "'2020-01-01'"

• or handle the additional quoting in your set statement, along the lines of:

{% set partitions_to_replace = [] %}
{% for execution_date in [var('execution_date')] %}
    {% set ex_date %} '{{ execution_date }}' {% endset %}
    {% do partitions_to_replace.append(ex_date) %}
{% endfor %}

Check out this related issue. The OP had some recommendations for syntax we could add to make this more straightforward; I'm curious to hear which of those would make sense to you.