Support incremental loading for scd2

[gnilrets]

Feature description

Currently, the scd2 write disposition requires running a full extract of the source data in order to work; in other words, it does not support incremental loads. The main justification for this seems to be that doing a full extract is the only way to detect hard deletes. While true, there are plenty of situations where we would like to perform incremental scd2 loading where hard deletes are not a concern. At the moment, the workaround we have is to do a regular merge incremental load, followed by a dbt snapshots. But that effectively doubles the amount of storage needed in our warehouse and introduces an extra computational step.

Are you a dlt user?

Yes, I run dlt in production.

Use case

We have data in a SaaS product that contains large, mutable data and the vendor enforces rate limiting. Doing daily or more frequent regular full extracts is not feasible or cost effective. Having an incremental scd2 solution (even if it didn't manage hard deletes) would be very valuable to us.

Proposed solution

The scd2 write disposition supports incremental merge, with the caveat that hard deletes are not supported when doing so.

Related issues

No response

[rudolfix]

@gnilrets thanks for feedback! Could you tell us how those merges should work? Currently all primary keys that are not present in the input datasets are retired. How would you limit that? We have merge_keys that could be used to do that ie. you'd be able to run scd2 for a given day or other partition. Is this what you mean? Or we should not do hard deletes at all (so your dataset can only be inserted or updated)?

[gnilrets]

Yeah, in this case, just don't do hard deletes.

[rudolfix]

@jorritsandbrink do you have a good idea on how to implement it in terms of user interface? add append-strategy to append? or use merge_key to limit which records are retired (empty key - no records are retired...) etc.?

[jorritsandbrink]

@rudolfix

append-strategy to append?

Not fitting because updates are involved.

use merge_key to limit which records are retired (empty key - no records are retired...) etc.?

Not very intuitive.

My suggestion:

@dlt.resource(
    write_disposition={"disposition": "merge", "strategy": "scd2", "retire_if_absent": False}
)
def my_incremental_dim_data():
    ...

where retire_if_absent defaults to True.

This is consistent with other scd2 config (active_record_timestamp/validity_column_names/etc.)

[gnilrets]

Yep, @jorritsandbrink 's suggestion is what I was thinking too.