I’m designing a data warehouse and have a Slowly Changing Dimension Type 2 table something like this:
And I have a fact table, which whilst it does have dates, doesn't really relate to the user dimension at any particular point in time, say a subscriptions table like this:
|SubsKey||SubsID||SubsType||UserKey / UserId ?||StartDate||EndDate|
|55501||SBP501||Premium||??? / 2||2021-08-01||2022-08-01|
|55502||SBB123||Bonus||??? / 2||2022-08-01||2022-09-01|
The subscriptions could be set up for dates in the future for example, or be changed retrospectively. Or alternatively we might have a fact table which doesn't have any date aspect at all.
In such cases is it reasonable for the fact table to reference the natural key
UserID instead of the type 2 dimension row key
UserKey? As it's not obvious which
UserKey ought to be used.
I suppose one might use whichever row key is current at the time the fact row is generated, but that would seem to be implying that the fact relates to a particular snapshot in time for the user, when in reality the fact doesn't really.