Business keys in Link tables

[universe-designer]

As far as I know business keys in links aren't "illegal". They don't change, they reduce joins and thus increase query performance. What do you think about enabling them for links just like in hubs?

example orders_customers_l.sql

{{ config(materialized='incremental') }}

{%- set yaml_metadata -%}
link_hashkey: 'hk_l_orders_customers'
foreign_hashkeys: 
    - 'hk_h_orders'
    - 'hk_h_customers'
business_keys: 
    - o_orderkey
    - customer_name**    
source_models: stg_order
{%- endset -%}    

{%- set metadata_dict = fromyaml(yaml_metadata) -%}

{%- set link_hashkey = metadata_dict['link_hashkey'] -%}
{%- set foreign_hashkeys = metadata_dict['foreign_hashkeys'] -%}
{%- set source_models = metadata_dict['source_models'] -%}


{{ datavault4dbt.link(link_hashkey=link_hashkey,
        foreign_hashkeys=foreign_hashkeys,
        source_models=source_models) }}

[molschimke]

Hi,

business keys in links are sometimes asked for. What we recommend is to either: switch the whole DV model from hash keys to business keys if the platform supports it (e.g. on many MPP databases) and if the whole solution is built on this platform (not a distributed solution across multiple environments with mixing tool stack).

Tests have shown that on PB-scale those business keys in links present problems, especially on traditional database servers.

With these arguments, we would not implement both in links in datavault4dbt as a standard solution. You can however, implement this in your forked version, just keep potential drawbacks in mind.

[universe-designer]

Thank you Michael! Just to be sure "PB-scale" means on peta byte scale (tables much larger than 1000 tera bytes).

[tkirschke]

Hi @universe-designer , yes correct! PB-scale means tables on a peta byte scale.