Version: v1.18

REFERENCE SATELLITE V0

This macro creates a reference satellite version 0, meaning that it should be materialized as an incremental table. It should be applied on top of the staging layer, and is connected to a reference Hub. On top of each version 0 reference satellite, a version 1 reference satellite should be created, using the ref_sat_v1 macro. This extends the v0 reference satellite by a virtually calculated load end date. Each reference satellite can only be loaded by one source model, since we typically recommend a satellite split by source system.

Compared to a regular satellite, a reference Satellite does not hold a Hashkey of the parent Hub, since the parent reference Hub does not have a Hub Hashkey. Instead, all reference keys of the parent reference Hub are loaded in the reference Satellite to allow unique connections between them.

Besides that, a reference Satellite v0 shares the same features as a regular Satellite v0, which are:

Can handle multiple updates per batch, without losing intermediate changes. Therefore initial loading is supported.
Using a dynamic high-water-mark to optimize loading performance of multiple loads

REQUIRED PARAMETERS

Parameters	Data Type	Required	Default Value	Explanation
parent_ref_keys	string \| list	mandatory	–	Name of the reference key(s) inside the parent reference Hub.
source_model	string	mandatory	–	Name of the underlying staging model, must be available inside dbt as a model.

OPTIONAL PARAMETERS

Parameters	Data Type	Required	Default Value	Explanation
src_payload	list of strings	optional	none	The descriptive attributes that should be included in this reference satellite. With a single column, `src_hashdiff` may be omitted and change detection runs directly on that column. With two or more columns, `src_hashdiff` is required. Omit entirely for a reference satellite without payload. See Payload and hashdiff options.
src_hashdiff	string	optional	none	Name of the hashdiff column of this satellite, that was created inside the staging area and is calculated out of the entire payload of this satellite. The stage must hold one hashdiff per satellite entity. Required when `src_payload` has two or more columns; omit it for a single-attribute or no-payload reference satellite.
src_ldts	string	optional	datavault4dbt.ldts_alias	Name of the ldts column inside the source model. 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.
additional_columns	string \| list of strings	optional	none	Column or list of columns that will additionally be added to the reference satellite.

EXAMPLE 1

{{ config(materialized='incremental',
        schema='Core') }}

{%- set yaml_metadata -%}
source_model: stg_nation
parent_ref_keys: N_NATIONKEY
src_hashdiff: hd_nation_rs
src_payload:
    - N_COMMENT
    - N_NAME
    - N_REGIONKEY
{%- endset -%}      

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

DESCRIPTION

parent_ref_keys:
- N_NATIONKEY: This reference Satellite would be attached to the reference Hub “Nation_rh” and therefore it must hold all the reference Keys of that Hub. In this case it would only be the column “N_NATIONKEY”
src_hashdiff:
- hd_nation_rs: Since we recommend naming the hashdiff column similar to the name of the satellite entity, just with a prefix, this would be the hashdiff column of the reference satellite for Nation.
src_payload: This satellite would hold the columns N_COMMENT, N_NAME and N_REGIONKEY, coming out of the underlying staging area. Must fit the input definition for the specified hashdiff column.
source_models:
- stg_nation: This satellite is loaded out of the stage for nation.

COMPILED SQL

Click me

WITH


source_data AS (

    SELECT
        
        N_NATIONKEY,
        
        hd_nation_rs as hd_nation_rs,
        
        rsrc,
        ldts,
        N_COMMENT,
        N_NAME,
        N_REGIONKEY
    FROM datavault4dbt_demo.core_Stages.stg_nation
),




deduplicated_numbered_source AS (

    SELECT
    
    N_NATIONKEY,
    
    hd_nation_rs,
    
        rsrc,
        ldts,
        N_COMMENT,
        N_NAME,
        N_REGIONKEY
    
    FROM source_data
    QUALIFY
        CASE
            WHEN hd_nation_rs = LAG(hd_nation_rs) OVER(PARTITION BY N_NATIONKEY ORDER BY ldts) THEN FALSE
            ELSE TRUE
        END
),


records_to_insert AS (

    SELECT
    
    N_NATIONKEY,
    
    hd_nation_rs,
    
        rsrc,
        ldts,
        N_COMMENT,
        N_NAME,
        N_REGIONKEY
    FROM deduplicated_numbered_source

    )

SELECT * FROM records_to_insert

PAYLOAD AND HASHDIFF OPTIONS

Both src_payload and src_hashdiff are optional. Depending on what you provide, a reference satellite supports three configurations:

Multiple attributes with a hashdiff — provide src_payload (one or more columns) together with src_hashdiff. Change detection runs on the hashdiff. Use this when the reference satellite carries several descriptive attributes — the classic case shown in Example 1 above.
A single attribute without a hashdiff — provide exactly one src_payload column and omit src_hashdiff. Change detection runs directly on that column. Use this when the reference satellite tracks exactly one descriptive attribute; it avoids computing and storing a hashdiff.
No payload — omit both src_payload and src_hashdiff. The reference satellite only records when a reference key appears, together with the load date, record source and any additional_columns.

If src_payload contains two or more columns but src_hashdiff is missing, compilation fails with a clear error, since a hashdiff is required to detect changes across multiple columns.

EXAMPLE 2 – single attribute without a hashdiff

{{ config(materialized='incremental',
        schema='Core') }}

{%- set yaml_metadata -%}
source_model: stg_nation
parent_ref_keys: N_NATIONKEY
src_payload:
    - N_NAME
{%- endset -%}      

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

EXAMPLE 3 – no payload

{{ config(materialized='incremental',
        schema='Core') }}

{%- set yaml_metadata -%}
source_model: stg_nation
parent_ref_keys: N_NATIONKEY
{%- endset -%}      

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

REQUIRED PARAMETERS​

OPTIONAL PARAMETERS​

EXAMPLE 1​

DESCRIPTION​

COMPILED SQL​

PAYLOAD AND HASHDIFF OPTIONS​

EXAMPLE 2 – single attribute without a hashdiff​

EXAMPLE 3 – no payload​