Hi everyone,
Currently we are facing the construction of DataMart that requires a Dimension SCD2 which attributes where loaded from several Sats owned to different Hubs.
These approach is triggered by the fact that bottom-up design was followed when Raw DataVault was designed. It supposes to get a more closed design to operational sources objects avoiding transform data previously in order to handle suitable BKs but spreading attributes of an entity (Employee per exemple) into several SAT from several attributes.
At this moment, we are facing the load of Employee SCD2 Dimension at Information Mart. It supposes that attributes sensitive to change and, consequently, to track their changes at Employee dimension are difficult to query in order to build the right timeline of changes at mentioned dimension.
Studying this case we were thinking about PIT table not just with LDTS from SAT of fixed HUB but include HK ir BK from hubs involved, tracing a timeline every time the attributes aimed to feed SCD2 attributes load a change at the associated SAT.
The issue is that the single mention about hybrid PIT/Bridge tables was seen at topic "What are PIT & Bridge Tables? but no more information was found about.
Seeking to adopt best practices about modelling DV and avoid refactoring I would appreciate information about this approach and with fields should consider the hybrid PIT
Thanks in advance