Extension not picking up env variables from .env file

[rapha-pereira]

Expected behavior

To use our dbt core we simply execute a source command that gets all the variables in our .env file of the workspace and it runs perfectly, the extension should use them too as described here in the docs of the extension.

Actual behavior

The extension doesn't pick up the variables in .env file and keeps returning the same error (image bellow).
Those env variables are necessary to find the profiles yml of our dbt project.

Steps To Reproduce

• Create a .env file in your project.
• Create an env variable called: DBT_PROFILES_DIR
• Open the project in VSCode
• Extension doesn't work.

Bellow is my .env file as a .txt

env-example.txt

for example.

Log output/Screenshots

No response

Operating System

WSL for Windows

dbt version

1.7.18

dbt Adapter

BigQuery

dbt Power User version

0.51.2

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[anandgupta42]

@rapha-pereira Have you looked at https://docs.myaltimate.com/setup/optConfig/#environment-variables-setup ?

[rapha-pereira]

@rapha-pereira Have you looked at https://docs.myaltimate.com/setup/optConfig/#environment-variables-setup ?

Yes, and I tried the method via the settings.json file and it didn't work either.
My coworker tried using the extension and got the same error as me.

[anandgupta42]

@rapha-pereira Can you share the settings.json here?

[rapha-pereira]

@anandgupta42
I'm not trying to make it get the environment variables from settings.json, although I've tried that too.
The extension isn't pulling the environment variables from my .env file, that's the problem.
dbt core works fine from terminal, btw.

Examples of my dbt project structure:
dbt_project.yml in the root, pointing out to the profile:
profile: 'bigquery-prof'

profiles.yml inside config folder, that folder is in the root of the project:
my-dbt-project -> config -> profiles.yml
masked project id for example purposes

bigquery-prof:
  target: "{{  env_var('DBT_PROFILE')  }}"
  outputs:
    prod:
      type: bigquery
      method: oauth
      project: my-project
      dataset: "{{  env_var('DBT_DATASET_DEFAULT')  }}"
      threads: 4
      timeout_seconds: 300
      priority: interactive
    nonprod:
      type: bigquery
      method: oauth
      project: my-project
      dataset: "{{  env_var('DBT_DATASET_DEFAULT')  }}"
      threads: 4
      timeout_seconds: 300
      priority: interactive
    local:
      type: bigquery
      method: service-account
      project: my-project
      dataset: "{{  env_var('DBT_DATASET_DEFAULT')  }}"
      keyfile: application_default_credentials.json
      threads: 4
      timeout_seconds: 300
      priority: interactive
      # maximum_bytes_billed: 30000000000
    dev:
      type: bigquery
      method: oauth
      project: my-project
      dataset: "{{  env_var('DBT_DATASET_DEFAULT')  }}"
      threads: 4
      timeout_seconds: 300
      priority: interactive
      # maximum_bytes_billed: 30000000000

My .env file, in the root of the project:

# GCP
GCP_PROJECT_ID=my-project

# DBT
DBT_PROFILE=local
DBT_PROFILES_DIR=config
DBT_PROJECT_DIR=/home/rapha-wsl/github-projects/my-dbt-project
DBT_DATASET_DEFAULT=dw_test

The error of the extension:

[anandgupta42]

We rely on the Python VSCode extension to fetch the environment variables.

Check the section: https://docs.myaltimate.com/setup/optConfig/#environment-variables-through-pythonenvfile

Try manually setting the path for your .env file using the settings mentioned in the document above. Then run the environment variable printer to check if the variable is detected.

Also, if you post it in the community Slack, someone may comment on it if they have solved it in the past -
https://getdbt.slack.com/archives/C05KPDGRMDW

[rapha-pereira]

When printing the env variables, my DBT_PROFILES_DIR variable from dotenv appears, but the extension fails to parse the project with the error image above.
e.g.:

DBT_PROFILES_DIR=config            source:dotenv
DBT_PROJECT_DIR=/home/rapha-wsl/github-projects/my-dbt-project            source:dotenv
DBT_DATASET_DEFAULT=dw_test              source:dotenv

Why does dbt core in the terminal work fine with these variables and the extension doesn't? What am I missing here?

[rapha-pereira]

@anandgupta42
Another thing...
The extension works for builds and runs, only query preview and compiled dbt preview don't work.

[BeaAnjos]

Up! I'm having the same issue!

[diegoquintanav]

if DBT_PROFILES_DIR is set, then

vscode-dbt-power-user/dbt_core_integration.py

Line 220 in 5735fa6

return os.path.normpath(os.path.join(project_dir, profiles_dir))

is joining project_dir with profiles_dir. If project_dir is coming from DBT_PROJECT_DIR, and both paths are relative then this is effectively producing <DBT_PROJECT_DIR>/<DBT_PROFILES_DIR> which may not exist.

In my case, I have

DBT_PROJECT_DIR=my_project
DBT_PROFILES_DIR=my_project/my_profiles

and this method is returning default_profiles_dir=my_project/my_project/my_profiles

The value of project_dir being passed is resolved as **/dbt_project.yml as in

vscode-dbt-power-user/src/manifest/dbtWorkspaceFolder.ts

Line 90 in 5735fa6

`**/${DBTProject.DBT_PROJECT_FILE}`,

Finally, I don't see any references to DBT_PROJECT_DIR in the code, maybe that's related to the issue?

Update: I can confirm that using full paths in my envvars, e.g.

DBT_PROJECT_DIR=/full/path/to/my_project
DBT_PROFILES_DIR=/full/path/to/my_project/my_profiles

[rapha-pereira]

Loved the PR @diegoquintanav!
Just a note here: the main issue is that the extension doesn't replicate the way that dbt core handles environment variables, even though it should, as explained in the documentation. dbt runs normally with my environment variables, but the extension doesn't.

[mdesmet]

@rapha-pereira : can you clarify in which way the extension doesn't handle environment variables as dbt
core ?

[rapha-pereira]

@rapha-pereira : can you clarify in which way the extension doesn't handle environment variables as dbt
core ?

dbt Core runs fine with DBT_PROFILES_DIR set, but the dbt Power User extension doesn't seem to recognize it, as I detailed in my comments above.