You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hop.apache.org by "mhamedbenjmaa (via GitHub)" <gi...@apache.org> on 2026/03/25 20:05:53 UTC
[I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
mhamedbenjmaa opened a new issue, #6876:
URL: https://github.com/apache/hop/issues/6876
### What would you like to happen?
Is there a way to ake 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 ends 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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mhamedbenjmaa (via GitHub)" <gi...@apache.org>.
mhamedbenjmaa commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4135673966
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4137643250
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4132627469
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mhamedbenjmaa (via GitHub)" <gi...@apache.org>.
mhamedbenjmaa commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4137918743
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 ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mhamedbenjmaa (via GitHub)" <gi...@apache.org>.
mhamedbenjmaa commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4138465706
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 :)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
hansva commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4135937552
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4138076304
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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters closed issue #6876: [Feature Request]: Add ability to track deleted record in Dimension lookup/update
URL: https://github.com/apache/hop/issues/6876
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Add ability to track deleted record in Dimension lookup/update (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #6876:
URL: https://github.com/apache/hop/issues/6876#issuecomment-4180814731
The `Dimension Lookup/Update` transform can not determine the deleted status of natural keys it isn't offered as input.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org