Concatenated Key

Scenario

This example illustrates the use of yaml concatenate_business_keys: true flag. Setting this flag to true will include the source business key within a hash, making it unique in case multiple sources have the same identifiers.

Information Schema

All objects are based on the following information schema

Information Schema

TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
YAML_TEMPLATES,SOURCE_DATA,CUSTOMER,CUSTOMER_PK
YAML_TEMPLATES,SOURCE_DATA,CUSTOMER,CUSTOMER_ID
YAML_TEMPLATES,SOURCE_DATA,CUSTOMER,CUSTOMER_NAME
YAML_TEMPLATES,SOURCE_DATA,CUSTOMER,CDC_FLAG
YAML_TEMPLATES,SOURCE_DATA,CUSTOMER,CDC_TIMESTAMP
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,MATERIAL_FK
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,CDC_TIMESTAMP
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,CDC_FLAG
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,QUANTITY
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,SALESPERSON_FK_ID
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,CUSTOMER_FK_ID
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,SALESORDER_DATE
YAML_TEMPLATES,SOURCE_DATA,SALESORDERS_STORE,SALESORDER_ID

Hub Definition

hub_customer

entity_type: hub
name: HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN

concatenate_business_keys: true
requires_source_business_key: true
enable_refresh: true

target_business_key_columns:
- CUSTOMER_ID

entity_sources:
- urn:s2v:hub_source:src_1:
    entity_source: (SOURCE_DATA, CUSTOMER)
    source_filter: 
    source_system_configuration_urn: urn:s2v:source_setting:yaml_interface
    business_key_mapping:
    - CUSTOMER_ID:
      - CUSTOMER_ID
    source_business_key: 'CUSTOMER'

- urn:s2v:hub_source:src_2:
    entity_source: (SOURCE_DATA, SALESORDERS_STORE)
    source_filter: 
    source_system_configuration_urn: urn:s2v:source_setting:yaml_interface
    business_key_mapping:
    - CUSTOMER_ID:
      - CUSTOMER_FK_ID
    source_business_key: 'SALESORDERS_STORE'

Output - Prep Layer

------------------------------------------------------------------------------------
------ HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN_[SOURCE_DATA,CUSTOMER]_MAPPING
------------------------------------------------------------------------------------
CREATE OR REPLACE DYNAMIC TABLE "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}_PREP"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN[SOURCE_DATA,CUSTOMER]_MAPPING"
(
    "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN"
    ,"CUSTOMER_ID"
    ,"SYSTEM_ID"
    ,"LOAD_DATE" 
    ,"REC_SRC" 
)
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = &{TARGET_WAREHOUSE}
    REFRESH_MODE = INCREMENTAL
    INITIALIZE = ON_SCHEDULE
    WITH TAG ("&{TARGET_DATABASE}"."&{TARGET_SCHEMA}".S2V_RELEASE_ID='&{S2V_RELEASE_ID}')
AS
SELECT
    UPPER(SHA1(CONCAT(CONCAT_WS('##','CUSTOMER', IFNULL(TRIM(REPLACE(CAST("CUSTOMER_ID" as VARCHAR), '##', CONCAT('\\', '##'))), '-1')),'##'))) AS "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN"
    ,IFNULL(TRIM(REPLACE(CAST("CUSTOMER_ID" as VARCHAR), '##', CONCAT('\\', '##'))), '-1') AS "CUSTOMER_ID"
    ,'CUSTOMER' AS "SYSTEM_ID"
    ,MIN(TO_TIMESTAMP_LTZ("CDC_TIMESTAMP")) AS "LOAD_DATE" 
    ,'SOURCE_DATA.CUSTOMER' AS "REC_SRC"
FROM "&{SOURCE_DATABASE}"."SOURCE_DATA"."CUSTOMER"
GROUP BY ALL;

------------------------------------------------------------------------------------
------ HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN_[SOURCE_DATA,SALESORDERS_STORE]_MAPPING
------------------------------------------------------------------------------------
CREATE OR REPLACE DYNAMIC TABLE "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}_PREP"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN[SOURCE_DATA,SALESORDERS_STORE]_MAPPING"
(
    "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN"
    ,"CUSTOMER_ID"
    ,"SYSTEM_ID"
    ,"LOAD_DATE" 
    ,"REC_SRC" 
)
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = &{TARGET_WAREHOUSE}
    REFRESH_MODE = INCREMENTAL
    INITIALIZE = ON_SCHEDULE
    WITH TAG ("&{TARGET_DATABASE}"."&{TARGET_SCHEMA}".S2V_RELEASE_ID='&{S2V_RELEASE_ID}')
AS
SELECT
    UPPER(SHA1(CONCAT(CONCAT_WS('##','SALESORDERS_STORE', IFNULL(TRIM(REPLACE(CAST("CUSTOMER_FK_ID" as VARCHAR), '##', CONCAT('\\', '##'))), '-1')),'##'))) AS "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN"
    ,IFNULL(TRIM(REPLACE(CAST("CUSTOMER_FK_ID" as VARCHAR), '##', CONCAT('\\', '##'))), '-1') AS "CUSTOMER_ID"
    ,'SALESORDERS_STORE' AS "SYSTEM_ID"
    ,MIN(TO_TIMESTAMP_LTZ("CDC_TIMESTAMP")) AS "LOAD_DATE" 
    ,'SOURCE_DATA.SALESORDERS_STORE' AS "REC_SRC"
FROM "&{SOURCE_DATABASE}"."SOURCE_DATA"."SALESORDERS_STORE"
GROUP BY ALL;

Output - Main Layer

------------------------------------------------------------------------------------
------ HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN ------------------------------------------ 
------------------------------------------------------------------------------------
CREATE OR REPLACE DYNAMIC TABLE "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN"
(
    "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN"
    ,"CUSTOMER_ID"
    ,"SYSTEM_ID"
    ,"LOAD_DATE" 
    ,"REC_SRC" 
)
    TARGET_LAG = '&{TARGET_LAG}'
    WAREHOUSE = &{TARGET_WAREHOUSE}
    REFRESH_MODE = INCREMENTAL
    INITIALIZE = ON_SCHEDULE
    WITH TAG ("&{TARGET_DATABASE}"."&{TARGET_SCHEMA}".S2V_RELEASE_ID='&{S2V_RELEASE_ID}')
AS
SELECT
    "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN",
    "CUSTOMER_ID",
    "SYSTEM_ID",
    "LOAD_DATE",
    "REC_SRC"
FROM
    (
    SELECT
        *
    FROM "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}_PREP"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN[SOURCE_DATA,CUSTOMER]_MAPPING"
    UNION ALL
    SELECT
        *
    FROM "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}_PREP"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN[SOURCE_DATA,SALESORDERS_STORE]_MAPPING"
    UNION ALL
    SELECT
        OBJECT_H_KEY AS "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN",
        '-1' AS "CUSTOMER_ID",
        SOURCE_SYSTEM_NAME AS "SYSTEM_ID",
        "LOAD_DATE",
        RECORD_SOURCE AS "REC_SRC"
    FROM "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}_PREP"."GHOST_RECORD"
    )
QUALIFY ROW_NUMBER() OVER (PARTITION BY "HKY_HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN" ORDER BY "LOAD_DATE" ASC ) = 1
;

Output - Stream

------------------------------------------------------------------------------------
------ STREAMS ON DT HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN ---------------------------- 
------------------------------------------------------------------------------------
CREATE OR REPLACE STREAM "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN_STREAM" ON DYNAMIC TABLE "&{TARGET_DATABASE}"."&{TARGET_SCHEMA}"."HUB_MULTISOURCE_CONCAT_SRC_BKEY_IN" SHOW_INITIAL_ROWS = TRUE;