History - [SCD Is Tombstone] - Option Push data last active record into Tombstone record

Hi Jan,

I am not sure that I fully understand. The [SCD Is Tombstone] is a flag, set on a record that no longer exists in the data source (or is not being loaded anymore) and contains the last known version of the record, like you request. 

Please elaborate, If I miss your point. 

Hi Thomas,

I see the record as displayed in the attached image. The new tombstone record only contains the business key other fields are empty

(discovery Hub 18.6.1.64)

Hi Jan,

Interesting. This is not how it looks on my side. Please take a look at this video and let me know if you see any major difference in my setup compared to yours. https://ilos.video/SJu9ES

Hi Thomas,

It happens when the option  'Create a new record when a deleted record appears in the source' is selected. I assumed the behavior would be the same when this is not selected. i.e. create a new empty record marked as tombstone.

It appears we can choose between either just mark the record as tombstone and then loose history when a record has been deleted and then reappeared or keep deletion history but have a tombstone record with empty fields.

I didn't really get that from the description.

I would prefer the new tombstone records with the ability to push the data from the last active record.  

Hi Jan,

I was able to reproduce the behavior you see when I changed the setting for "Create a new record when a deleted record reappears in the source".

I agree with you that the tombstone record should contain the last known values! 

Hi Jan,

Before starting development on this, we did do another review and we came to the conclusion that the current implementation is the correct implementation. 

When performing lookup against the history table to obtain the information from the time of a transaction it is important to get the correct result. If the transaction took time in a period where the historical record was deleted, then NULL value is the correct result. 

If you need to have the last know value, then you can use a post-execution script action to apply those to the tombstone records. 

Thanks for using our software and sorry that I gave you a wrong answer to beging with! 

Hi Thomas,

I understand why it works like that. I would like the option to be flexible.

Either a boolean field 'IsLastKnownValue' would be nice or the ability to select an option to push the data from the last active record. 

I don't mean to change the current default behaviour. 

Hi Jan,

Do you need this flag only for conditional lookups? Because if it is only for conditional lookups, you can instead choose lookup operator Top, and then e.g. order by [DW_Id] Descending, as well as add [SCD Tombstone] = 0 to the joins.

Hi Radek, 

Unfortunately it's for some reports as well where the customer wants to see the latest values that were active

Thanks for the tip on the lookup though. I can use that too.

Hi Jan,

For this functionality I think the proper solution is to disable the "tombstone record" option, then you just get the tombstone flag on the last active record.

Thomas is right that when you add a tombstone record, in order for it to be correct it must have NULL values for all (non-key) attributes.

Hi Steven,

Yes, you only loose history about when a record was deleted then. If that is not a problem this works.