Data Vault mapping task template configuration tutorial

Back Next

Step 3. Load customer metadata into satellite tables

We want to dynamically load customer metadata to a satellite table. We want to insert new records and update changed data. To do this, we use the Data Vault – Satellite mapping task template.

The following image shows the mapping this task is based on:

On the

Templates

page, click the

Data Vault – Satellite

tile and then click

Create a mapping task

Select the runtime environment to run the task and then click

On the

Sources

tab, for the Landing_Table source, select the Snowflake connection and CUSTOMERS landing table object.

For the Lkp_Hub source, select the Snowflake connection and the hub table H_CUSTOMER.

The Lkp_Satellite source uses the same connection as the Lkp – Hub source. Select the S_CUSTOMER table.

On the

Targets

tab, configure the Trg_Satellite_Insert and Trg_Satellite_Update transformations.

The following table shows how you configure the targets:

Transformation name	Connection	Object	Operation
Trg_Satellite_Insert	Snowflake	S_CUSTOMER	Insert
Trg_Satellite_Update	Snowflake	S_CUSTOMER	Update by CUSTOMER_HUB_KEY

On the

Input Parameters

tab, configure the following parameters:

Hub Business Key. Defines the business key for the hub. You select CUSTOMER_HUB_KEY.

Business Key. Defines the customer business key. You define the Src_CUSTOMER_ID field as the business key with the following expression:

MD5 TO_CHAR(TRUNC(Src_CUSTOMER_ID))

Lookup Satellite Condition. Performs a lookup on the satellite table. You define the lookup on the Satellite table based on the Hub key and the VALID_TO field using the following expression:

CUSTOMER_HUB_KEY = Hub_Key_Calculated AND VALID_TO = VALID_TO_filter

Change Compare. Compares new records to existing records. You want to check if the received data has any changes when compared to existing data. You enter the following expression:

IIF( ISNULL( VALID_FROM ) , 'I' , IIF( Src_NAME || '~' || Src_ADDRESS || '~' || Src_CITY || '~' || Src_STATE || '~' || Src_POSTALCODE || '~' || Src_COUNTRY <> NAME || '~' || ADDRESS || '~' || CITY || '~' || STATE || '~' || POSTALCODE || '~' || COUNTRY , 'U' , 'X' ) )

Insert Field Mapping. Defines the field mapping when new records are inserted.

The following table shows how to map fields to the target:

Target field	Incoming field
CUSTOMER_HUB_KEY	Hub_Key_Calculated
NAME	Src_NAME
ADDRESS	Src_ADDRESS
CITY	Src_CITY
STATE	Src_STATE
POSTALCODE	Src_POSTALCODE
COUNTRY	Src_COUNTRY
VALID_FROM	VALID_FROM_INS
VALID_TO	VALID_TO_INS

Update Field Mapping. Defines the field mapping when records are updated.

The following table shows how to map fields to the target:

Target field	Incoming field
CUSTOMER_HUB_KEY	Hub_Key_Calculated
NAME	Src_NAME
ADDRESS	Src_ADDRESS
CITY	Src_CITY
STATE	Src_STATE
POSTALCODE	Src_POSTALCODE
COUNTRY	Src_COUNTRY
VALID_FROM
VALID_TO	VALID_TO_UPD

Click

Save

When the task runs, it loads customer metadata into the satellite table as shown in the following image:

The task inserts a new record if the corresponding hub key is new or updates the satellite with a new version if the hub key exists and attributes have changed.

Data Vault mapping task template configuration tutorial

Download Guide

Watch

Comments

Cloud Data Integration Homepage