[Feature] Allow dbt init to use profile templates with nested configurations

[edgarrmondragon]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Some adapters may have complex profiles with nested objects and arrays, and currently it's not possible to capture user input of such values with dbt init and profile_template.yml

An example of such a profile is what dbt-sqlite requires:

default:
  outputs:

    dev:
      type: sqlite
      threads: 1
      database: <database name>
      schema: 'main'
      schemas_and_paths:
        main: '/my_project/data/etl.db'
      schema_directory: '/my_project/data'
      extensions:
        - '/path/to/sqlite-digest/digest.so'

    prod:
      type: sqlite
      threads: 1
      database: <database name>
      schema: 'main'
      schemas_and_paths:
        main: '/my_project/data/etl.db'
      schema_directory: '/my_project/data'
      extensions:
        - '/path/to/sqlite-digest/digest.so'

  target: dev

Source

• The extensions array will have as many entries as SQLite extensions the user needs.
• The schemas_and_paths object can have any number of key-value pairs.

So, my proposal is to add a new yaml type option to profile_template.yml prompts and use that to parse input as YAML that is written as such to the profiles file.

Describe alternatives you've considered

1. Make arrays and objects fixed in the profile_template.yml file. This makes the user edit the ~/.dbt/profiles.yml instead of relying solely on input.

2. A different (and harder to implement) spec where object and array prompts are walked recursively based on something like a JSON schema for the profile.

Who will this benefit?

Users of dbt-sqlite (or any adapter with such configuration requirements) initializing new dbt projects.

Are you interested in contributing this feature?

Yes!

Anything else?

If you're interested, I can start a PR soon 😄

[NiallRees]

Hey @edgarrmondragon, I can't speak for the maintainers, but when I added this in #3625 the only reason for not supporting nested configs was that I didn't know they existed! I don't see any reason not to support it though. This is where the changes will be needed: https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/task/init.py#L123-L160

[edgarrmondragon]

Thanks @NiallRees! I've started a draft PR with I think should be enough for YAML objects to parsed or passed through. Let me know if you think I'm on the right path 😄

[NiallRees]

A different (and harder to implement) spec where object and array prompts are walked recursively based on something like a JSON schema for the profile.

Would be very interested to see any ideas for this!

[jtcohen6]

@edgarrmondragon Thanks for opening, and @NiallRees thanks for jumping in with the pointer to the codepath!

I also wasn't aware of any profiles that used nested properties, outside of the not-exactly-recommended keyfile_json option on BigQuery (docs). I see no reason to actually oppose this capability.

A different (and harder to implement) spec where object and array prompts are walked recursively based on something like a JSON schema for the profile.

Would be very interested to see any ideas for this!

Ooh, this does sound neat — in particular, it sounds like we should be using a jsonschema generated from the adapter plugin's Credentials object (e.g. for Postgres). That object is missing some information, such as which fields should be included during initialization (probably not every single option), and inter-relationships between fields (which ones require / "turn on" other fields), but I'm sure we could find some way to code those up.

For now, I think it's fair to say that these kinds of nested fields are an edge case, and as long as there's some reasonable way to pass their input, it's good enough.

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

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest; add a comment to notify the maintainers.