Compared to a regular version 1 Satellite, a version 1 reference Satellite includes the parent reference keys, instead of a parents Hashkey. Besides that, it shares the same features as a standard v1 Satellite, which are:

Calculating a virtualized load-end-date on top of a version 0 satellite
Enforces insert-only approach without loosing time ranges for business vault entities

Required Parameters

Parameters	Data Type	Required	Default Value	Explanation
ref_sat_v0	string	mandatory	–	Name of the underlying version 0 refrence_satellite.
ref_keys	string/list	mandatory	–	Name of the references key(s) inside the parent reference Hub. Should be the same as defined in the underlying version 0 reference Satellite.
hashdiff	string	mandatory	–	Name of the hashdiff column inside the underlying version 0 reference satellite. Needs to be similar to the ‘src_hashdiff’ pararmeter inside the sat_v0 model.

Optional Parameters

Parameters	Data Type	Required	Default Value	Explanation
src_ldts	string	optional	datavault4dbt. ldts_alias	Name of the ldts column inside the source models. 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 models. Needs to use the same column name as defined as alias inside the staging model.
ledts_alias	string	optional	datavault4dbt. ledts_alias	Desired alias for the load end date column.
add_is_current_flag	boolean	optional	False	Optional parameter to add a new column to the v1 sat based on the load end date timestamp (ledts). If set to true it will add this is_current flag to the v1 sat. For each record this column will be set to true if the load end date time stamp is equal to the variable end of all times. If its not, then the record is not current therefore it will be set to false.

Example 1

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

{%- set yaml_metadata -%}
ref_sat_v0: nation_0_rs
ref_keys: N_NATIONKEY
hashdiff: hd_nation_rs
add_is_current_flag: true
{%- endset -%}      

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ datavault4dbt.ref_sat_v1(ref_sat_v0=metadata_dict.get('ref_sat_v0'),
                    ref_keys=metadata_dict.get('ref_keys'),
                    hashdiff=metadata_dict.get('hashdiff'),
                    add_is_current_flag=mmetadata_dict.get('add_is_current_flag')) }}

Description

With this example, a reference Satellite in version 1 is created. Due to the fact that a version 1 Satellite always calculates the LoadEndDate, the two parameters ledts_alias and add_is_current_flag are set in in this macro. For detailed information on the attributes, look at the table above.

ref_sat_v0:
- nation_0_rs: This satellite would be the version 1 reference satellite of the underlying version 0 reference satellite for nation.
ref_keys:
- N_NATIONKEY: The satellite would be attached to the reference hub ‘nation_rh’, which has the column ‘N_NATIONKEY’ as a reference key column.
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.
ledts_alias:
- loadenddate: The ‘ledts’ column will be called ‘loadenddate’
add_is_current_flag:
- true: This will add a new column to the v1 sat based on the load end date timestamp (ledts). For each record this column will be set to true if the load end date time stamp is equal to the variable end of all times

Compiled SQL

Click Me!

WITH


end_dated_source AS (

    SELECT
        
        N_NATIONKEY,
        
        hd_nation_rs,
        rsrc,
        ldts,
        COALESCE(LEAD(ldts - INTERVAL '1 MICROSECOND') OVER (PARTITION BY N_NATIONKEY ORDER BY ldts),TO_TIMESTAMP('8888-12-31T23:59:59', 'YYYY-MM-DDTHH24:MI:SS')) as ledts,
        
        N_COMMENT,
        N_NAME,
        N_REGIONKEY
    FROM datavault4dbt_demo.core_Core.nation_rs

)

SELECT
    
    N_NATIONKEY,
    
    hd_nation_rs,
    rsrc,
    ldts,
    ledts,
        CASE WHEN ledts = TO_TIMESTAMP('8888-12-31T23:59:59', 'YYYY-MM-DDTHH24:MI:SS')
        THEN TRUE
        ELSE FALSE
        END AS IS_CURRENT,
    
        N_COMMENT,
        N_NAME,
        N_REGIONKEY
FROM end_dated_source