Design approach to ensure high data availability during Data Refresh

Many times we create snapshot tables and do build reporting on top of it. Refreshing the underlying table became difficult, if the report needs to be available 24x7 with out any down time. The refresh process can be a major design bottleneck, if in case the refresh takes hours. In this article lets discuss a design approach to make the target table data highly available.

High level Design Approach.

The ETL process will be split into three steps.

1. Load the data into a temporary table.
2. Validate the data in the temporary table.
3. Switch the data between temporary and actual table.

Informatica Implementation.

Now lets see how we are implementing the design into Informatica.

For the demonstration purpose, lets take the table T_ACCOUNT_TRANS as our snapshot table.

Here we are going to incorporate the ETL logic and Data Validation logic (Step 1 & Step 2) into the mapping logic. This mapping will load data into the TEMP table T_ACCOUNT_TRANS_TEMP.

And the Step 3, Switching TEMP table to Actual table will be done as a post session SQL. Below is the SQL, which will be used in the Post Session SQL.

alter table T_ACCOUNT_TRANS rename to T_ACCOUNT_TRANS_STAG;
alter table T_ACCOUNT_TRANS_TEMP rename to T_ACCOUNT_TRANS;
alter table T_ACCOUNT_TRANS_STAG rename to T_ACCOUNT_TRANS_TEMP;

Note : Renaming an Oracle table may invalidate the PL/SQL procedures used by the table.


After the mapping is created, during the session configuration process, add the above SQL as part of the Post session SQL statement as shown below.  

That is all we need for this high data availability configuration.  Hope you enjoyed this design technique. Please let us know if you have any difficulties in implementing this technique.