Clear all

Key Value Pair Modeling in Data Vault

Posts: 487
Topic starter
Joined: 3 years ago

Something everyone should consider:

Key Value pair modeling is not easy in a relational / traditional sense.  It has nothing to do with Data Vault per-say, but more so about the fixed nature of Data Models in a traditional Schema-on-write (Create Table first, then load it) situation.

Here are my suggestions:

1) Move to JSON format if at all possible, and move to a platform that can handle JSON natively (SnowflakeDB, PostGreSQL, MongoDB, MarkLogic, etc..)

2) If that's not possible, then: stuff as much of your key-value pair into a text field or CLOB, extract ONLY the keys (to columns) that are necessary for performance to meet 80% of the user query demands, or at least 40% of the user query demands.  I would extract or pivot those keys to columns using the PIT and BRIDGE structures.  Then, index the key columns where the values live.

3) ALWAYS extract the business keys, and build Hubs and Links where appropriate.

JSON format is the most optimal, and the best practice, especially on a platform that can support it.  Unfortunately Oracle and SQLServer support it, but they are not the most optimal platforms to run with - to top it off, these two database engines struggle to perform with their JSON storage formats.

Hope this helps, Dan

1 Reply