Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow custom date for dbt_valid_to in snapshots #10187

Closed
Tracked by #10151
gshank opened this issue May 20, 2024 · 3 comments · Fixed by #10780
Closed
Tracked by #10151

Allow custom date for dbt_valid_to in snapshots #10187

gshank opened this issue May 20, 2024 · 3 comments · Fixed by #10780
Assignees
Labels
snapshots Issues related to dbt's snapshot functionality user docs [docs.getdbt.com] Needs better documentation

Comments

@gshank
Copy link
Contributor

gshank commented May 20, 2024

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

Screenshot 2024-06-27 at 3 51 31 PM

@graciegoheen graciegoheen added the snapshots Issues related to dbt's snapshot functionality label May 21, 2024
@graciegoheen graciegoheen added the user docs [docs.getdbt.com] Needs better documentation label May 29, 2024
@graciegoheen
Copy link
Contributor

graciegoheen commented May 30, 2024

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
Copy link

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
Copy link
Collaborator

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
snapshots Issues related to dbt's snapshot functionality user docs [docs.getdbt.com] Needs better documentation
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants