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 appearances 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 losing intermediate changes. Therefore 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.