Business key(BK) standardisation# BK to be used for hash key creation

Data Vault 2.0
, ,
1

image
image1708×640 47.6 KB

In this example, we are receive details of insurable entity ids from source and the records contain different levels of granularity as shown under ‘Source Data’ section.
A record may represent data for content item(first two rows where content item id is populated) or at location level(3rd row where location id is populated) and so on.
The lowest level populated in the row decides which level the record is for.
Lowest to highest: contentitem_id → building_id → complex_id → location_id

When storing the data in raw data vault template, we need to have a rule to identify the lowest level business key relevant for that record and accordingly generate the hash key in satellite. No data transformation/change is expected with this rule , rather the rule will help us to identify which id among(location id/complex id/building id/content item id) we should use to generate the hash key in the satellite based on the granularity of the record.

Are we allowed to perform such logic(for lowest level business key identification in my scenario) while loading data in raw data vault following data vault 2.0 guidlines?

2

I would not consider identifying the lowest populated level (content item → building → complex → location) and using that identifier to generate the hash key to be a violation of Raw Data Vault 2.0 principles. The logic is determining which business key the source record represents rather than transforming the business data itself.

However, I would first validate that the selected identifier is truly the business key. If Location, Complex, Building and Content Item are distinct business entities, then modelling them as separate hubs with links representing the hierarchy may be more appropriate. If they are all instances of a single business concept such as “Insurable Entity”, then deriving the business key from the lowest populated level is a reasonable approach, provided the identifiers are unique across all entity types. For example, a content item identifier should uniquely identify a single content item regardless of the location, complex or building to which it is related. If the same identifier can represent multiple business entities, then the lowest populated identifier alone may not be sufficient as the business key.

1 Like
3

Some ideas could be:

  1. I would For the single-hub design add hardcoded prefix type (and aux column with it on the hub as convenience), to avoid any possible conflicts of same id at different levels,:
| Lowest populated ID | Hash input                |
| ------------------- | ------------------------- |
| content_item_id     | `CONTENT;<content_id>`    |
| building_id         | `BUILDING;<building_id>`  |
| complex_id          | `COMPLEX;<complex_id>`    |
| location_id         | `LOCATION;<location_id>`  |

- Only the lowest populated ID goes in: A row with all four IDs populated hashes just `CONTENT;<content_id>`. The parent IDs are not in the hash, so a reorganisation doesn’t change the key. The parent IDs feed the hierarchical link instead.

- The prefix is a constant, not source data: It stays consistent with “no data change” from the original question, since the rule only picks which ID represents the record.

- Store the type in the hub too: Keep an `entity_type` column (or store the prefixed BK) alongside the raw ID, so the hash can be re-derived and audited..

  1. link table to reflect parent/child relationships (with effective_sat), and also take cares of reorganization/movements of locations at any level (set end_dt old relationship + insert new relationship row(s) with end_dt = null)
2 Likes