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
  
  

---

Generated SQL Code of Example

  CREATE OR REPLACE VIEW
    "COALESCE_WORKSHOP"."CORE"."CUSTOMER_WIKI_N1_S" (
      "HK_CUSTOMER_H",
      "HD_CUSTOMER_N_S",
      "LDTS"
      COMMENT 'The Load Date Timestamp (LDTS) describes when this data first arrived in the Data Warehouse.',
      "RSRC"
      COMMENT 'The Record Source (RSRC) describes the source of this data.',
      "C_ACCTBAL",
      "C_MKTSEGMENT",
      "C_COMMENT",
      "LEDTS",
      "IS_CURRENT"
    )
  COMMENT = 'Customer data as defined by TPC-H' AS
  SELECT
    "CUSTOMER_WIKI_N0_S"."HK_CUSTOMER_H" AS "HK_CUSTOMER_H",
    "CUSTOMER_WIKI_N0_S"."HD_CUSTOMER_N_S" AS "HD_CUSTOMER_N_S",
    "CUSTOMER_WIKI_N0_S"."LDTS" AS "LDTS",
    "CUSTOMER_WIKI_N0_S"."RSRC" AS "RSRC",
    "CUSTOMER_WIKI_N0_S"."C_ACCTBAL" AS "C_ACCTBAL",
    "CUSTOMER_WIKI_N0_S"."C_MKTSEGMENT" AS "C_MKTSEGMENT",
    "CUSTOMER_WIKI_N0_S"."C_COMMENT" AS "C_COMMENT",
    COALESCE(
      LEAD("LDTS" - INTERVAL '1 MICROSECOND') OVER (
        PARTITION BY
          "HK_CUSTOMER_H"
        ORDER BY
          "LDTS"
      ),
      TO_TIMESTAMP('8888-12-31T23:59:59', 'YYYY-MM-DDTHH24:MI:SS')
    ) AS "LEDTS",
    CASE
      WHEN LEAD("LDTS" - INTERVAL '1 MICROSECOND') OVER (
        PARTITION BY
          "HK_CUSTOMER_H"
        ORDER BY
          "LDTS"
      ) IS NULL THEN TRUE
      ELSE FALSE
    END AS "IS_CURRENT"
  FROM
    "COALESCE_WORKSHOP"."CORE"."CUSTOMER_WIKI_N0_S" "CUSTOMER_WIKI_N0_S"