[Feature] Improvements to source properties handling

[geo-martino]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

We have a use case for dynamic assignment of database names for models which select a database name based on the currently authenticated user to allow for usage of developer-specific data sets during development. While we have been able to implement a custom generate_database_name macro for this functionality on models, we cannot apply this same logic on sources as dbt does not support using the generate_X_name macros for dynamic assignment of either database or schema properties for sources.

For context, we currently use logic similar to below to assign database names within sources properties files.

sources:
  - name: source_x
    database: "\
      reporting_
      {%- if target.name == 'dev' -%}
        {{ env_var('DBT_USER') }}
      {%- else -%}
        {{ target.name }}
      {%- endif -%}
      "

While this functionally works, we have to copy and paste this code for every single source property file in our project to reuse this logic for each source. This exact same logic is contained within our generate_database_name macro so it would be useful to be able to reuse that and not violate DRY for every new source.

I therefore suggest one of the following approaches to bring source configuration more in-line with model configuration to allow for this functionality with reference to the above example on how this would improve it.

• Have sources properties use the generate_X_name macros automatically. e.g.

sources:
  - name: source_x
    database: "reporting"  # This would then use the `generate_database_name` macro to use the same logic as above

• Allow usage of generate_X_name macros within source properties files to allow us to unify handling of these properties for both sources and models.

sources:
  - name: source_x
    database: "{{ generate_database_name('reporting') }}"

• If this is not possible due to design choices, bring parity of source properties with other resource types to allow for definitions at the project file level to pass down to all sources within a resource path as is possible with models. This will at least allow us to define the logic for sources once at the project file level.

sources:
  project_name:
    resource_name:
      +enabled: true
      +database: "\
          reporting_
          {%- if target.name == 'dev' -%}
            {{ env_var('DBT_USER') }}
          {%- else -%}
            {{ target.name }}
          {%- endif -%}
          "

Describe alternatives you've considered

Manually copying and pasting database jinja templates across all source properties which violates DRY.

Who will this benefit?

Anyone who uses dynamic database or schema assignment for sources in a similar way to models. Specifically this will benefit users who need developer specific data environment handling for sources.

Are you interested in contributing this feature?

Yes

Anything else?

I am aware there are stale discussions for all of the suggestions above as described here and here.

These discussions are stale and closed so I am consolidating these here in an effort to unify discussion on this issue.

[alison985]

Snapshots also have this issue. In each snapshot sql config block you HAVE to specify database to reference the generate_custom_database() AND specify schema even if schema name isn't dynamic. Took me days of troubleshooting to figure this out.

[Caitlin-Syntax]

I was redirected to Issue 4753 from Issue 6056 and landed here based on a comment left on Issue 4753. (And I see the OP on this thread also notes Issue 3662). I have effectively the same problem as the OP in this request but related to generate_schema_name, which we use to route dev work into a prefixed schema, e.g. [developer_schema]_[production_schema]. We need everything to flow into the same schema for both Sources and Models during development and testing.

In my specific use case, we only have one database in play, but each of our clients' data is stored in a dedicated schema and must be kept separate for security and integrity purposes. We use Redshift Spectrum+dbt External Sources to land raw client data files as tables in a schema per client (hence the need to be able to land a Source in a dev schema alongside downstream models if we're doing any client dev). We do have an uncommon setup in that we do substantial amounts of dev testing directly on our production database because we don't want to create any additional copies of our live client data for security purposes. The generate_schema_name macro supports this pattern quite nicely for Models, but we hit the same roadblock described in the original request for Sources.

From a DevOps perspective, this is frustrating and confusing because it means configs, and therefore the ETL construction process, work fundamentally differently for different dbt resources - the natural support of parallel construction and DRY-friendly structure is normally one of the strongest benefits of using dbt. I have gotten around this for now by adjusting the schema names directly in each Source file: "{{ '[client]_landing' if target.schema in [my_values] else target.schema ~ '_[client]_landing' }}". This is not outrageously problematic while I'm the only data engineer at a small startup and our number of Sources isn't huge, but as my team grows, it would be great to have one of the improvements described in the original request above.