DBT CLI vars value doesn't get passed as string

Question

I have a var("run_date") defined in the project YML to be yesterday as a date, using dbt_date.yesterday()

If I use this in SQL it works just fine. However, I'm using this setup to be able to override the value using the CLI. But when I do something like

dbt run --select test_model --vars '{run_date: "2023-12-01"}'

it just gets passed as 2023-12-01 and the SQL fails. I tried different ways of using ', " or without in the command, but nothing seems to work.

I'm using the following SQL to test this;

{% set run_date_dash = var("run_date") %}

SELECT REPLACE(SAFE_CAST( {{ run_date_dash }} as STRING), "-","") as test_date

Someone in another question fixed his problem by simply adding the '' in the sql, but that doesn't work here as by default the run_date is of type DATE

Any suggestions to fix this directly or should I build in some jinja condition to check if the variable is overruled or something?

UPDATE: as a SELECT the code below seems to work, but might not be the nicest solution. My problem is that var("run_date") by default is dbt_date.yesterday(), which is just an SQL statement. This isn't executed within a macro if I'm correct? So using a macro wasn't a solution here.

However, trying to use this now in a WHERE statement seems to also be problematic, but that probably is a different question..

{% set run_date_dash = var("run_date" %}

{% if run_date_dash == dbt_date.yesterday() %}
  SELECT
    1 as test,
    REPLACE(CAST(
      cast(
        datetime_add(
          cast(
            cast(timestamp(datetime(current_timestamp(), 'Europe/Amsterdam')) as date) as datetime
          ),
          interval -1 day
        ) as date
      ) as STRING
    ), "-", "") as test_date
{% else %}
  SELECT
    1 as test,
    REPLACE(CAST('{{ run_date_dash }}' as STRING), "-", "") as test_date
{% endif %}

UPDATE2: Managed to fix it using a macro that also can do other things to the date string if needed.

Answer 1

A few things it's important to clarify:

• dbt_date.yesterday() is not SQL, it's a dbt jinja macro. It has to be called inside {{ }}. Kudos for using the cross-dialect macro.
• A var in dbt_project.yml of dbt_date.yesterday() will never evaluate even if you put it in {{ }} because the vars are only ever treated as strings. (I know it's annoying.)
• var and env_var are different variable types.
• Don't assume that var("run_date") is type date. It's in date format, but I bet it's type is string.

The "English" of what you're trying to do is "When I pass a run_date to dbt through the dbt CLI then use that date, else use yesterday's date."

The way to accomplish that is {{ env_var('run_date', dbt_date.yesterday()) }} ::date. The ::date assumes you're in a SQL dialect that supports that casting syntax.

Note: If you're using dbt Cloud you'll want to name the variable DBT_run_date instead of run_date.