Standard Satellite v1

This node creates the standard satellite version 1. It contains the same data as the version 0 satellite, but adds the column 'LEDTS', which defines the end of the validation, as a new row is set to active. It also adds the 'is_active' column, which defines if a row describes the latest descriptive data. Although the columns 'LEDTS' and 'is_active' get updated, it is best practice, because the satellite v1 is a view and not materialized, following the insert only approach.

---

User Configuration

Config Name	Config Type	Explanation
Hashkey Column	columnSelector	In the Config section on the right there is a tab called 'Data Vault'. Expand this and you get a column selector list 'Hashkey Column'. Select the hashkey of this satellite and bring it to the right side.

---

System Columns

Sys Column Name	Default Value	Explanation
LEDTS	See Transformation below	The 'LEDTS' is the calculated load end date of a row. It is calculated when a new different row with is inserted, which contains different descriptive data to the same hashkey.
IS_CURRENT	See Transformation below	The 'IS_CURRENT' column indicates whether the current row is the latest one, containing the most actual data. It is calculated the same way as the 'LEDTS' column.

'LEDTS' Transformation expression:

COALESCE(
  LEAD("{{ datavault4coalesce.config.ldts_alias }}" - INTERVAL '1 MICROSECOND')
  OVER (
    PARTITION BY "{{ get_value_by_column_attribute("is_hk") }}" 
    ORDER BY 
    "{{ datavault4coalesce.config.ldts_alias }}"),
    {{ datavault4coalesce__string_to_timestamp(datavault4coalesce.config.timestamp_format, 
      datavault4coalesce.config.end_of_all_times) }}
  )

'IS_CURRENT' Transformation expression:

CASE 
  WHEN 
    LEAD("{{ datavault4coalesce.config.ldts_alias }}" - INTERVAL '1 MICROSECOND') 
    OVER (PARTITION BY "{{ get_value_by_column_attribute("is_hk") }}" 
    ORDER BY "{{ datavault4coalesce.config.ldts_alias }}") IS NULL 
  THEN TRUE 
  ELSE FALSE 
END

---

Example 1

Description

In this example we got a standard satellite v0 with non privacy data. The source table is the 'STG_CUSTOMER_WIKI'.

Create the node:

• Select the 'CUSTOMER_WIKI_N0_S' and create the node 'SAT_V1_CUSTOMER_WIKI_N0_S' with the node type 'Datavault by Scalefree: Satellite v1'
• Rename the satellite using your naming conventions. In this case rename it to 'CUSTOMER_WIKI_N1_S'
  
  

• In the Config section on the right there is a tab called 'Data Vault'
• Expand this and you get a column selector list 'Hashkey Column'
  • Select the hashkey of this satellite and bring it to the right side in the 'Hashkey Column' section
    
    

• Create the node