target-based Jinja rendering of vars in dbt_project.yml

[matt-winkler]

Describe the bug

When compiling / running models from dbt Cloud, the compiler does not correctly evaluate variables defined using jinja in dbt_project.yml and referenced in a sources: block.

Steps To Reproduce

Create in dbt_project.yml:

vars:
  database_prefix: "{% if target.name == 'dev' %}dev_{% else %}{% endif %}"

Create in sources.yml:

sources:
  - name: tpch
    database: "{{ var('database_prefix') }}raw"

Expected behavior

SQL compilation will resolve the variable to either dev_ or an empty string when running either dbt compile or dbt run

Screenshots and log output

On dbt compile:

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

0.18.1

The operating system you're using:
dbt Cloud

[jtcohen6]

Thanks for the bug report @matt-winkler! I get that this is unexpected behavior. The truth is that vars in dbt_project.yml aren't Jinja-rendered today. dbt needs to know the values of vars before it can render any other parts of the project. This leads to weirdness today whereby var() is in the scope of the dbt_project.yml context, but only if set by --vars (docs).

Ultimately, a solution here would be to clearly establish the parsing order of project files:

• env vars, --vars, dbt_version, and builtins are always available
• parse profile --> get target values
• parse vars (with env vars, --vars, and target available)
• parse the rest of the project file

I'm definitely interested in rationalizing when vars are available, and what's available to them: see #2955. I don't feel a ton of immediate priority here, though; in the use case you're describing, there are several other ways to achieve this env-based behavior:

• Your deployment can override {{ var('database_prefix') }} with a (static string) value passed to --vars
• You could control this behavior via {{ env_var() }} instead

If you do want dbt to handle the database prefixing on the basis of target.name alone, it's not possible to get any DRYer than this:

version: 2

sources:
  - name: tpch
    database: "{{ 'dev_' if target.name == 'dev' else '' }}raw"
    tables:
      - name: something

[matt-winkler]

Thanks for the commentary @jtcohen6! Super helpful for you to reinforce that this relates to the parsing order. Agree that adding the jinja to sources.yml works. Thanks for linking 2995 as well. Would you still say this fits as a bug, or should we close and plan to handle with the additional variable extraction mentioned in 2955?

[jtcohen6]

After thinking about it a bit more, I do think this represents something novel that hasn't been requested before, though with plenty of overlap with other questions of parse order and vars definitions. I'm going to recast this as a feature request and rename it accordingly.

Glad you're able to work around this in the meantime!

[adityaguru149]

On the same boat here.
I was trying to figure out a way to get the source schema name (+some other values) to change based on the target.

Use Case:
Multiple customers have their data in separate tables having same fields. Similar transformations need to be run on them resulting in a set of resultant tables per customer which can then be utilized by BI tool.
Similar requirement on Stackoverflow

As of now how this can be achieved is kind of bundle the SQL transformations into macros and use them in tables, once for each customer. Any other approach?

Solution:
Extracting out vars makes sense . Being able to select vars per target / making target itself a var would then enable such use cases.

@jtcohen6 Thoughts?

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[choudrybabu]

database: "{{ 'dev_' if target.name == 'dev' else '' }}raw" --> can this be changed to if else , we have 4 different targets and so 4 diff db names for each target
any suggestions

[philippefutureboy]

@choudrybabu

Either use the target.name directly:

{{ target.name + '_' if target.name else '' }}raw

or a dictionary:

{{ {"dev": "dev_", "target-2": "target_2_", ...}.get(target.name, '') }}raw

?

Could that work?

[moseleyi]

I am so much in need of using target.project in dbt_project.yml vars! I want to create vars for couple of policy tags that have to be set up on a project level (and dev dbt goes to dev project). Right now I have to copy a long string for each column in any model:

policy_tags:
  - 'projects/{{target.project}}/locations/{{var("default_location")}}/taxonomies/6932546414257800377/policyTags/3610480920170058151'

but it would be eaiser for people writing the models to just use;

policy_tags:
  - '{{ var("full_name_policy_tag") }}'

because I could change the policy without having to change all the references

[philippefutureboy]

@moseleyi Have you considered using env_var instead of var and call dbt from a parent process (python script or a workflow manager such as Airflow)? I don't know how many env vars you'll need for your use case, but it can be manageable if you think it's worth the trouble.

[moseleyi]

Not sure, would I just pass project and location? and would the vars in dbt_project.yml then work?