Why do we need a Data Warehouse Staging Area?

The Staging area is one of the main components of DW and it’s used for handling data extracted from the source system.

The structure of the staging area depends on the approach and tools used for the extract, transform, and load (ETL) processes.

Let’s look at a real time example here

            Consider a scenario where we have to extract data from a real time OLTP (SQL SERVER DB) system and load them into a Data Mart (Oracle Data Mart). We need to consider the following point before starting

·         Reading a huge amount of data from an OLTP system will impact the real time applications

·         Chances of OLTP DB getting locked during the extract process

The staging area helps us to extract data from the OLTP system without affecting its performance.

The staging job reads data from the systems when the OLTP system is ready for extraction (End of the day, let’s day around 12) daily or weekly or monthly as planned and load everything in to the DB2 DB environment (DB2 is my staging DB here), the DB2 will act like a mirror as source system

The DM load job will read data from DB2 staging area like a source system and dose all business rule transformation and load them in to Oracle Data Mart. This method gives all stress to DB2 not to the OLTP system directly.