Issue with MA satellite

[MKloseINF]

MA satellite only uses hash key and hash diff to identify latest entries in sat. Multi active attribute(s) are ignored in partition clause. Using row number to qualify results in exactly one hash + hash diff key. But if you've multi active for same ldts in your satellite only one is identified and a similar row is loaded.

Example: Line is MA attribute

source data first load: HK |HD |Line | Quantity | LDTS ABCD | 5678 | 1 | 5 | 14.8.2024 ABCD | 3456 | 2 | -10 | 14.8.2024

MA sat after first load: HK |HD |Line | Quantity | LDTS ABCD | 5678 | 1 | 5 | 14.8.2024 ABCD | 3456 | 2 | -10 | 14.8.2024

source data second load (maybe same data because of no increment from source): HK |HD |Line | Quantity | LDTS ABCD | 5678 | 1 | 5 | 15.8.2024 ABCD | 3456 | 2 | -10 | 15.8.2024

MA sat after second load: HK |HD |Line | Quantity | LDTS ABCD | 5678 | 1 | 5 | 14.8.2024 ABCD | 3456 | 2 | -10 | 14.8.2024 ABCD | 5678 | 1 | 5 | 15.8.2024

Which line for second load will be identified as diff is random because of ROW function.

[tkirschke]

Hi @MKloseINF and thanks for reaching out!

We use the Block Hashdiff for Multiactivity, meaning in your example, all participants of one multi-active group have the same hashdiff:

source data first load: HK |HD |Line | Quantity | LDTS ABCD | 5678 | 1 | 5 | 14.8.2024 ABCD | 5678 | 2 | -10 | 14.8.2024

MA sat after first load: HK |HD |Line | Quantity | LDTS ABCD | 5678 | 1 | 5 | 14.8.2024 ABCD | 5678 | 2 | -10 | 14.8.2024

Then the second load would not be seen as a delta, and no insert happens. For more information about the block-hashdiff, generated by LISTAGG(), see our blog article here: https://www.scalefree.com/blog/data-vault/using-multi-active-satellites-the-correct-way-2-2/

Let me know if this helped!

[MKloseINF]

Hi @tkirschke,

unfortunately that won't help and macros/tables/snowflake/ma_sat_v0.sql didn't use LISTAGG function.

When looking at the Jinja macro for incremental the row number = 1 throws you only back one row (random) but ignores in above exampel LINE 1 and 2 (MA key). Records to insert part of sql statement identifies then 1 column as new.

{# Get the latest record for each parent hashkey in existing sat, if incremental. #} {%- if is_incremental() %} latest_entries_in_sat AS (

SELECT
    {{ parent_hashkey }},
    {{ ns.hdiff_alias }}
FROM 
    {{ this }}
QUALIFY ROW_NUMBER() OVER(PARTITION BY {{ parent_hashkey|lower }} ORDER BY {{ src_ldts }} DESC) = 1  

), {%- endif %}

[tkirschke]

Hi @MKloseINF ,

LISTAGG() or STRINGAGG() is used in the Staging model, as soon as multi-activity is configured. Can you confirm, that your stage model has the multi active config properly defined, as seen in Example 3 in the wiki: https://github.com/ScalefreeCOM/datavault4dbt/wiki/Staging#example-3

Please send the stage model and Ma sat v0 model, so we can help you better.

[MKloseINF]

yes I checked and everythings looks good from my point of view.

Stage: `{{ config(materialized="table") }}

{%- set yaml_metadata -%} source_model: 'DS_AFDPNLT0011' ldts: 'CURRENT_TIMESTAMP' rsrc: '!DS_AFDPNLT0011' hashed_columns: hk_faabstnk_h:

• FAABSTNK hk_faaposex_h:
• FAAPOSEX hk_faalifnr_h:
• FAALIFNR hk_faaacblnr_h:
• FAAACBLNR hk_faaacldoc_h:
• FAAACLDOC hk_faakstar_h:
• FAAKSTAR hk_gm1pck_h:
• GM1PCK hk_faakostl_h:
• FAAKOSTL hk_faaciresp_h:
• FAACIRESP hk_faacaufn_h:
• FAACAUFN hk_faaserv_h:
• FAASERV hk_faaposid_h:
• FAAPOSID hk_faaprespc_h:
• FAAPRESPC hk_faaktopl_h:
• FAAKTOPL hk_faasaknr_h:
• FAASAKNR hk_faafkber_h:
• FAAFKBER hk_faawrttp_h:
• FAAWRTTP hk_gm1uphi_p_h:
• GM1UPHI_P hk_faacobval_h: # MANUAL CODE
• FAACOBVAL hk_faagsber_h:
• FAAGSBER hk_faacustka_h:
• FAACUSTKA hk_faadctyp_h:
• FAADCTYP hk_faacobtyp_h: # MANUAL CODE
• FAACOBTYP hk_faakokrs_h:
• FAAKOKRS hk_faabelnr_h:
• FAABELNR hk_soursystem_h:
• SOURSYSTEM hk_gaarevrc_h:
• GAAREVRC hk_faavers_h:
• FAAVERS hk_faabukrs_h:
• FAABUKRS hk_gm1uphier_h:
• GM1UPHIER hk_gm1fctcty_h:
• GM1FCTCTY hk_gm1fctfct_h:
• GM1FCTFCT hk_gm1fc_h:
• GM1FC hk_faavale_h:
• FAAVALE hk_gaakeytyp_h:
• GAAKEYTYP hk_gm1dcupa_h:
• GM1DCUPA hk_gm1puph_h:
• GM1PUPH hk_gaacurcl_h:
• GAACURCL hk_gaacurct_h:
• GAACURCT hk_faakunwe_h:
• FAAKUNWE hk_faakunrg_h:
• FAAKUNRG hk_faakunre_h:
• FAAKUNRE hk_faakunag_h:
• FAAKUNAG hk_faarespc_h:
• FAARESPC hk_faavtweg_h:
• FAAVTWEG hk_faabsark_h:
• FAABSARK hk_faarebzg_h:
• FAAREBZG hk_faavbeln_h:
• FAAVBELN hk_faabwtar_h:
• FAABWTAR hk_gm1restk_h:
• GM1RESTK hk_gm1lffrp_h:
• GM1LFFRP hk_gm1dcuru_h:
• GM1DCURU hk_gm1rppga_h:
• GM1RPPGA hk_faalocmat_h:
• FAALOCMAT hk_faabklas_h:
• FAABKLAS hk_gm1audid_h:
• GM1AUDID hk_gbfvarnt_h:
• GBFVARNT hk_gbfscope_h:
• GBFSCOPE hk_gm2uphag_h:
• GM2UPHAG hk_gm2matnr_h:
• GM2MATNR hk_gm1uphb_h:
• GM1UPHB hk_gm1uphbf_h:
• GM1UPHBF hk_gm1uphbl_h:
• GM1UPHBL hk_gm1uphbs_h:
• GM1UPHBS hk_gm1uphbu_h:
• GM1UPHBU hk_gm1uphpg_h:
• GM1UPHPG hk_gm2uphia_h:
• GM2UPHIA hk_gm2uphmg_h:
• GM2UPHMG hk_gm2uphug_h:
• GM2UPHUG hk_faawert_h:
• FAAWERT hk_faaaugru_h:
• FAAAUGRU hk_faawerks_h:
• FAAWERKS hk_faaitemc_h:
• FAAITEMC hk_faaposnr_h:
• FAAPOSNR hk_faavkorg_h:
• FAAVKORG hk_faaauart_h:
• FAAAUART hk_faabzirk_h:
• FAABZIRK hk_he2segmt_h:
• HE2SEGMT hk_gaainvdt_h:
• GAAINVDT hk_gaaposdt_h:
• GAAPOSDT hk_gaasydat_h:
• GAASYDAT hk_gaasystim_h:
• GAASYSTIM hk_fiscper_h:
• FISCPER hk_calday_h:
• CALDAY hk_faakunrgs_h:
• FAAKUNRGS hk_faakunwes_h:
• FAAKUNWES hk_faakunags_h:
• FAAKUNAGS hk_faanotafi_h:
• FAANOTAFI hk_faavkbur_h:
• FAAVKBUR hk_faasalsrp_h:
• FAASALSRP hk_faakunres_h:
• FAAKUNRES hd_AFDPNLT0011_s: is_hashdiff: true columns:
  • gm1qntty
  • gm1amount
  • unit
  • currency
  • hvr_is_deleted hk_AFDPNLT001_l:
• FAABSTNK
• FAAPOSEX
• FAALIFNR
• FAAACBLNR
• FAAACLDOC
• FAAKSTAR
• GM1PCK
• FAAKOSTL
• FAACIRESP
• FAACAUFN
• FAASERV
• FAAPOSID
• FAAPRESPC
• FAAKTOPL
• FAASAKNR
• FAAFKBER
• FAAWRTTP
• GM1UPHI_P
• FAACOBVAL
• FAAGSBER
• FAACUSTKA
• FAADCTYP
• FAACOBTYP
• FAAKOKRS
• FAABELNR
• SOURSYSTEM
• GAAREVRC
• FAAVERS
• FAABUKRS
• GM1UPHIER
• GM1FCTCTY
• GM1FCTFCT
• GM1FC
• FAAVALE
• GAAKEYTYP
• GM1DCUPA
• GM1PUPH
• GAACURCL
• GAACURCT
• FAAKUNWE
• FAAKUNRG
• FAAKUNRE
• FAAKUNAG
• FAARESPC
• FAAVTWEG
• FAABSARK
• FAAREBZG
• FAAVBELN
• FAABWTAR
• GM1RESTK
• GM1LFFRP
• GM1DCURU
• GM1RPPGA
• FAALOCMAT
• FAABKLAS
• GM1AUDID
• GBFVARNT
• GBFSCOPE
• GM2UPHAG
• GM2MATNR
• GM1UPHB
• GM1UPHBF
• GM1UPHBL
• GM1UPHBS
• GM1UPHBU
• GM1UPHPG
• GM2UPHIA
• GM2UPHMG
• GM2UPHUG
• FAAWERT
• FAAAUGRU
• FAAWERKS
• FAAITEMC
• FAAPOSNR
• FAAVKORG
• FAAAUART
• FAABZIRK
• HE2SEGMT
• GAAINVDT
• GAAPOSDT
• GAASYDAT
• GAASYSTIM
• FISCPER
• CALDAY
• FAAKUNRGS
• FAAKUNWES
• FAAKUNAGS
• FAANOTAFI
• FAAVKBUR
• FAASALSRP
• FAAKUNRES multi_active_config: multi_active_key: ["REQTSN", "DATAPAKID", "RECORD"] main_hashkey_column: 'hk_AFDPNLT001_l' {%- 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, ) }}`

MA Sat: `{{ config(materialized="incremental") }}

{%- set yaml_metadata -%} source_model: 'DS_STG_AFDPNLT0011' parent_hashkey: 'hk_AFDPNLT001_l' src_hashdiff: 'hd_AFDPNLT0011_s' src_ma_key:

• REQTSN
• DATAPAKID
• RECORD src_payload:
• gm1qntty
• gm1amount
• unit
• currency
• hvr_is_deleted src_rsrc: '!AFDPNLT0011' {%- endset -%}

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

{{ datavault4dbt.ma_sat_v0( source_model=metadata_dict["source_model"], parent_hashkey=metadata_dict["parent_hashkey"], src_hashdiff=metadata_dict["src_hashdiff"], src_ma_key=metadata_dict["src_ma_key"], src_payload=metadata_dict["src_payload"], ) }} `

[tkirschke]

Hi @MKloseINF

The Multi Active Config seems fine, but it's not passed into the stage macro:

{%- 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"] -%}
{%- set multi_active_config = metadata_dict["multi_active_config"] -%}

{{
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,
multi_active_config=multi_active_config
)
}}`

Let me know if this solves the issue.

[MKloseINF]

indeed you're right. we took the "wrong" example code which is missing multi active config.

I added missing lines but now generated code throws errors, see attached file console_output.txt

does main_hashkey_column work with link hash key? Looking at example 3 there is hk_test used which is not specified in whole definition code

[tkiehn]

Hi @MKloseINF,

the error in your compiled code is caused by an additional comma, this was fixed here. Which version of datavault4dbt are you using?

Kind regards Theo

[MKloseINF]

Hi @tkiehn

we were on version 1.3.1....so I did the upgrade to 1.3.3. Additional comma error resolved, but now new error invalid identifier 'HK_AFDPNLT001_L' occurs. I can point it out where it comes from. We are using a MA satellite for link. The link hash key is calculated after the hash key, but we need the link hash key (HK_AFDPNLT001_L) in hash diff calculation which fails due to later calculation.

But it works changing model spec order...first link hash key, then hashdiff key

Thanks a lot for your support

[MKloseINF]

Thanks a lot