How to build a Link/Sat with Dependent Child Key

[klaus1978]

Hello,
how to build a Link and a Satellite that has a Dependent Child Key?

Unfortunatelly I do not find a example for this use case.

Thanks in advance
Klaus

[tkirschke]

Hello, how to build a Link and a Satellite that has a Dependent Child Key?

Unfortunatelly I do not find a example for this use case.

Thanks in advance Klaus

Hi @klaus1978 and thanks for reaching out to us!

When you have a Dependent Child Key, you would just add the corresponding column(s) to the link hashkey calculation in the underlying staging model. Then you would just refer to this link hashkey column downstream in the link model and the attached satellite model(s).

Kind regards,
Tim

[klaus1978]

So if I take your example for a staging file I would add the field MY_DEP_CHILD to the hashed columns:

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

{%- set yaml_metadata -%}
source_model: 'source_account'
ldts: 'edwLoadDate'
rsrc: 'edwRecordSource'
hashed_columns: 
    hk_account_h:
        - account_number
        - account_key
    MY_DEP_CHILD:
        - MY_DEP_CHILD
    hd_account_s:
        is_hashdiff: true
        columns:
            - name
            - address
            - phone
            - email
derived_columns:
    conversion_duration:
        value: 'TIMESTAMP_DIFF(conversion_date, created_date, DAY)'
        datatype: 'INT64'
    country_isocode:
        value: '!GER'
        datatype: 'STRING'
    account_name:
        value: 'name'
        datatype: 'String'
prejoined_columns:
    contractnumber:
        src_name: 'source_data'
        src_table: 'contract'
        bk: 'contractnumber'
        this_column_name: 'ContractId'
        ref_column_name: 'Id'
    master_account_key:
        src_name: 'source_data'
        src_table: 'account'
        bk: 'account_key'
        this_column_name: 'master_account_id'
        ref_column_name: 'Id'
missing_columns:
    legacy_account_uuid: 'INT64'
    shipping_address: 'STRING'
{%- endset -%}

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

{%- set source_model = metadata_dict['source_model'] -%}
{%- set ldts = metadata_dict['ldts'] -%}
{%- set rsrc = metadata_dict['rsrc'] -%}
{%- set hashed_columns = metadata_dict['hashed_columns'] -%}
{%- set derived_columns = metadata_dict['derived_columns'] -%}
{%- set prejoined_columns = metadata_dict['prejoined_columns'] -%}
{%- set missing_columns = metadata_dict['missing_columns'] -%}

{{ datavault4dbt.stage(source_model=source_model,
                    ldts=ldts,
                    rsrc=rsrc,
                    hashed_columns=hashed_columns,
                    derived_columns=derived_columns,
                    prejoined_columns=prejoined_columns,
                    missing_columns=missing_columns) }}

But then the dependent child key is hashed? I thought it is prefered to use these keys directly without hashing them?

KR Klaus

[tkirschke]

Hi @klaus1978 ,

I have now created a new Wiki page for handling dependent child keys with datavault4dbt. Check it our here!

I have included example models for clarification. Let me know if there is anything left uncovered by this wiki page!

Kind Regards,
Tim

[klaus1978]

Great,
now I got it!

thanks Klaus