DBT jinja code for Generating current date

Question

Before job runs everyday, I trying to create a partition_key variable = currentdate()-1 so that my pipeline would run for previous day but I am getting errors within DBT console

No filter named 'format_datetime'. line 2 set partition_key = dateadd('day', -1, CURRENT_DATE())|format_datetime('%Y-%m-%d') %}

Code I am using in the DBT :

{% macro yesterday_date() %}
{% set partition_key = dateadd('day', -1, CURRENT_DATE())|format_datetime('%Y-%m-%d') %}
{% endmacro %}

Also, tried to set Partition_key within my sql file but still it gave me the error.

Answer 1

I guess you already found a way to solve your problem as this question was posted 6 months ago, but I'll answer it in case someone else needs it.

Jinja is a templating language that uses some syntax from Python and where you cannot use your well-known SQL functions. To be able to get a date string for yesterday in Jinja, you need to write:

{% macro yesterday_date() %}
  {% set partition_key = (modules.datetime.datetime.now() - modules.datetime.timedelta(days=1)).strftime("%Y-%m-%d") %}
{% endmacro %}

I hope this helps!

Answer 2

You could generate the current date with either of these methods in your jinja template:

Using the DBT run_started_at variable:

{{run_started_at.strftime('%Y-%m-%d')}}

Using the datetime package:

{{modules.datetime.date.today().strftime('%Y-%m-%d')}}

Or if you're looking to do something more complicated, you could look into a package such as

dbt-date https://github.com/calogica/dbt-date