Alternative strategy for dbt_valid_to

[dbeatty10]

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.

Originally posted by @zendesk-cisenbe in #10187 (comment)