I want to have a run_date variable reused in my sql files that uses the DBT run_started_at variable.
I’ve created a macro like so:
{% macro get_run_date() %}
Date(‘{{run_started_at.pytz…}’)
{%endmacro%}
This seems to work in my compiled sql after using {{get_run_date()}} in my code by passing in a string literal like so:
Select * from my_table where date=Date(‘2023-07-13T12:00:30.123489’)
But I’d like to just get the date using some kind of pre hook or project variable so my compiled code doesn’t have to call Date(timestamp) every time and instead uses ‘2023-07-13’ in the compiled code.
Is there anyway to set a pre hook or a project variable that will run the macro and get the actual date to pass to my code instead of passing a string literal everywhere?
When I try using the macro in run_query() in a {%set…%} instruction at the top of a script, I get an error saying bracket constructors are not allowed (even if this worked, I’d prefer not to have to do it in every sql file). I also tried setting a var to the macro in project.yml, but it doesn’t recognize the macro and passes a string literal ‘{{get_run_date()}}’ instead to my compiled sql.
My goal is to ultimately pass a run date using run_started_at to my sql queries to backfill data from a shell script.
Thanks for your help.
Have you tried formatting the run_started_at variable as a date directly in your macro?
-- /macros/your_macro_name.sql
{% macro get_run_date() %}
{{ run_started_at.strftime("%Y-%m-%d") }}
{%endmacro%}
This would avoid the need of calling the date() function in each of the models where you use this variable.