Resolving joins in staging

Data Vault 2.0
, ,
1

I need to load a chained reference as depicted below from a source system to the Data Vault.

image
image763×730 28.1 KB

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 EMPLOYEE_ADDRESS_REF, EMPLOYEE_ADDRESS_REF_FIX, and 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.

2

Think of REF tables as nothing more than tables used to enrich your data for consumption, i.e your information marts. You’re right, we generally allow for pre-staging joins in case of staged content not loaded when a table needs it for a join. But there are exceptions, your model looks almost complete