Standard Satellite v0

This node creates the standard satellite version 0. It contains the descriptive data, splitted by privacy, security and rate of change or the in your company defined rules.

---

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	columnDropdownSelector	Select the parent hashkey of the satellite from the drop-down menu.
Hashdiff Column	columnSelector	Select the hashdiff of this satellite and bring it to the right side.

---

System Columns

No Sys Columns are added inside a Satellite v0 Node.

---

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 'STG_CUSTOMER_WIKI' and create the node 'SAT_V0_STG_CUSTOMER_WIKI' with the node type 'Datavault by Scalefree: Satellite v0'
• Rename the satellite using your naming conventions. In this case rename it to 'CUSTOMER_WIKI_N0_S'
  
  

• Open the node and delete all unnecessary columns, so that you only keep the Hashkey, Hashdiff, LoadDate Timestamp and Record Source aswell as the right descriptive data for that satellite
  
  

• 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' and 'Hashdiff Column'
  • Select the hashkey of this satellite and bring it to the right side in the 'Hashkey Column' section
  • Select the hashdiff of this satellite and bring it to the right side in the 'Hashdiff Column' section
    
    

• Create and run the node
  
  

---

Generated SQL Code of Example

      CREATE OR REPLACE TABLE
        "COALESCE_WORKSHOP"."CORE"."CUSTOMER_WIKI_N0_S" (
          "HK_CUSTOMER_H" STRING,
          "HD_CUSTOMER_N_S" STRING,
          "LDTS" TIMESTAMP
          COMMENT 'The Load Date Timestamp (LDTS) describes when this data first arrived in the Data Warehouse.',
          "RSRC" STRING
          COMMENT 'The Record Source (RSRC) describes the source of this data.',
          "C_ACCTBAL" NUMBER (12, 2) NOT NULL,
          "C_MKTSEGMENT" VARCHAR (10),
          "C_COMMENT" VARCHAR (117)
        )
      COMMENT = 'Customer data as defined by TPC-H'

---

      INSERT INTO
        "COALESCE_WORKSHOP"."CORE"."CUSTOMER_WIKI_N0_S"
      WITH
        latest_entries_in_sat AS (
          /* get current rows from satellite */
          SELECT
            "HK_CUSTOMER_H",
            "HD_CUSTOMER_N_S"
          FROM
            "COALESCE_WORKSHOP"."CORE"."CUSTOMER_WIKI_N0_S" QUALIFY ROW_NUMBER() OVER (
              PARTITION BY
                "HK_CUSTOMER_H"
              ORDER BY
                "LDTS" DESC
            ) = 1
        ),
        deduplicated_numbered_source AS (
          SELECT
            "STG_CUSTOMER_WIKI"."HK_CUSTOMER_H" AS HK_CUSTOMER_H,
            "STG_CUSTOMER_WIKI"."HD_CUSTOMER_N_S" AS HD_CUSTOMER_N_S,
            "STG_CUSTOMER_WIKI"."LDTS" AS LDTS,
            "STG_CUSTOMER_WIKI"."RSRC" AS RSRC,
            "STG_CUSTOMER_WIKI"."C_ACCTBAL" AS C_ACCTBAL,
            "STG_CUSTOMER_WIKI"."C_MKTSEGMENT" AS C_MKTSEGMENT,
            "STG_CUSTOMER_WIKI"."C_COMMENT" AS C_COMMENT,
            ROW_NUMBER() OVER (
              PARTITION BY
                "HK_CUSTOMER_H"
              ORDER BY
                "LDTS"
            ) as rn
          FROM
            "COALESCE_WORKSHOP"."CORE"."STG_CUSTOMER_WIKI" "STG_CUSTOMER_WIKI" QUALIFY CASE
              WHEN "HD_CUSTOMER_N_S" = LAG("HD_CUSTOMER_N_S") OVER (
                PARTITION BY
                  "HK_CUSTOMER_H"
                ORDER BY
                  "LDTS"
              ) THEN FALSE
              ELSE TRUE
            END
        )
      SELECT DISTINCT
        HK_CUSTOMER_H,
        HD_CUSTOMER_N_S,
        LDTS,
        RSRC,
        C_ACCTBAL,
        C_MKTSEGMENT,
        C_COMMENT
      FROM
        deduplicated_numbered_source
      WHERE
        NOT EXISTS (
          SELECT
            1
          FROM
            latest_entries_in_sat
          WHERE
            deduplicated_numbered_source.HK_CUSTOMER_H = latest_entries_in_sat."HK_CUSTOMER_H"
            AND deduplicated_numbered_source.HD_CUSTOMER_N_S = latest_entries_in_sat."HD_CUSTOMER_N_S"
            AND deduplicated_numbered_source.rn = 1
        )