Update instructions for env var management based on consistent support for `.env` file

[jtcohen6]

Contributions

• I have read the contribution docs, and understand what's expected of me.

Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/docs/configure-dbt-extension#set-environment-variables-locally

What part(s) of the page would you like to see updated?

Follow-up to #8071

We should recommend that users put environment variables in an .env file located at the root of their project, or wherever they run their project from. We are implementing consistent support for this file across Fusion CLI, Core CLI, and VSCode extension.

Additional information

Pending outcome of:

• [FEAT] Fusion CLI should load environment variables from root .env file dbt-fusion#946
• [Feature] dbt should load env vars from root .env file dbt-core#12106

[jtcohen6]

@dbeatty10 originally documented this correctly way back in 2022 - #2146

It appears that there's been a drift in documentation across these two snippets, which have been updated over the past few months:

• _connection-profiles snippet (correct)
• profiles.yml snippet

docs.getdbt.com/website/snippets/_connection-profiles.md

Line 16 in 2ce7672

dbt will search the current working directory for the `profiles.yml` file and will default to the `~/.dbt/` directory if not found.

docs.getdbt.com/website/snippets/_profiles.yml.md

Line 26 in 2ce7672

2. Project root directory — Project-specific credentials.

The former (correct) reflects the behavior in Core
The latter (incorrect) is the current behavior in Fusion -> dbt-labs/dbt-fusion#1205