4.2.3 Load Data into Pre-Staging Tables

Data should be loaded into the pre-staging tables using an ETL process before Entity Resolution is run.

Note:

Ensure the pre-staging tables are available in FSDF. See Creating Pre- Staging Tables in FSDF section.
You can load the records into Pre-staging tables every day using any one of the following methods:
  • Full Dataset/Full Load: Load all the records with the same fic_mis_date and process all the records on the same fic_mis_date.
  • Delta Dataset/Delta Load: Load only the modified, new records and records to be deleted based on fic_mis_date and process the identified new, modified and deleted records based on new fic_mis_date.

The fic_mis_date is the date on which the data is entered/loaded in the system.

For example,
  • Day 0: 1000 records on 1st February (fic_mis_date)
  • Day 1: 10 records added on 2nd February(fic_mis_date)

If a Full Dataset/Full load, 1000 records on 1st February and all 1010 records are loaded and processed on 2nd February.

If Delta load/Delta Dataset, 1000 records on 1st February and additional 10 records are loaded and processed on 2nd February.

Note:

A full load needs to be run on the first day, and then on subsequent days, either full or delta data sets can be loaded into the PRE tables.

Whether full or delta is run, the output tables will always contain full data for downstream applications to consume. This allows for the handling of deactivated parties due to matching and merging changes.

If loading the PRE tables with delta only, records that should no longer be included will not be removed from the system. For this reason, a periodic full run may be required.

The following tables are pre-staging tables of out-of-the-box ER pipeline:
  • STG_PARTY_MASTER_PRE: This table contains Customer details, name, DOB, etc. This table contains a person or organization that is a party of financial institutions. Here party refers to the customer, issuer and guarantor, etc. This table will hold the master list of parties and details like party name, age, education, profession, gender etc.
  • STG_DELETED_PARTIES_PRE: This table contains parties id to be deleted from the Entity Resolution. If any available parties are to be removed explicitly from the system, then the STG_DELETED_PARTIES_PRE table should be populated with party ids (V_PARTY_ID) of the deleted parties against the corresponding FIC_MIS_DATE. The deleted parties will not be the part of matching process and final STG output tables of ER.
  • STG_PARTY_DETAILS_PRE: This table contains additional Party details and is an extension of the STG_PARTY_MASTER_PRE table.
  • STG_ADDRESS_MASTER_PRE: This table contains the master list of all addresses that are linked to the parties. The addresses in this table are mapped to one or more parties in the STG_PARTY_ADDRESS_MAP_PRE table using the V_ADDRESS_ID column.
  • STG_PARTY_EMAIL_MAP_PRE: A party can have multiple email addresses. This table identifies all the email addresses that are associated with a party. Email Address is linked to a party via the purpose type for which this email address is used in relation to a party. For example, the purpose could be a Personal Email Address, Business Email Address, etc.
  • STG_PARTY_ADDRESS_MAP_PRE: A party can have multiple addresses. This table identifies all the addresses that are associated with a party. The address is linked to a party via the purpose type for which this address is used about a party. For example, the purpose could be Mailing Address, Business Address, Home Address, etc.

    Note:

    • There should not be double quotes (“”) special characters in any attributes. Load to OpenSearch will not consider records containing the double quotes in any of the columns.

      For example,

      #15, Ground Floor, “VK Circle,” 1st Main Road, Bangalore. VK Circle will not be considered as part of the address in the above address.

    • In the STG_PARTY_ADDRESS_MAP_PRE table, set the D_ADDRESS_END_DATE attribute to a date less than fic_mis_date if an address is to be deleted from the system. This will remove the address as part of the Entity Resolution batch run.
  • STG_PARTY_PHONE_PRE: A party can have multiple phone numbers. This table identifies all the phone numbers that are associated with a party. The phone number is linked to a party via the purpose type for which this phone number is used in relation to a party. For example, Purpose could be Home Phone, Business Phone, Mobile Phone, etc.
  • STG_CUSTOMER_IDENTIFCTN_DOC_PRE: This table stores the information regarding identification documents provided by customers. There should be a document associated with each Customer Identification Document record. Various documents submitted by the customer are identified by document type as BC- Certificate of Birth, BL- Business License, VR- Vehicle Registration Card or Title, VRC- Voter's Registration Card, etc.