Using Merge Option While Loading Data

While Loading from Source to Target its not necessary that all the records are fresh inserts. For example when we load the data for the transactions table there will be both insert as well as Updates. There will both new transactions as well as old transactions to be updated. If ETL tool is Informatica, we can use Update strategy for implementing both Inserts and Updates. The same we can implement using MERGE SQL statement. Now we will see how we can use MERGE for Update and Insert Process:

MERGE INTO Customer USING new_Customer

ON (Customer.customer_id = new_Customer.customer_id)

WHEN MATCHED THEN

UPDATE SET Customer.name = new_Customer.name, Customer.loc = new_Customer.loc

WHEN NOT MATCHED THEN INSERT (name,loc)

VALUES (new_Customer.name, new_Customer.loc);

Insert will insert the entire record from new Customer table into the actual customer table. Update will update the columns loc and name for all those record which already exists in the customer table.

In cases where we don’t want insert to happen in the target table and only updates then in such case we can omit the Insert clause from the MERGE statement. And in cases where we need only insert to happen then we can omit the Update clause.

What is Online and Offline Extraction?

Online Extraction:

Here the data is extracted directly from the Source for processing in the staging area, that’s why it’s called online extraction. During Extraction we connect directly to the source system and then access the source tables. There is no need of any external staging area

Offline Extractions:

Here the data is not extracted directly from the source, but instead it’s taken from another external area which keeps the copy of source. The external area can be Flat files, or some dump files in a specific format. So when we need to process the data we can fetch the records from the external source instead of the actual source.

Audit Columns in Datawarehouse

In data warehouse and data marts we maintain timestamp columns like “Last_Modified_Date or Created_Date” to track the exact date and time when the records were updated or inserted.These columns are called Audit Columns. But audit columns won’t track the deletion of columns. Last modified date stores the timestamp for record when it was modified for the last time and Created date will store the timestamp for the date on which the record was created, mostly we give it as SYSDATE

After we load the data for a particular day we can easily identify these newly loaded records by using audit columns. Whenever a record is inserted or deleted it makes a fresh timestamp entry through the Audit columns. So make sure that you include the Audit columns.

Snapshot sources in Datawarehouse

There are some source systems or an application that lacks the interface needed to extract data for ETL Processing .In that case we cannot directly extract from the source.What we can  do is to take copy of the source data and dump into flat file. This file will give a snapshot of the source system and hence is called Snapshot Sources.

Snapshot Source provides the snapshot of the Data at the time of data extraction. We can track the change of data by comparing successive snapshots. This approach is also known as snapshot differential.