NoETL – Data Vault Link Satellite tables (part 2)

This is the second part of the Link Satellite virtualisation overview (the first post on this topic is here), and it dives deeper into the logic behind Driving Key based Link Satellites. Driving Key implementation is arguably one of the more complex things to implement in Data Vault – and you (still) need to ensure you can cover reloads (deterministic outputs!), zero records / time variance and things such as re-opening closed relationships.

In the example of the employee that moves to another company (therefore ending the relationship with the previous company) as outlined in the previous post you want to support correctly recording the fact when the employee moves back to the previous company at some point! In the sample metadata this scenario is covered using a ‘customer’ that is associated with an active ‘offer’.

This is modelled as the LSAT_CUSTOMER_OFFER table with the customer as the Driving Key:

Driving Key LSAT model

First off we should analyse the query itself to virtualize / simulate this table:

Link-Satellite query for the driving key

Let’s start seeing how this works by closing and re-opening some relationships between the ‘Customer’ and the ‘Offer’. For the purpose of explaining the behaviour I uncommented the business keys (CUSTOMER_ID and OFFER_ID)  in the selection.

Output for Driving Key Link Satellites (1)

As explained in the previous posts we agree (through the model configuration) that if the ‘customer’ is associated with a new ‘offer’ this should end-date the relationship of that ‘customer’ with the previous ‘offer’. We can trigger this by introducing a new ‘change’ in our source data for customer 235892 to receive a new offer. I won’t bore you by running through the screens with the delta detection again – the process is the same and results in an additional record introduced to the PSA. This was literally a two-second job!

This action triggers the end-dating of the previous relationship for the Driving Key if we virtualise / simulate the result again as shown here:

Output for Driving Key Link Satellites (2)

As you can see the relationship key (hash) has changed for our driving key 235892, but, the timeline is maintained. It is a design decision to interpret things like this, but being a virtual environment you can make other choices here as you see fit. Now, to make things interesting we can test re-opening the (end-dated) relationship.

In the source this means updating the customer to match the original offer and detecting the changes as usual. After we process the data delta the result looks like this:

Output for Driving Key Link Satellites (3)

As you can see the relationship is now re-opened which is an interesting testcase to have. Hopefully this explains this particular mechanism and intended behaviour a bit! I’ll publish the tool for download shortly for those interested.

Roelant Vos

Roelant Vos

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.