The problem I’m having
I have multiple staging models that provide attributes for my destination model/table record. This destination model doesn’t exactly fit into a dimension design, but that’s another matter.
The situation is that, for the KEY columns in play, not every staging model will have a record.
Key columns = Natural Key + Start Effective Date + System Date
When building a record joining all staging models, there are going to attributes that are null, if that model does not have a record for the KEY.
However, the ask is that we bring/carry forward the missing attributes from the record having the prior start effective date.
What I’ve already tried
I’ve explored snapshots, but the destination table is not set up to be a true type 2 dimension and thus having a snapshot between the staging /intermediate models and the final model might not work.
The deviation from a true type 2 in this instance is cause the destination table is designed to hold multiple records for the same natural Key + Start Effective Date + End Effective Date, with the System Date providing the uniqueness on the record.
The End Effective Date however is determined solely by the appearance of a new Start Effective Date value.
Example
Day 1:
Destination Table data
Natural Key: ABC
Eff Date: 1/1/2023
System Date: 1/2/2023
Stage_1_attrbute: XYZ
Stage_2_attrbute: 123
End Eff Date: NULL
Day 2:
Stage_1 : no records for ABC
Stage_2 record:
Natural Key: ABC
Eff Date: 1/3/2023
System Date: 1/4/2023
Stage_2_attrbute: 456
Intermediate model results using Stage 1 & 2:
Natural Key: ABC
Eff Date: 1/3/2023
System Date: 1/4/2023
Stage_1_attrbute: NULL
Stage_2_attrbute: 456
Destination Table data (two records now)
Natural Key: ABC
Eff Date: 1/1/2023
System Date: 1/2/2023
Stage_1_attrbute: XYZ
Stage_2_attrbute: 123
End Eff Date: 1/2/2023 – End date populated.
– New record
Natural Key: ABC
Eff Date: 1/3/2023
System Date: 1/4/2023
Stage_1_attrbute: XYZ – Carryforward from prior record
Stage_2_attrbute: 456
End Eff Date: NULL
TIA