[CT-3179] [Feature] Handle multiple changes in one snapshot run

[tajsg18]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

One run of dbt snapshot can currently only handle one change per unique key. It would be useful to support multiple changes to data in one dbt snapshot run. This would mean supporting also "immutable" tables (that contain new rows for every mutation of a key), not just mutable tables.

Even though "immutable" tables already keep history, it's not in an SCD2 format.

See an example below.

Input data:

name	age	update_time
Emily	30	2023-10-03T15:12:45.278Z
Emily	31	2023-10-03T15:13:02.056Z

Result of dbt snapshot:

name	age	update_time	dbt_scd_id	dbt_updated_at	dbt_valid_from	dbt_valid_to
Emily	30	2023-10-03T15:16:14.187Z	29ae89d7a22d602801c0551b5bcaf93a	2023-10-03T15:16:14.187Z	2023-10-03T15:16:14.187Z	null
Emily	31	2023-10-03T15:16:17.841Z	45fc06e3fb2503ed7ed5c8e841dc7476	2023-10-03T15:16:17.841Z	2023-10-03T15:16:17.841Z	null

What I would expect:

name	age	update_time	dbt_scd_id	dbt_updated_at	dbt_valid_from	dbt_valid_to
Emily	30	2023-10-03T15:16:14.187Z	29ae89d7a22d602801c0551b5bcaf93a	2023-10-03T15:16:14.187Z	2023-10-03T15:16:14.187Z	2023-10-03T15:16:17.841Z
Emily	31	2023-10-03T15:16:17.841Z	45fc06e3fb2503ed7ed5c8e841dc7476	2023-10-03T15:16:17.841Z	2023-10-03T15:16:17.841Z	null

I propose to introduce this change with a configuration or snapshot strategy, as it would definitely produce different behavior and it could be a breaking change for current users (as also noted in 7138).

Describe alternatives you've considered

An alternative solution is to build a macro to transform "immutable" data to SCD2 format.
The disadvantage of this approach would be that different functionality would have a similar purpose: transform data to SCD2 format.

Who will this benefit?

Anybody who would like to bring "immutable" data to the same SCD2 format as dbt snapshot outputs.

Are you interested in contributing this feature?

If there is interest, yes.

Anything else?

No response

[dbeatty10]

Thanks for a fantastic write-up @tajsg18 ! 💡

You did a great job of putting your finger on the most important piece here:

This would mean supporting also "immutable" tables (that contain new rows for every mutation of a key), not just mutable tables.

Even though "immutable" tables already keep history, it's not in an SCD2 format.

We recommend the alternative you described

We're inclined to follow the Unix DOTADIW principle ("Do One Thing And Do It Well") for snapshots and not expand it to cover the case you described. i.e., keep the intended domain of dbt snapshots restricted to capturing changes of current database tables rather than expanding it.

But you presented a totally legit and super common use-case!

To cover it, we recommend the alternative you described for tables that already have a full history of changes over time (populated from some kind of CDC source or audit / history tables). e.g., using lead/lag window functions to transform that source data into your desired format (whether that is an Type 2 SCD like snapshots, or some other SCD or CDC format).

Personally, I'd love to see someone contribute a dbt package of temporal macros and/or materializations related to SCD and put it up on the dbt Package Hub.

I'm going to close this as "not planned" for the reasons mentioned above.