I have a scenario where I need to model data tracked employee information. The source system employee table contains potentially many records per employee number, with 'effective start date' and 'effective end date' attributes ensuring uniqueness for a known point in time. This will track things like change in name, marital status, employment status etc. These date records can be future dated - such as a known employment end date.
What is the best way to handle this? Do I simply just load each record as a new satellite record, with these effectivity dates just as attributes of the satellite and have downstream business rules deal with interpreting what record is actually applicable at a point in time? As and when a new record is staged we can simply load the record as a new version of the satellite record. The interesting thing here is that the version will be valid for a particular date range that has nothing to do with the physical load date.
I was reading up on status tracking satellite and effectivity satellites, but I don't think they easily apply here. There is no absence of CDC data - the source employee table tells use the business date range the data applies as well as the physical system insert/update dates (so don't think status tracking satellites are relevant) and the employee table is not modeled as a link (which is relevant for effectivity satellites).
Any thoughts on approach options?
I want to ensure that any downstream target dimension will use the correct version of the employee record based on what the effectivity dates indicate rather than just relying on the load_dt.