From/To Dates and SCD Type 2 Process.

If you have:

1. An eligibility table that contains a from and to date that tracks eligibility status for a person.
2. Several fields in this table are tracked for type 2 changes and some of these changes can happen retroactively to eligibility periods which have already passed. For example, a change to someone's old 2011 eligibility can happen in 2013.

Is it a good idea in the ETL to automatically expire the SCDs for records where the to date of the eligibility has already passed? There is a thought that it may be a good idea to do this to save processing time since there are quite a few of these that will never be updated again.
We are concerned however that if we follow this approach it will not work correctly. Can the ETL for a Type 2 SCD process be tweaked so that changes that happen to records which already have an expired SCDToDate be recorded?

Any advice on handling this situation?


Thanks!

I know this post has been around for a while but as no-one has responded I thought I would give it a go...

As a principle, you would never change the 'to' date on an SCD Dimension unless you were creating a newer version of it.
If I have understood your post correctly, the business key of your eligibility dimension is the person + their eligibility period? So the SCDs show how the attributes for this business key change over time e.g.

Person Elig_Start Elig_End SCD_Start SCD_End SCD_Current
A 01/01/2010 31/12/2010 15/12/2009 21/09/2010 N
A 01/01/2010 31/12/2010 22/09/2010 31/12/3000 Y

I'm not sure how expiring the SCD record would speed up your processing as I assume you only use the SCD dates to find the Dimension record applicable at a point in time e.g. "event_date BETWEEN SCD_start and SCD_End" - so whether SCD_End = 31/12/3000 or 14/01/2014 should make no difference to the speed of processing.

As an aside, and obviously I have no idea of your wider business processes and how you use eligibility, but isn't a person's eligibility a fact about that person that could be tracked in a fact table rather than a dimension?

Nick