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