Support referencing across YAML config (e.g. jinja suppport)

[MeltyBot]

[Migrated from GitHub issue to discussion.]

Premise

A dynamic expression capability such as jinja would often be helpful, especially for two use cases:

1. For one configuration node to ref() against another node in the meltano.yml config file(s).
2. For certain dynamic expressions to be expressed statically - for instance, calculating start_date config values relative to now().

Background discussion

Details

Migrated from GitLab: https://gitlab.com/meltano/meltano/-/issues/3112

Originally created by @kgpayne on 2021-12-09 10:31:49

---

In order to support resource references (#3108), it would be advantageous to support some kind of referencing or templating capability within the Meltano config. YAML reference support is available, but not as full featured enough to allow attribute selection or string formatting (e.g. to compile a resource URI from component user, password, host etc.).

As Meltano is built in python, Jinja comes to mind as an obvious choice. Meltano could provide a set of built-in macros for referencing config elements, akin to the ref and source macros provided in dbt.

plugins:
  prod-snowflake-load:
    config:
      account: "<snowflake account name>"
      database: "<snowflake database name>"
      region: "<warehouse region>"
      warehouse: "<snowflake warehouse name>"
      username: "<user name>"
      password: "<password>" # ideally this would be a ref to a secret value added separately, or reference an env var
      role: "<snowflake role>"
      session_parameters: "<session level parameters>"

environments:
- name: prod
  config:
    plugins:
      loaders:
      - name: target-snowflake
        config:
          # Macro usage: `ref('<config object key path>', '<config object attribute>')`
          db_name: "{{ ref('plugins.prod-snowflake-load', 'database') }}" # References resources defined above
          account: "{{ ref('plugins.prod-snowflake-load', 'account') }}"
          user: "{{ ref('plugins.prod-snowflake-load', 'username') }}"
          password: "{{ ref('plugins.prod-snowflake-load', 'password') }}"
          role: "{{ ref('plugins.prod-snowflake-load', 'role') }}"
          load_schema: "${TAP_NAMESPACE}_raw"  # Templated string, value pulled from env var at tap execution

Alternatives and Workarounds

As of now, there are two basic alternatives that solve most use cases:

1. For dynamic expressions such as relative dates, the current best practice is to calculate these and store as environment variables before invoking meltano. Sample code here: https://stackoverflow.com/a/71709771/4298208
2. To send the same value to multiple plugins, declare the needed inputs are environment variables as an env: {} declaration, and then pass the env var to all plugins which require it.

[MeltyBot]

View 5 previous comments from the original issue on GitLab

[ian-lewis-d]

I am currently working through a problem that requires me to deal with (at least) three separate source environments with corresponding targets.

In this case the ability to dynamically assign, or rewrite, the contents of meltano.yml would be very useful.

I might be saying this though because the handling of environment variable in meltano.yml is still somewhat rough. If that were solved I wouldn't be thinking of using jinja templating.

My case is that I have to load files from S3 -> Redshift for multiple sources. The data structures are the same but the S3 buckets and destination Redshift databases are namespaced.

Using tap-spreadsheets-anywhere I have to find a way to manage the namespaces.

eg.

      extractors:
      - name: tap-spreadsheets-anywhere
        config:
          tables:
          - name: users
            selected: true
            format: json
            path: $S3_BUCKET
            pattern: $PREFIX/platform-users/.*.singer.gz
            key_properties: [id]

Ideally I need to be able to provide a different $S3_BUCKET and $PREFIX for each environment. There are up to 80 tables in each environment so duplicating the meltano environment is not an appealing option.

[edgarrmondragon]

#8268 might fix that