[Feature Request]: Add ability to track deleted record in Dimension lookup/update

[mhamedbenjmaa]

What would you like to happen?

Is there a way to make a difference by adding a check box to track deleted record like the feature in dbt for snapshot

hard_deletes: invalidate

this way the target table will just put an end date to the deleted row from the source when not found

usually with classic ETL you need to get the source keys, compare manually with destinations keys, flag missing one etc.....

since you are already preforming lookup, only missing couple of line code to add to get the feature, would be to flag missing rows from source and flag them with an end date

Imagine how hard an SSIS developer or a DataStage would cary to have a checkbox like that :D

thanks

Issue Priority

Priority: 2

Issue Component

Component: Transforms

[mattcasters]

This is not possible. dbt snapshots can't really make the same SCD like this transform does either.
However, it is possible to compare 2 source data snapshots with the Merge Rows transform and that one will flag the deleted records.
Unless required by privacy regulations I would never hard_delete records from an SCD either, but that's a preference, not a technical issue.

[mhamedbenjmaa]

Just looking for a simple way to detect deleted rows—I mentioned dbt only as an example of the pattern.

Manually detecting deletions has always been a tedious process, and I really like the approach of simply disabling the row (adding an end date or flag) rather than physically removing it. That gives the developer the flexibility to decide how to handle those rows based on business requirements, rather than forcing a hard delete.

Since the lookup logic is already there, adding this detection would be a small but powerful addition.

[hansva]

But the lookup logic isn't there as far as I understand your request. You want to flag the rows that were not touched after the Dimension lookup/update completes. This would still require a complex query, as far as I know. It's not that we do a preload of all records; we only look up the records that are provided to the transform.

Unless I am not understanding the logic you would like to have applied. But you could write an update query to do this. Add a last_seen field that is updated on every run and then you could find records that do not have this and close them.

[mattcasters]

The dbt option is an option of the snapshot function. It is equivalent to comparing 2 data set snapshots using a Merge Rows transform. Please note that the SCD Type 2 built by dbt is equivalent to using a Group By transform across all fields (sorted by ID and timestamp) and then by aggregating the timestamp field to get a from/to date range. It's a quite simple process. The Dimension Lookup/Update is not needed for that.

[mhamedbenjmaa]

Ok here the objective

this is source
ID ATTR1 ATTR2
K10 A X
K20 B Y
K30 C Z

let's say in the dimension Lookup/Update you set Key to ID and both fields are set to INSERT

the destination will be

ID ATTR1 ATTR2 SK version date_from date_to
K10 A X 1 1 1900 2199
K20 B Y 2 1 1900 2199
K30 C Z 3 1 1900 2199

so if i change an attribute in the source, it will be lets say

ID ATTR1 ATTR2
K10 A X
K20 B T
K30 C Z

the destination will be

ID ATTR1 ATTR2 SK version date_from date_to
K10 A X 1 1 1900 2199
K20 B Y 2 1 1900 2199
K30 C Z 3 1 1900 2199
K20 B T 4 2 1900 today

so far so good this is how all of them works

Now, lets say I remove K30 from the source , all ETL in the world will leave K30 unchanged in the destination,so is hop as of today, here where we could shine with just a little checkbox that says invalidate_missing record and the result would be

ID ATTR1 ATTR2 SK version date_from date_to
K10 A X 1 1 1900 2199
K20 B Y 2 1 1900 2199
K30 C Z 3 1 1900 today
K20 B T 4 2 1900 today

that's it , this is how i see it , or it can be another binary column that says suppresed_from_source , it up to you

see how nice this is ?

[mattcasters]

In that case I understood it correctly the first time. Are you suggesting that we should query the source database for every natural key in the dimension table to figure out which one doesn't exist anymore? Performance might suffer /S.
How do you envistion your logic to work exactly?

[mhamedbenjmaa]

humm i see your point

but this case works only if we are dealing with type 2 SCD means that we do have a unique key in source and a unique key in dest and we are actually doing a lookup

is there a way to do the lookup in both ways ? missing rows from source to dest are insert and missing rows from dest to source are invalidated row ?

i dont know the source code for this module , i"m just peculating, you can point me to it , maybe my freind claude can take a look and suggest smthing :)

[mattcasters]

The Dimension Lookup/Update transform can not determine the deleted status of natural keys it isn't offered as input.