talk to an expert


Effectivity Satellite

This macro creates an Effectivity Satellite version 0. It should be materialized as an incremental table. It should be applied ‘on top’ of the staging layer, and is either connected to a Hub or a Link.

The purpose of an effectivity satellite is to capture whether an object or relationship disappears in the source system. Technically, one effectivity satellite always tracks the appereances of one hashkey, either a hub hashkey for business objects, or a link hashkey for relationships. If the hub or link is loaded by multiple sources, create one effectivity satellite for each source.

Disclaimer

This effectivity satellite is designed to identify and track deletions and appearances of hashkeys inside source objects. It only works if the source data delivery always includes a full load of the data. It does not work, if the data delivery only includes deltas.

Features:

  • Can handle multiple updates per batch, without loosing intermediate changes. Therefor initial loading is supported. Effectivity is properly calculated for each batch.
  • Using a dynamic high-water-mark to optimize loading performance of multiple loads.

Version 1 Satellite

There is no specific Effectivity Satellite v1 macro. To calculate load end dates of effectivity data, the Standard Satellite v1 should be used. To make it properly work, set the parameter hashdiff  to your is_active_alias, as defined in the global parameter datavault4dbt.is_active_alias. 

Required Parameters

Parameters Data Type Required Default Value Explanation
source_model string mandatory – Name of the underlying staging model, must be available inside dbt as a model.
tracked_hashkey string mandatory – Name of the hashkey column inside the stage that should be tracked for deletes.

Optional Parameters

Parameters Data Type Required Default Value Explanation
src_ldts string optional datavault4dbt.ldts_alias Name of the ldts column inside the source model. Is optional, will use the global variable ‘datavault4dbt.ldts_alias’. Needs to use the same column name as defined as alias inside the staging model.
src_rsrc string optional datavault4dbt.rsrc_alias Name of the rsrc column inside the source model. Is optional, will use the global variable ‘datavault4dbt.rsrc_alias’. Needs to use the same column name as defined as alias inside the staging model.
disable_hwm boolean optional False Whether the automatic application of a High-Water Mark (HWM) should be disabled or not.
source_is_single_batch boolean optional True Performance boost for single source loads. Set this to FALSE only if source contains multiple batches. Model will get slower if set to FALSE, even if source only holds one batch.
is_active_alias string optional datavault4dbt.is_active_alias Define how the effectivity column should be called. Optional, will use the global variable ‘datavault4dbt.is_active_alias’ if not set.

Example 1


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

{%- set yaml_metadata -%}
tracked_hashkey: hk_account_h
is_active_alias: 'active'
source_model: stage_account
{%- endset -%}

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

{{ datavault4dbt.eff_sat_v0(tracked_hashkey=metadata_dict.get('tracked_hashkey'),
                            source_model=metadata_dict.get('source_model'),
                            is_active_alias=metadata_dict.get('is_active_alias'),
                            src_ldts=metadata_dict.get('src_ldts'),
                            src_rsrc=metadata_dict.get('src_rsrc'),
                            source_is_single_batch=metadata_dict.get('source_is_single_batch'),
                            disable_hwm=metadata_dict.get('disable_hwm')) }}

Description

With this example, an effectivity satellite v0 for stage_account is created. It tracks the appearances of the Hub Hashkey hk_account_h.

  • tracked_hashkey:
    • hk_account_h: The satellite tracks the appearances of the hub hashkey for the Account Hub.
  • is_active_alias:
    • active The new column generated by the effectivity satellite v0 is now called ‘active’, instead of the default value defined in the global variable ‘datavault4dbt.is_active_alias’.
  • source_model:
    • stage_account: This satellite is loaded out of the stage for account.