dbt_profile vars not parsing derived variable correctly when used in a yml file

[petertjmills]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Unfortunately I’ve got some sources with different date formats at the end of their table names. I’d like to specify vars in my dbt_project.yml like this:

…
vars:
  date: “2024-06-30”
  year: “{{ modules.datetime.datetime.strptime(var(‘date’), ‘%Y-%m-%d’).year }}”
…

The year variable works fine when I use it in an sql query, but if I use it in my sources.yml like so:

…
sources:
 - name: xyz
   schema: abc
   tables:
    - name: table_x
      identifier: “{{ ‘table_identifier_’ ~ var(‘year’) }}”
…

However when I try and use this with {{ source(‘xyz’, ‘table_x’) }} dbt renders, as a table name, abc.table_identifier_{{ modules.datetime…}} obviously throwing an error.

If I hardcode the value for year it works fine.

Expected Behavior

Expecting {{ source(‘xyz’, ‘table_x’) }} to resolve to abc.table_identifier_2024

Steps To Reproduce

1. Define dbt_profiles.yml as above
2. Define sources in models/*.yml, referencing a derived variable
3. Run dbt show —inline “select * from {{ source(‘xyz’, ‘table_x’) }}”
4. Unhandled error while executing

Relevant log output

No response

Environment

- OS: macOS Monterey 
- Python: 3.9.6
- dbt: 1.7.3

Which database adapter are you using with dbt?

other (mention it in "Additional Context")

Additional Context

I’m using a custom built adapter for our data infrastructure but I don’t believe this to have an impact. It seems the issue is in how the variables are rendered before any adapter code is run.
Happy to be wrong if this isn’t the case!

[dbeatty10]

What if you use a Jinja filter to cast it to a string?

i.e.

…
vars:
  date: "2024-06-30"
  year: "{{ modules.datetime.datetime.strptime(var('date'), '%Y-%m-%d').year  | string }}"
…

Or:

…
sources:
 - name: xyz
   schema: abc
   tables:
    - name: table_x
      identifier: "{{ 'table_identifier_' ~ var('year') | string }}"
…

[petertjmills]

Thanks for getting back!
Tried all of these, in various combinations, including on the {{ source() }} statement with no luck. 😕

Using {{ var('year') }} in the sql statement rather than in the yaml file works fine without casting to a string which seems strange.

[dbeatty10]

Ah, I see!

Here's what's going on: Jinja is not supported within the vars config in dbt_project.yml.

There's more information here and here. (The proposal in #2955 mentions being "strict about accepting only literal values", so I don't think it would fit what you're after.)

I understand that the way you defined the year variable works the way you want within a .sql model file, but I believe that is an accidental side effect of the way we render those files rather than a purposeful feature.

Since we're not planning on supporting Jinja within the vars config, I'm going to close this as "not planned".

Alternative approach

If you want to keep it as close to your example as possible, you'd need to do this:

dbt_project.yml

vars:
  date: "2024-06-30"

sources:
 - name: xyz
   schema: abc
   tables:
    - name: table_x
      identifier: "{{ 'table_identifier_' ~ modules.datetime.datetime.strptime(var('date'), '%Y-%m-%d').year }}"

instead of this:

dbt_project.yml

vars:
  date: "2024-06-30"
  year: "{{ modules.datetime.datetime.strptime(var('date'), '%Y-%m-%d').year }}"

sources:
 - name: xyz
   schema: abc
   tables:
    - name: table_x
      identifier: "{{ 'table_identifier_' ~ var('year') }}"

[petertjmills]

Understood, have missed that!

Thanks for your help.

For anyone else looking I stumbled across a more succinct solution. Yaml will parse a date like this with no quotation marks, so there's no need to use modules.datetime:
dbt_project.yml

...
vars:
  date: 2024-06-30
...

sources.yml

...
    identifer: "{{ 'table_' ~ var('date').year }}"
...

[dbeatty10]

Nice find, and thank you for sharing! 🤩

I just verified that you can also include a datetime via the --vars CLI flag like this:

--vars "{'date': 2022-02-02}"

The key is not wrapping the datetime in quotes (or else it will be treated as a string instead).

The example values here all worked as well:

canonical:        2001-12-15T02:59:43.1Z
valid iso8601:    2001-12-14t21:59:43.10-05:00
space separated:  2001-12-14 21:59:43.10 -5
no time zone (Z): 2001-12-15 2:59:43.10

[Fakhfaakh]

@dbeatty10 @MichelleArk
Hi :)
I think this would be a great feature to add, having access to runtime-available variables without having the ability to dynamically set them (not possible with a pre-hook nor with templating "vars") is a little bit of a disappointment for me.
Please let me know, If you know another way of setting a dynamic global-runtime-available variable before running the models.

[FrankTub]

I have a use case that is somewhat related to this. I'm trying to dynamically change the netsuite database that is used in the netsuite package (see step 4). The laziest way would be to do it by doing some sort of jinja in dbt_project.yml:

vars:
  netsuite_database: "{{ 'staging_raw' if target.name in ('snowflake-staging', 'development') else 'raw' }}"

However, the resulting SQL is then:

...
from {{ 'staging_raw' if target.name in ('snowflake-staging', 'development') else 'raw' }}.netsuite_suiteanalytics.location
...

It would be really awesome if something like this would be possible. The alternative I'm going for now is don't set the variable in dbt_project.yml, but from the command line. But my pain is that I don't want to have to add it to every statement that I run. Not sure if there is an alternative to always add the -- vars part to every dbt statement.

dbt run -s netsuite2 --vars "{'netsuite_database': $NETSUITE_DATABASE}"

More background on the things I tried can be found here.