Allow custom date for dbt_valid_to in snapshots

[gshank]

Description

Right now, dbt_valid_to is set to NULL for current records.

Some people, want to set dbt_valid_to to an arbitrary future date (so that their snapshot will work for a join, corporate standard, etc.).

The way they do this today, is create a view on top of their snapshot:

select 
    coalesce(dbt_valid_to, date(9999, 12, 31)) as fixed_dbt_valid_to,
...
from {{ ref('my_snapshot') }}

Acceptance criteria

• new config to define what you want to set dbt_valid_to for the current records in your snapshot
  • default is NULL (current behavior)

    snapshots:
    my_project:
    +dbt_valid_to_current_indicator: "to_date('2024-05-10')"

• can set config as project default or for individual snapshot
• config can be equal to:
  • SQL statement ('NULL', hard coded date/timestamp, etc.) "to_date('2024-05-10')"
  • [ideally also] project variable that returns a SQL statement '{{ var('my_future_date') }}'
  • [ideally also] macro that returns a SQL statement '{{ dbt.date(9999, 12, 31) }}'
• if someone updates this config, we:
  • because we rely on knowing what dbt_valid_to is equal to for current records for the insertion logic when snapshotting, the existing snapshot must be updated before we can proceed
  • how can we throw an error:
    • if there was a dbt_is_current column (as has been requested in another feature request) we could run a validation check to say "is every row with is_current = true also have dbt_valid_to equal to whatever I have set for my dbt_valid_to_for_current_records config); could have a performance implication
    • [not everyone uses CI] similar to how we handle versioned model contract changes, if someone updates this config and runs their snapshot using deferral and state:modified we should throw a warning/error that this config has been updated (make them go in and manually update their existing snapshot)

Notes

Ideas for how to handle config changes:

• ["silent failure"] we just use the new config for all future inserts
• [out of scope, but potential future enhancement] update all historic "current" records to have the new value (would this be possible?)
• ideas for config name: dbt_valid_to_for_current_records, dbt_valid_to_current_records, dbt_valid_to_default, dbt_valid_to_current_indicator, dbt_valid_to_current, dbt_valid_to_current_date, dbt_valid_to_current_marker

[graciegoheen]

Rough draft of YML:

{{
    config(
      target_database='analytics',
      target_schema='snapshots',
      unique_key='id',

      strategy='timestamp',
      updated_at='updated_at',

      #ideas
      coalesce_dbt_valid_to='date(9999, 12, 31)',
      dbt_valid_to_for_current_records='date(9999, 12, 31)' #default is NULL
      future_date_dbt_valid_to=false,
      coalesce_dbt_valid_to=dbt_max_date(),
      coalesce_dbt_valid_to=var('dbt_max_date')

    )
}}

• idea: future date value
  • max date that the warehouse supports
• if we can’t do that, allowing you to write SQL
• can set at project level as well
• how will we handle if someone updates this config? will we go back and edit historical records?