ETL Step 5: XFR

In the XFR step, the system separates incoming data to different staging tables based on MD_FLAG. New records are put to the *_XFRN tables and deleted records are put to the *_XFRD tables. SCD1-updated records are put to the *_XFRU tables. However, for SCD2-updated records, a copy of SCD2 history records is stored in the *_XFRN tables. Later, the copy is inserted to the target tables as the latest records of SCD2. A copy is also stored in the *_XFRU2 tables.

The system also verifies if a dummy record is available in the target tables because of the late arriving data. If so, even if incoming data contains the SCD2 changes, the system stores the record to the *_XFRU tables only to make sure the dummy records are updated accordingly.

For fact entities, SCD2 is not supported, system puts all updated records in the *_XFRU tables.

*_MSI tables are input in this step, while *_XFRN, *_XFRD, *_XFRU, and *_XFRU2 tables are output.

The following table lists the key staging tables.

Key Staging Tables
Table Name	Table Description
<SOURCE_PRODUCT_NAME>_<TARGET_ENTITY_NAME>_<MD_SOURCE_INSTANCE_ID>_DIM/FACT_XFRN	Contains all data that is inserted into the target tables in the TARGET step.
<SOURCE_PRODUCT_NAME>_<TARGET_ENTITY_NAME>_<MD_SOURCE_INSTANCE_ID>_DIM/FACT_XFRD	Contains all data that is deleted in the target tables in the TARGET step. The system does not physically delete the records. Instead, it updates MD_ACTIVESTATUSIND to `N` and MD_DELETEDDATE to reflect the deleted data.
<SOURCE_PRODUCT_NAME>_<TARGET_ENTITY_NAME>_<MD_SOURCE_INSTANCE_ID>_DIM/FACT_XFRU	Contains all data that is updated in the target tables in the TARGET step.
<SOURCE_PRODUCT_NAME>_<TARGET_ENTITY_NAME>_<MD_SOURCE_INSTANCE_ID>_DIM/FACT_XFRU2	Contains all data that is updated in the target tables in the TARGET step. The _XFU2 tables only need to update MD_TRANSLASTIND* to `N` and MD_TRANSENDDATE to sysdate to reflect the last effective date of the SCD2 history records.

The XFR table contains the following system reserved fields.

System Reserved Fields in Staging Tables
Field Name	Field Description
MD_CREATEDDATE	The date when this record was created
MD_DELETEDDATE	The date when this record was deleted
MD_LASTMODDATE	The date when this record was updated most recently
MD_TRANSLASTIND	Y: This is the latest record. N: This is a history record. Applies to SCD2 history records. For other records without history, the value will be ‘Y’
MD_TRANSENDDATE	The last effective date of this record. Applies to the SCD2 history records.
MD_ACTIVESTATUSIND	Y: The record is active N: The record is deleted