0

I'm trying to model a kind of INFORMATION_SCHEMA using data vault approach for detecting of data lineage and I'm a bit confused about how to implement the relationship between tables and their columns. Some columns have the same name, like ID or Name, but different description and meaning.

I would like to have 2 HUBs, one for tables and one for columns, to be able to create hierarchical links for each of there HUBs for storing these origin objects and formulas for transformations.

If i would create a HUB for tables and a HUB for columns, what should i use as a business key for columns, because the column names is not unique? If i put columns into a satellite table of HUB_TABLE using multi-active satellite modelling approach, i can not simply create a hierarchical link for this satellite.

Any better ideas or suggestions?

What is the best practice to model such kind of nested structures with 1:n relationships? (DB -> SCHEMA -> TABLE -> COLUMN)?

korbee82
  • 107
  • 1
  • 6
  • what do you mean by "i can not simply create a hierarchical link for this satellite." ? Why do you want to create a hierarchical link, what is the business case? – MLeblanc Oct 24 '21 at 04:49
  • 1
    In the book "the Data Vault Guru" there is a schematic approach called "Schema Vault" -- an information schema to support a data vault; would that be what you are interested in? – patrick_at_snowflake Nov 14 '21 at 22:54
  • @MLeblanc i thought, it is not a best practice to create a link for a satellite – korbee82 Nov 19 '21 at 12:34

0 Answers0