Record Tracking Satellite

The "Datavault4Coalesce: Record Tracking Satellite" node creates a record tracking satellite entity, tracking the appearences of a Hub or Link hashkey.

User Configuration

Config Name	Config Type	Explanation
Hashkey Column	string	In the Config section on the right there is a tab called 'Data Vault'. Expand this and you get a column selector 'Hashkey Column'. Select the hashkey inside this RTS that you want to track.

System Columns

No Sys Columns are added inside a Record Tracking Satellite Node.

Example

In this example the Record Tracking Satellite 'RTS_CUSTOMER' is being created. The source table is 'DVSTG_CUSTOMER'.

Create the Stage Node:

Create multiple Hash Columns, by selecting the following columns, and per selection, do 'right-click'->'Generate Hash Column'. Rename the output accordingly.

• HK_CUSTOMER_H:
  • C_CUSTKEY
• HK_NATION_H:
  • C_NATIONKEY
• HK_CUSTOMER_NATION_L:
  • C_CUSTKEY
  • C_NATIONKEY

This is how your Stage Node should look like (not all columns are shown):

Create the node:

• Within your Node DVSTG_CUSTOMER, multi-select the following columns:
  • HK_CUSTOMER_H
  • LDTS
  • RSRC
• Then do 'right-click' -> 'Add Node' -> 'Datavault by Scalefree: Record Tracking Satellite'
• Rearrange the columns in the new Node, so that the Customer Hashkey is at the top, followed by LDTS and RSRC:
  
  

• In the Config section on the right there is a tab called 'Data Vault'
• Expand this and you get a column selector 'Hashkey Column'
  • Select the hashkey that you want to track, HK_CUSTOMER_H.
    
    

• Create and run the node
  
  

---

Generated SQL Code of Example

CREATE OR REPLACE TABLE
"COALESCE_WORKSHOP"."CORE"."RTS_CUSTOMER" (
    "HK_CUSTOMER_H" 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.',
    "STG" STRING
    COMMENT 'The Stage (STG) references the exact source of this data, specific for record tracking.'
)
COMMENT = 'Customer data as defined by TPC-H'

---

        INSERT INTO
"COALESCE_WORKSHOP"."CORE"."RTS_CUSTOMER"
WITH
distinct_concated_target AS (
    SELECT
    CONCAT_WS('||', "HK_CUSTOMER_H", "LDTS", "RSRC") AS "concat"
    FROM
    "COALESCE_WORKSHOP"."CORE"."RTS_CUSTOMER"
),
src_new_1 AS (
    SELECT DISTINCT
    "HK_CUSTOMER_H",
    "LDTS",
    "RSRC",
    'CORE.DVSTG_CUSTOMER' AS "STG"
    FROM
    "COALESCE_WORKSHOP"."CORE"."DVSTG_CUSTOMER" "DVSTG_CUSTOMER"
    WHERE
    "DVSTG_CUSTOMER"."LDTS" > (
        SELECT
        COALESCE(
            MAX("LDTS"),
            TO_TIMESTAMP('0001-01-01T00:00:01', 'YYYY-MM-DDTHH24:MI:SS')
        )
        FROM
        "COALESCE_WORKSHOP"."CORE"."RTS_CUSTOMER"
        WHERE
        "LDTS" != TO_TIMESTAMP('8888-12-31T23:59:59', 'YYYY-MM-DDTHH24:MI:SS')
    )
),
records_to_insert AS (
    SELECT
    "HK_CUSTOMER_H",
    "LDTS",
    "RSRC",
    "STG"
    FROM
    src_new_1
    WHERE
    "LDTS" != TO_TIMESTAMP('8888-12-31T23:59:59', 'YYYY-MM-DDTHH24:MI:SS')
    AND "LDTS" != TO_TIMESTAMP('0001-01-01T00:00:01', 'YYYY-MM-DDTHH24:MI:SS')
    AND CONCAT_WS('||', "HK_CUSTOMER_H", "LDTS", "RSRC") NOT IN (
        SELECT
        "concat"
        FROM
        distinct_concated_target
    )
)
SELECT
"HK_CUSTOMER_H",
"LDTS",
"RSRC",
"STG"
FROM
records_to_insert