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, coalesce_dbt_valid_to

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

[zendesk-cisenbe]

Is it within scope to consider an altogether alternative strategy to dbt_valid_to? For example, with the current snapshot implementation, you can't utilize between queries since dbt_valid_to of the "prior" record = dbt_valid_from of the next record. If we could offset these dates or timestamps between records, we'd unlock a more ergonomic query pattern:

id	created_date	updated_date	dbt_valid_from	dbt_valid_to
1234	2024-10-01	2024-10-01	2024-10-01	2024-10-02
1234	2024-10-01	2024-10-03	2024-10-03	9999-12-31

We also have a more complex use-case that alleviates some issues caused by extraction delays. For example, let's say that our extract failed for 2024-10-01:

id	created_date	updated_date	dbt_valid_from	dbt_valid_to
1234	2024-10-01	2024-10-02	2024-10-01	2024-10-02
1234	2024-10-01	2024-10-03	2024-10-03	9999-12-31

Even though we extracted the record for this new key a day late, we'd like to reflect when it was generated by the source system for this first record. For subsequent records, we would follow more standard logic by using the updated_date to generate new records and retire old records. This allows users to use dbt_valid_from and dbt_valid_to but ignore extraction delays, which can be common in high-volume systems.

Maybe this is too narrow to make it into dbt Core, but I figured I'd throw it out there.

[FishtownBuildBot]

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#6275