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

warning

Up until datavault4dbt v1.9.12 the datatype of the is_active-column was a numeric datatype. Starting with datavault4dbt v1.10.0 the default datatype has been changed to Boolean. For Fabric & Synapse the default datatype was changed to Bit, for Oracle the default datatype was kept at Number. Since v1.10.0 it is also possible to change the datatype by setting the variable datavault4dbt.is_active_datatype to the desired value. Internally the values 0 and 1 are used and then CAST() to the specified datatype.

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 -%}

{{ datavault4dbt.eff_sat_v0(yaml_metadata=yaml_metadata) }}

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.