How get curent date in dbt CLI?

[Vytlksn]

The problem I’m having

Trying to build model and have dynamic variable - today date as string (YYYY-MM-DD)

The context of why I’m trying to do this

Need to have models with historical date filter and current date filters.

What I’ve already tried

dbt run --select +model --vars '{"key": "value", "current_date": "now().date()"}'
dbt run --select +model --vars '{"key": "value", "current_date": "{{ macros.date() }}'

Works manually

dbt run --select +model --vars '{"key": "value", "current_date": "2023-03-13"}'

Solution

In BigQuery you can use current_date() function to get YYYY-MM-DD date.
In SQL you can use now().date()

SETUP:
dbt_project.yml var:
current_date: current_date: "'2022-12-31'"

macro:
where transaction_date <= {{current_date}}

model sql:

with model as ( 
    {{ macro_with_filter(current_date=var('current_date')) }}
)

select * from model

This setup will for for CLI’s:

dbt run --select model_name 
dbt run --select model_name --vars '{"key": "value", "current_date": "current_date()"}'

[brunoszdl]

Hi @Vytlksn, how are you using this var in your model?

[Vytlksn]

Hi,

And have it in dbt_project.yml as as VAR : current_date: '2020-10-01'

in Macro : where transaction_date <= '{{current_date}}'

and in model:

with name as ( 
    {{ macro_with_filter(current_date=var('current_date')) }}
)

select * from name 

[brunoszdl]

Have you tried in the macro
where transaction_date <= {{current_date}}
without quotes?

[Vytlksn]

Yes, model takes var('current_date') and generate model with it. No problem with variable from dbt_project.yml.
CLI works also like: dbt run --select +model --vars '{"key": "value", "current_date": "2023-03-13"}'

But how instead of '2023-03-13 'get dynamic variable of today date in CLI?

[brunoszdl]

You should be able to do this you wrote

dbt run --select +model --vars '{"current_date": "now().date()"}'

But I wanted to check if the problem is with the quotes here
where transaction_date <= '{{current_date}}'

I did not understand if you tried without the quotes, like this:
where transaction_date <= {{current_date}}

I think in the first option you are passing a string, and not the date function.

[Vytlksn]

If I leave in macro: where transaction_date <= {{current_date}}

model:

with model as ( 
    {{ macro_with_filter(current_date=var('current_date')) }}
)

select * from model

And run CLI:

dbt run --select +model --vars '{"key": "value", "current_date": "now().date()"}'

Got Syntax error: Function call cannot be applied to this expression. Function calls require a path, e.g. a.b.c()
I am using BigQuery

[brunoszdl]

In bigquery you can use the current_date() function instead of now().date()

[Vytlksn]

Oh, really! It worked :slight_smile
But now if run simple dbt run --select model
got error
No matching signature for operator <= for argument types: DATE, INT64. Supported signature: ANY <= ANY
But probably it’s another topic.

[brunoszdl]

can you show the generated .sql file in target/compile?

[Vytlksn]

Generated SQL

with payouts as ( 
    
-- config

with payouts as ( 
    select 
        *
    from table_a
    where transaction_date <= 2022-12-31
    group by 
        column_b
)

select * from payouts

)

select * from payouts

I am misisng quotes ‘’ to compile correctly, but with current_date() works fine.

[Vytlksn]

I added in dbt_project var: current_date: "'2022-12-31'"
And now works with VAR and with curent_date()
Thanks, good luck!

[jkpltd]

I look up current date from date dimension.
If you use a function like getdate(), you wont get benefits of cached results. Which is important for me to keep costs down.