This is a bit tricky to search on keywords, so my apologies if this question exists.
Let's say I have a standard type 2 slowly changing dimension, modeled with a Startdate and Enddate column. The record with the NULL Enddate is the current version of the dimension record.
I understand it's pretty straightforward when I detect what will be a fact table insert from the source data. The new fact table record is simply inserted mapped to the most current dimension record based on matching the business key AND the dimension record where the Enddate = NULL.
I'm having a little trouble figuring out what to do when there's an update to a measure in the source system, what will amount to an update, not an insert, in my fact table. It seems I only have the business key to join on, and the existing record in the fact table could point to a previous version of the dimension record. I'm unsure of how to grab the correct surrogate key from the dimension and perform the fact table update.
I can provide more detail if needed.
Thanks in advance.
Do you have any insert or create date on source table? You can use that to do
select * from dim where src_dt
between dim.startdate and dim.enddate and keys = src.keys
and return correct dimension row. if you are using SCD.
When you do lookup you should use dates along with natural keys to get correct dimension row and not select most current dim row for both inserts and updates.