Data vault schema evolution question

Hello, I have a novice (hypothetical) question about a schema evolution scenario to gain a better understanding of how a data vault should be operated.

Let’s assume I’m developing a database for the human resources department of an IT-resales company. I initially create a simple model with HUB_EMPLOYEE having two multi-active Satellites:


This setup functions well for several years. However, at one point, employee certifications become a crucial aspect of the business (determining vendor discounts, etc.), prompting the need to expand that part of the schema.

My questions are about querying the certifications after the expansion:

  • Do I need to continue joining the old SAT_CERTIFICATE to all my queries involving certifications indefinitely, or is there a more straightforward approach?

Furthermore, if the former is true:

  • Is there a standard way to document which queries need to include such historical joins and which don’t? I imagine it might become somewhat confusing if there have been multiple schema expansions.

I suppose it depends if you still need to populate the old satellite table.

If you have refactored the content this way, does that suggest SAT_CERTIFICATE contains the certificate id you now want to be loaded to HUB_CERTIFICATE?
If so, it seems more like a refactoring and you could rather benefit from simplifying the model instead of “evolving” it in this way.

No need to keep populating SAT_CERTIFICATE with duplicate info after the expansion.

Do I understand correctly that the easiest course of action would be to copy all the existing data from SAT_CERTIFICATE to SAT_CERTIFICATE_INFO and drop the original SAT_CERTIFICATE altogether?

Probably – sounds like a refactor + migration

Hi Mart,

Looks like Patrick has provided you with your answer hopefully :slight_smile:

Can i ask how you created your Data Vault relationship diagram? Which tool did you use?

Thanks in advance.


Hi, Carl
This was done by Microsoft Visio, using the Data Vault stencil from here:
(With minor modifications to fonts, sizes and colours.)

That’s great. Thank you @Mart :slight_smile: