I assume everyone is aware of type II dimension concept. In type II dimension when we get an updated row we retire old row and insert new one. But what do you populate in the from_date —to_date attributes. ETL create/update datetime or when that particular row was retired in the source system?
The question can be asked in a different way… When a employee changes from one department to another department; While populating in the data warehouse type II dimension EMPLOYEE table… Do you use the actual date of his transfer or the ETL date when you are populating the table?
What are you doing in your ETL code?
I believe, it depends on the batch window when we are loading the data into the warehouse. If it is a daily load I would like to set to_date to processing date -1 when updating and from_date to processing date during insert.
If the load is not daily then I would like to have a separate date field like effective data which can replace the processing date in the first scenario.