Variable as partition date in dbt, when loading BigQuery table

user_15717278 0
Viewed 2347

i am facing some issues when trying to load a partitioned table using the incremental mode. Each partition is created based on a execution_date variable i pass as an argument. For some reasons, the new partition is always generated using CurrentDate as the partition value. Even tough the variable passed as argument as a different date value. I have defined a macro to format the variable passed through the command line. Please find below the code for the macro:

{% macro formatted_date(execution_date) %}
  {% set execution_date_obj = modules.datetime.datetime.strptime(execution_date|string, "%Y%m%d") %}
  {{ return(execution_date_obj.strftime("%Y-%m-%d")) }}
{% endmacro %}

And below is how I have defined the dbt model for loading the table:

{{config(alias='unipr_salesforce',
    materialized = 'incremental',
    partitions = [formatted_date(var('execution_date'))]
)}}

Finally, this is the command use to run the dbt model, where you can see the variable used as argument:

unipr-subscription-pipeline toledanof$ dbt run --target dev --profiles-dir ./ --vars 'execution_date : "20210310"'

Every time i run the dbt model, the partition generated correspond to the CurrentDate, regardless the execution_date value. Does anybody know a possible reason for this behaviour? Thank you !

partition dbt google-bigquery
1 Answers

it seems there are a couple of issues with what you're trying to do:

  1. You need to tell dbt the name of the column you want to partition by to be able to write to a specific partition. If you don't, dbt treats this as a model that is updated incrementally but has no partitioning
  2. To be able to specify what partitions you want to replace, you'd need to use the insert_overwrite strategy. When you don't specify a strategy explicitly, dbt defaults to the merge strategy which scans the entire table to decide what to update and what to insert (and I believe requires that you specify a unique_key in your config)

One way to solve this would be to also include the current date as a column in your model and use that as your partition key. Here's what your config might look like:

{{
  config(
    materialized='incremental',
    partition_by={'field': 'current_date', 'data_type': 'date'},
    incremental_strategy='insert_overwrite',
    partitions=[formatted_date(var('execution_date'))]
  )
}}
user_93366420
Related