I can't seem to find a clear answer for handling my situation. 50% of my source table data for link satellites are based on relationships between 2 or more business keys AND an effective date. I don't believe this would be an issue, except that multiple records for same Link HK and different effdate (which from everything I have read and heard in class, should NOT be used as a link dependent child key); have the same load date. The time is also the same, since it is historical data and I don't have a timestamp. For example a provider is related to a facility; there are separate spans, but the load date/time is the same in the satellite which causes a violation. For example, user builds provider A linked to Facility B active 1/1/2019 - 3/31/2020 and then another linked span for 7/1/2020 - open. Both records are built on 12/1/2020 (loaded to DV on this load date at the same time). Since the Link between Provider A and Facility B is the same HK and and load date is the same (PK of satellite is HK and Load Date), I have a violation that won't allow both records. How do I handle this?
Any help is appreciated!
Thank you,
Erika