Metadata
- Author: Dan Linstedt
- Full Title:: DV2 Sequences, Hash Keys, Business Keys – Candid Look
- Category:: 🗞️Articles
- URL:: https://web.archive.org/web/20220112051906/https://danlinstedt.com/allposts/datavaultcat/dv2-keys-pros-cons/
- Finished date:: 2023-08-14
Highlights
the first issue leads to slower sql joins and slower queries (View Highlight)
if a hash key is chosen for implementation, then a hash collision strategy must also be designed. this is the responsibility of the team (View Highlight)
if given the choice between surrogate sequences, hashes or natural business keys – natural business keys would be the preference. the (View Highlight)
the issue with a multi-part business key is with performance of a join. there are multiple mathematical tests and quantitative results that show time and time again that multi-field join criteria is slower than single field join criteria. it only goes “slower” in large volume or big data solutions. at this point, perhaps, a hash key or surrogate sequence in the data vault may be faster than a multi-field join because it reduces the join back to a single field value. (View Highlight)
another alternative is to concatenate the multi-field values together thus forming somewhat of an intelligent key (View Highlight)