[CT-377] Configure profiles_dir with a config file

[dbrtly]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

As per the documentation while this works:

$ dbt run --profiles-dir path/to/directory

and so does

$ export DBT_PROFILES_DIR=path/to/directory

I would prefer to set it in dbt_project.yml and forget about it.

[profiles-path]: directorypath

Describe alternatives you've considered

.env file in repo

Who will this benefit?

cli users

Are you interested in contributing this feature?

yes

Anything else?

No response

[iknox-fa]

Hi @dbrtly and thanks for the feature request! I'll add this as a backlog ticket and we'll get it prioritized as soon as it makes sense. In the interim, I've generally recommended .env files for these sorts of use cases in the past.

[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.

[lostmygithubaccount]

removing stale and clearing assignee, this sounds useful. perhaps we can pick this up for 1.4?

[jtcohen6]

Sounds a lot like the changes we made to support target-path and log-path (#5402). For consistency with those and other "global"/runtime configs, the precedence order would want to be:

1. CLI flag: --profiles-dir
2. Env var: DBT_PROFILES_DIR
3. Project config: profiles-dir: in dbt_project.yml

This is also related to @dbeatty10's proposed change, to look for profiles.yml in the current working directory IF a custom --profiles-dir is not set (#5411, #5717). Taking that into account, the updated precedence order would actually look like:

1. --profiles-dir command-line argument
2. DBT_PROFILES_DIR environment variable
3. profiles-dir property of dbt_project.yml NEW
4. current working directory of dbt_project.yml NEW
5. HOME directory of the user (i.e. ~/.dbt/)

[dbrtly]

:) yes please Thanks, Daniel

…

________________________________ From: Jeremy Cohen ***@***.***> Sent: Friday, September 16, 2022 8:27:13 PM To: dbt-labs/dbt-core ***@***.***> Cc: Daniel Bartley ***@***.***>; Mention ***@***.***> Subject: Re: [dbt-labs/dbt-core] [CT-377] dbt_project enhancement (Issue #4885) Sounds a lot like the changes we made to support target-path and log-path (#5402<#5402>). For consistency with those and other "global"/runtime configs, the precedence order would want to be: 1. CLI flag: --profiles-dir 2. Env var: DBT_PROFILES_DIR 3. Project config: profiles-dir: in dbt_project.yml This is also related to @dbeatty10<https:/dbeatty10>'s proposed change, to look for profiles.yml in IF a custom --profiles-dir is not set (#5411<#5411>, #5717<#5717>). Taking that into account, the updated precedence order would actually look like: 1. --profiles-dir command-line argument 2. DBT_PROFILES_DIR environment variable 3. profiles-dir property of dbt_project.yml NEW 4. current working directory of dbt_project.yml NEW 5. HOME directory of the user (i.e. ~/.dbt/) — Reply to this email directly, view it on GitHub<#4885 (comment)>, or unsubscribe<https:/notifications/unsubscribe-auth/ADLXPTOZIJF4OWF27LDIAEDV6RDQDANCNFSM5Q5NL42Q>. You are receiving this because you were mentioned.Message ID: ***@***.***> [CT-377]: https://dbtlabs.atlassian.net/browse/CT-377?atlOrigin=eyJpIjoiNWRkNTljNzYxNjVmNDY3MDlhMDU5Y2ZhYzA5YTRkZjUiLCJwIjoiZ2l0aHViLWNvbS1KU1cifQ

[jtcohen6]

Update: I realized that this might be very tricky because of the order in which dbt parses each config at run initialization. I think dbt needs to read and locate profiles.yml first, in order to set UserConfig, before it even starts reading dbt_project.yml. That would make this much trickier to implement.

We have been discussing moving UserConfig out of profiles.yml and into a dedicated .env / config file, which would also make sense.

[dbeatty10]

Agreed that the implementation would be quite involved!

Oooh, I like the idea of separating the current profiles.yml file into separate configuration files 🤩:

1. user configuration
   • config key in current profiles.yml
   • maybe named something like user_config.yml
2. connection configuration
   • <profile-name> keys in current profiles.yml
   • probably still named profiles.yml

@dbrtly how would you imagine using the proposed profiles-dir property of dbt_project.yml?

Are you aiming to specify the user configuration for the dbt project? Or the database connection configuration? Or both?

If you have multiple dbt projects, would you intend to point them all at a single shared profiles.yml file? Or would you point each of them at their own unique profiles.yml files (one per dbt project)?

If each project would have its own profiles.yml file, where would you put it? In the same directory as dbt_project.yml?

[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 comment on the issue or else it will be closed in 7 days.

[dbrtly]

My initial beef was annoyance at setting $DBT_PROFILES_DIR over and over. These days, I’m also using direnv to run gcloud auth login … so direnv is doing more than one thing.

If dbt looked for profiles.yml in the same directory as the dbt_project.yml file, I would be happy with that.

If you are going to change this, my perspective is that there should be an option to use a pyproject.toml file for compatibility with the wider modern Python ecosystem.

Something like:

[tools.dbt.config]
# https://docs.getdbt.com/reference/global-configs

send_anonymous_usage_stats = False
use_colors = True
partial_parse = False
printer_width = 90
write_json = True
warn_error_include= all 
log_format = json
debug = False
version_check = False
fail_fast = False
use_experimental_parser = False
static_parser = False

[tools.dbt.targets.dev]
# https://docs.getdbt.com/reference/warehouse-setups/bigquery-setup

type = bigquery
method = oauth
location = “US” # Optional, one of US or EU, or a regional location
# project = use $GOOGLE_PROJECT_ID
# actively discourage hardcoded secret in config

Github Actions treats Google project ID as a secret. It never prints it to the logs (always uses a dummy string). I find it a code smell that dbt encourages users to handle the ID with less care.

[Bilbottom]

Commenting to add my support for using the pyproject.toml file for project configuration 🚀

Like @dbrtly mentioned, the pyproject.toml file is becoming the standard way to configure project settings in the Python ecosystem

I'm less opinioned on how much stuff to put into pyproject.toml compared to dbt_project.yml/profiles.yml, but at the very minimum I would love to be able to specify the project_dir and profiles_dir so that I can avoid setting $DBT_PROJECT_DIR and $DBT_PROFILES_DIR each time I want to run dbt (same gripe as dbrtly!)

Relating this to the precedence order mentioned before, I'd tweak item 3 to the pyproject.toml file:

1. --profiles-dir/--project-dir command-line arguments
2. DBT_PROFILES_DIR/DBT_PROJECT_DIR environment variables
3. profiles_dir/project_dir property of pyproject.toml <- NEW
4. current working directory of dbt_project.yml
5. HOME directory of the user (i.e. ~/.dbt/)

With the dbt Mesh announcement, I think this will be increasingly more important as people move away from mono-repos (which would probably make item 5 above redundant, since it would now depend on the repo -- at least for project config)

This is already supported by things like the SQLFluff package's dbt-templater plugin:

[tool.sqlfluff.templater.dbt]
project_dir = <relative or absolute path to dbt_project directory>
profiles_dir = <relative or absolute path to the directory that contains the profiles.yml file>
profile = <dbt profile>
target = <dbt target>

[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 comment on the issue or else it will be closed in 7 days.

[Bilbottom]

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 comment on the issue or else it will be closed in 7 days.

Commenting to keep this alive

[dbeatty10]

Labeling this as triage for us to choose between one of the following approaches for this request:

1. Support a pyproject.toml file to read dbt project config
2. Add a profiles-dir property to dbt_project.yml
3. Status quo / do nothing
   • 1. Encourage using an .env or .envrc (direnv) file with DBT_PROFILES_DIR specified

[dbeatty10]

Currently, the parent directory for profiles.yml is determined using the following precedence [1]:

1. --profiles-dir CLI flag
2. DBT_PROFILES_DIR environment variable
3. current working directory (conventional file location 1)
4. ~/.dbt/ directory (conventional file location 2)

@jtcohen6 and I got a chance to discuss this briefly today. We decided a couple things:

• Our location for specifying dbt configuration is dbt_project.yml, so we're not planning to add an additional file format (i.e., pyproject.toml)
• Given that we already have a CLI flag, environment variable, and two conventional file locations, it's not a priority for us to add profiles-dir property to dbt_project.yml

So I'm closing this issue as "not planned".