Defining Data Model Quality Metrics for Data Vault 2.0 Model Evaluation

3.1. Defining Metrics for Data Vault 2.0 Data Model Quality Evaluation

To verify that there are no columns missing, we used table and column metrics as follows:

Satellite tables have optional columns if those columns are optional in the source database. Therefore, we cannot check the ratio of mandatory columns in Satellites, unless we compare them to those in the source tables. This can be carried out but the assumption is that ChatGPT follows the column definitions of the original DDLs. Based on the experiments so far, the following assumption holds.

Manually defined metrics:

20.

DTTA, Data types of Data Vault 2.0 technical columns are correct:

a.

In Hubs, DTTAB;

b.

In Satellites, DTTAS;

c.

In Links, DTTAL.

The last two defined metrics are to be carried out manually. There are two reasons for this: to be able to do this in a reliable manner, we would need to follow this with naming conventions, and ChatGPT is not able to follow those without adding RAG to the process. The reply from ChatGPT is in line with this decision: “Please note that this is a simplified representation, and in a real-world scenario, you might need to consider additional aspects such as data types, constraints, indexes, and any other specific requirements of your target database platform”.

We could have metrics for indexes (at least PK and FK), but since these should be a generic set of metrics and different relational database management systems (RDBMs) support and require different kind of indexing, we abstain from using indexes as an element of the set of metrics.

We could define different weights for each metric to measure the amount of work needed for them, but this would make the model more complicated. This can be carried out as future research if needed, as well as using RAG to verify business keys, or to give instructions on the content of a PK column and the Hashdiff column, for example.

3.2. Empirically Evaluating the Metrics

We started our experiments of creating a new set of DDLs for the Data Vault 2.0 data model by prompting:

“The DDLs for the database are following:

<DDLs of the source database>

Please generate the DDLs for the target database following the Data Vault 2.0 methodology”.

Then, we prompted engineered ChatGPT to address the flaws noticed by adding in the end of the prompt:

“Also remember that all Primary keys should be hash columns of type binary. Satellites should have a hashdiff column”.

Model5 requires much more manual work than Model4. However, the inclusion of two additional metrics in the 20-point system falsely demonstrates their equivalence. Based on this experiment, these measures should not be used, or their weights should be reconsidered. As we have acknowledged the simplicity of programmatically adding missing technical columns or correcting their data types, there is a compelling rationale to exclude metrics 19 and 20 from the set of metrics.

3.3. Manual Human Expert Review of the Models

A quick review by a human expert was performed for all models. The maximum score for a model was 20, as determined by the anticipated effort required to rectify the model. A model receiving a score of 20 points suggests that the model is suitable for use without modifications, whereas any score below 20 indicates imperfections in the model. The human reviewer uses their own expertise and experience to determine metrics.