I need to load a chained reference as depicted below from a source system to the Data Vault.
I see two ways of achieving this:
Option 1: Create a custom joined address table in the Staging area and employ my standard Multi-Active Satellite loading workflow to load it into the Data Vault.
Option 2: Load
ADDRESS to the Raw Vault as Reference tables (as they lack business keys) and enforce the join as a business rule when loading the Business Vault.
Option 1 would contradict the principle of maintaining standardised loading procedures for incoming tables with minimal dependencies on other tables. Moreover, if I were to implement this particular join in staging, it would be logical to apply all other business rules as well. After all, why complicate the overall process and distribute different rules across different stages?
Option 2 would deviate from the principle of modelling the business rather than the source data. Indeed, what purpose does the Raw Vault serve if it merely replicates the source system with different table names?
I’ve noticed a similar question was discussed in [this post] some time ago, but I’m unsure about the conclusion reached. In our case, “fixing” the source system isn’t feasible, as it’s proprietary software beyond my control.