[Feature] Support Jinja within custom dbt_valid_to_current configurations

[dbeatty10]

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

As originally proposed within #10187, allow config for dbt_valid_to_current to contain Jinja in both schema.yml files and dbt_project.yml:

• macro that returns a SQL statement {{ dbt.date(9999, 12, 31) }}
• project variable that returns a SQL statement {{ var('my_future_date') }}

Examples:

Macro that returns a SQL statement

models/my_model.sql

select 1 as id, {{ dbt.current_timestamp() }} as updated_at

snapshots/_snapshots.yml

snapshots:
  - name: my_snapshot
    relation: "ref('my_model')"
    config:
      unique_key: id
      strategy: check
      check_cols: all
      dbt_valid_to_current: "{{ dbt.date(9999, 12, 31) }}"

Project variable that returns a SQL statement

dbt_project.yml

vars:
  # SQL expression that is specific to a particular database
  my_future_date: "date('9999-12-31')"

snapshots/_snapshots.yml

snapshots:
  - name: my_snapshot
    relation: "ref('my_model')"
    config:
      unique_key: id
      strategy: check
      check_cols: all
      dbt_valid_to_current: "{{ var('my_future_date') }}"

Project variable that returns a Jinja expression

In my personal ideal world, this would also work, but we officially do not support Jinja within vars. However, I seem to recall that it may actually work in certain cases. So we may choose for or against this (or allow it to "accidentally" work rather than be intentionally supported).

dbt_project.yml

vars:
  # Macro that yields a SQL expression when rendered downstream
  my_future_date: "{{ dbt.date(9999, 12, 31) }}"

Jinja within dbt_project.yml

dbt_project.yml

snapshots:
    +dbt_valid_to_current: "{{ dbt.date(9999, 12, 31) }}"

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[tommyh]

@dbeatty10 - one silly question, would it be possible todo something like this:

snapshots:
  +dbt_valid_to_current: "{{ dbt.date(9999, 12, 31) }}"
  - name: my_snapshot
    relation: "ref('my_model')"
    config:
      unique_key: id
      strategy: check
      check_cols: all

If possible, that would be very helpful to keeping the snapshot configs DRY and preventing issues when someone forgets to add dbt_valid_to_current for new snapshots.

Note: there is a more general form of this question where it would be helpful to specify the config for snapshot(s) at the directory level, in the same way we can for materialization: table/etc. If I have to snapshot several tables from the same source, the snapshot config is super similar, and I can override as needed instead of repeating unique_key, strategy and check_cols for each snapshot. :)

[dbeatty10]

Thanks for pointing this out @tommyh ! 🧠

I've added an example for dbt_project.yml to the description accordingly.

Other configs

In terms of other configs like unique_key, strategy, check_cols, etc. try out this and let us know if it doesn't work for you: