Question

2. Optional description of the table​

Answer 1

follow me.....

Daniel Linstedt, Michael Olschimke, in Building a Scalable Data Warehouse with Data Vault 2.0, 2016

12.2.3.1 T-SQL Example

The approach to virtually providing code and description tables has the same requirements as outlined in section 12.2.1. If any of these conditions are not met, or if the complexity of the view is becoming too high, the table could be materialized using the following DDL statement:

Sign in to download full-size image

As all other reference tables, the code and descriptions table relies on a clustered primary key because of the natural key in the group and code columns. It is possible to load the code and descriptions table using a MERGE statement again, but it becomes much more complex, due to the multiple source tables in the staging area:

Sign in to download full-size image

Sign in to download full-size image

This MERGE statement does not delete any codes in the target code and description table. The UNION ALL is still included in the SELECT clause in order to avoid dealing with parallel running of INSERT statements on the same table, for example by adding locks on the target table in the loading processes.