Multi active Satellite v1

This node creates the multi-active satellite version 1. It contains the same data as the version 0 multi-active 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

In the 'Config' section on the right hand-side, expand section 'Data Vault' to modify the following configurations for the node:

Config Name	Config Type	Explanation
Hashkey Column	columnSelector	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. The LEDTS in a multi-active satellite version 1 is calculated for each the parent hash key and multi-active key combination.
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") }}", "{{ get_source_ma_keys(delimiter=',') }}" 
          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") }}", "{{ get_source_ma_keys(delimiter=',') }}"
        ORDER BY "{{ datavault4coalesce.config.ldts_alias }}") IS NULL 
      THEN TRUE 
      ELSE FALSE 
    END

---

Example 1

Description

In this example we got a multi-active satellite v0 SATMV0_CUSTOMER_ADDRESS. The source table is the 'STG_CUSTOMER_ADDRESS'.

Create the node:

• Select the 'SATMV0_CUSTOMER_ADDRESS' and create the node 'SATMV1_CUSTOMER_ADDRESS' with the node type 'Datavault by Scalefree: Multi-active Satellite v1'
• Rename the satellite using your naming conventions. In this case rename it to 'SATMV1_CUSTOMER_ADDRESS'
  
  

• In the 'Config' section on the right hand-side, expand section 'Data Vault'
  • In the 'Hashkey Column' section, select the hashkey of this satellite and bring it to the right side
    
    

• Create the node
  
  

---

Generated SQL Code of Example

CREATE OR REPLACE VIEW
  "COALESCE_WORKSHOP"."CORE"."SATMV1_CUSTOMER_ADDRESS" (
    "HK_CUSTOMER_H",
    "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.',
    "HD_CUSTOMER_ADDRESS_P_S",
    "CA_ADDRESS_ID",
    "CA_ADDRESS_STREET",
    "CA_ADDRESS_POSTAL_CODE",
    "LEDTS",
    "IS_CURRENT",
    "CA_COMMENT",
    "CA_MKTSEGMENT",
    "CA_NATIONKEY",
    "CA_NAME"
  ) AS
SELECT
  "SATMV0_CUSTOMER_ADDRESS"."HK_CUSTOMER_H" AS "HK_CUSTOMER_H",
  "SATMV0_CUSTOMER_ADDRESS"."LDTS" AS "LDTS",
  "SATMV0_CUSTOMER_ADDRESS"."RSRC" AS "RSRC",
  "SATMV0_CUSTOMER_ADDRESS"."HD_CUSTOMER_ADDRESS_P_S" AS "HD_CUSTOMER_ADDRESS_P_S",
  "SATMV0_CUSTOMER_ADDRESS"."CA_ADDRESS_ID" AS "CA_ADDRESS_ID",
  "SATMV0_CUSTOMER_ADDRESS"."CA_ADDRESS_STREET" AS "CA_ADDRESS_STREET",
  "SATMV0_CUSTOMER_ADDRESS"."CA_ADDRESS_POSTAL_CODE" AS "CA_ADDRESS_POSTAL_CODE",
  COALESCE(
    LEAD("LDTS" - INTERVAL '1 MICROSECOND') OVER (
      PARTITION BY
        "HK_CUSTOMER_H",
        "CA_ADDRESS_ID"
      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",
        "CA_ADDRESS_ID"
      ORDER BY
        "LDTS"
    ) IS NULL THEN TRUE
    ELSE FALSE
  END AS "IS_CURRENT",
  "SATMV0_CUSTOMER_ADDRESS"."CA_COMMENT" AS "CA_COMMENT",
  "SATMV0_CUSTOMER_ADDRESS"."CA_MKTSEGMENT" AS "CA_MKTSEGMENT",
  "SATMV0_CUSTOMER_ADDRESS"."CA_NATIONKEY" AS "CA_NATIONKEY",
  "SATMV0_CUSTOMER_ADDRESS"."CA_NAME" AS "CA_NAME"
FROM
  "COALESCE_WORKSHOP"."CORE"."SATMV0_CUSTOMER_ADDRESS" "SATMV0_CUSTOMER_ADDRESS"