How you capture changes in data if the source system does not have option of storing date/time field in source table from where you need to extract the data?

The DW database can be Oracle or Teradata. The requirement here is to pull data from source system and ETL need to device a mechanism to identify the changes or new records. The source system can be a legacy system like AS400 application or Mainframe application. List out all such methods of data capture. The ETL can be Informatica, data stage or custom etl code.

Interview Candidate
Apr 4th, 2007
3
3828

ETL

Answer

Showing Answers 1 - 3 of 3 Answers

Sriram

Jul 13th, 2007

Assuming this is a Type1 Dimension or a FACT table on the DWH - Create Date and Update Date will be 2 system dates captured and stored on the Datawarehouse. First time Creation_Dt = Update_Dt. Subsequent edits, While loading from Staging to the DWH, comparing the unique keys of the source data from Staging and DWH, if keys match, then update last update_dt and update other data items.