Same hub different context

[universe-designer]

I have a source table with two columns referencing different accounts:
table(account_A, account_B, attribute_1, attribute_2)

account_A and account_B are both account numbers, thus business keys for the same hub.

I would like to model my data vault model like this:

Hub_account(hashkey_account, business_key_account_nr, source, load_date)
NHLink_transaction(Link_hashkey, hashkey_account_A, hashkey_account_B, attribute_1, attribute_2, source, load_date)

What is the best way to achieve this?

I tried to model this with turbovault4dbt but the result is not sufficient.

The hub is fed with a nullif( account_A, account_B) meaning that account_B is loaded only if account_A is missing. I want all keys to be inserted into the hub. This way some account_B numbers will be missing.

I created a synonym on database level to trick dbt but I receive this error:

"dbt found two schema.yml entries for the same resource[...]. Resources and their associated columns may only be described a single time. To fix this, remove the resource entry [...] in one of these files:

• models\sources\my_table.yml
  models\sources\my_table_synonym.yml

There must be a standard solution to this, as a hub may be used in different contexts.

[obause]

Hi @universe-designer,
there are multiple ways to tackle your problem.

Firstly, you always have the option of adding another (possibly virtualized) stage before the datavault4dbt stage model in which more customized hard rules (such as merging two columns with the same business keys for loading the hub) can be performed.

In your case, however, you don't necessarily need this. You could just add the same source twice as two sources in the hub model. Once for one business key/hash key column and once for the other business key/hash key column. Both then end up together in the hub in one column.

You can have a look into the multi-source hub example in our wiki here: https://github.com/ScalefreeCOM/datavault4dbt/wiki/Standard-Hub

Let me know if that helps :)

Best regards,
Ole

[universe-designer]

I tried the multi source option with turbovault4dbt. In another example it works, but in my scenario the source.yml file is generated empty:

sources\source_synonym.yml

version: 2
models:

12:26:32  Encountered an error:
Parsing Error
  The schema file at models\sources\source_synonym.yml is invalid because the value of 'models' is not a list

It is empty because in turbovault4dbt I set Is_Primary_Source = 0. However when I set Is_Primary_Source = 1 it result

12:38:12  Encountered an error:
Compilation Error
  dbt found two schema.yml entries for the same resource. Resources and their associated columns may only be described a single time. To fix this, remove the resource entry in one of these files:
   - models\sources\source.yml
  models\sources\source_synonym.yml

We are introducing datavault4dbt / turbovault4dbt and our strategy is to modell in turbovault4dbt only. We have a bright skillset in our team and want to avoid enlarge it with dbt and yml skills.