Re-instate hard-deleted records during snapshot

[joelluijmes]

Context

With PR #2749, dbt now can detect hard-deleted records and mark these records as invalidated. However as rightly mentioned on the PR, when the record returns (i.e. many-to-many link table), dbt won't see it.

In any case, I'd welcome a new issue on the subject of handling hard-delete "returns" so we can move the discussion there and determine which is the most effective and concise approach.

Originally posted by @jtcohen6 in #2749 (comment)

Describe the feature

Deleted records should be added back to the snapshot.

Describe alternatives you've considered

State it as limitation in the docs <-- personally not a fan, we need this feature 😅

Additional context

Read the thread from the PR. Possible solution posted by by @seunghanhong in #2749 (comment) but as replied by @jtcohen6 seems kinda over-engineered.

Who will this benefit?

Anyone who was waiting for snapshotting hard-deletes in first place.

Are you interested in contributing this feature?

Yes no problem, I'll dive in today to see if I can find an easy fix. Nevertheless, open to any suggestions you have 😃

[seunghanhong]

Adding where dbt_valid_to is null by @jtcohen6 works great.

[joelluijmes]

Adding where dbt_valid_to is null by @jtcohen6 works great.

Sounds great, thanks for letting me know! Currently adapting test to re-instantiate a record in order to make a PR 😏