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
  • name of config is tbd
  • default is NULL (current behavior)

  snapshots:
    my_project:
      +dbt_valid_to_for_current_records: 'date(9999, 12, 31)'
  

• can set config as project default or for individual snapshot
• config can be equal to:
  • SQL statement ('NULL', hard coded date/timestamp, etc.)
  • [maybe] project variable that returns a SQL statement
  • [maybe] macro that returns a SQL statement
• if someone updates this config, we could:
  • throw an error (make them go in and manually update their existing snapshot)
  • update all historic "current" records to have the new value (would this be possible?)

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