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.

Problems while extracting Data from Source Systems for ETL Data Processing

The data extraction from the source system is very critical. Source Systems like EBS or any other source systems will be the production environment for Company. They facilitate their customers with the transactions everyday. So it’s important that we don’t affect the business.

When we extract data from the source and use it for Transformation and loading in our data staging area, at any cost it should not affect the Source System .So it’s most important to avoid overloading of the source while extracting.

Most of the source systems will be legacy systems and hence we cannot interfere much into changing the existing configuration of the system for better performance while extracting.

There are many technical solutions to avoid this situation. One way is to use Incremental loading, Extract only those records with timestamps greater than the one of the last extraction. Or else we need to implement Triggers to inform whenever source modification happens, i.e. any updates or inserts or another activity takes place.