We are building the test framework described in the book “The Data Vault Guru”. We have an issue with the supplied SQL code of the “Staged to target check” of SKEY and BKEY. For example on page 418 the SQL code of HUB_SKEY_SGTG: the last line of the SQL code in the book is:
and h.DV_HASHKEY_{{HUB-TABLENAME}} is null
This code is not working correctly in Oracle at our site. If we substitute the code with following line it is working correctly:
where h.DV_HASHKEY_{{HUB-TABLENAME}} is null
In the Orphan checks a similar line of code is using where instead of and.
Is this a glitch in the code or are some DBMS’es capable of using that code?
I built those based on a platform I was working on that is ANSI-SQL compliant
If you need to adjust those queries to suit your needs… you have my permission to do so.
I think I highlight that point in the book also, a lot of the RI tests “prove” that the DV model is “in-tact”. And later in the same chapter I discount a lot of the tests if the features are natively supported by the platform.
I hope that makes sense.
1 Like
Thanks, it makes sense. Oracle has deviations from ANSI SQL so we adjust where needed.
We have build all tests in the test framework. We are planning not to set any constraints in the database. We insert the testresults to a common table to store the testresults. We added one extra test: ‘number of rows > 0’ and keep track of the count(*).
We already have found some data quality issues and an initial load issue with our first data source. It’s a very powerfull tool. I think we will also build a data profiling tool like this to be able to analyze data during the design phase.
you could extend this framework for recon, although built in Snowflake you could adapt this to anything and throw a dashboard on top of it
I also did an extension to the framework here: What is the Shape of your Data?. … and why is it important. | by Patrick Cuba | The Modern Scientist | Medium