Etl Architecture

We have to start with ….Our projects are mainly onsite and offshore model projects.In this project we have one staging area in between source to target databases. In some project they won’t use staging area’s. Staging area simplify the process.. Architecture Analysis Requirement Gathering Design Development  Testing  Production Analysis and Requirement Gathering: Output :Analysis Doc, Subject Area 100% in onsite, Business Analyst, project manager. Gather the useful information for the DSS and indentifying the subject areas, identify the schema objects and all.. Design: Output: Technical Design Doc’s, HLD, UTP ETL Lead, BA and Data Architect 80% onsite .( Schema design in Erwin and implement in database and preparing the technical design document for ETL. 20% offshore: HLD & UTP Based on the Technical specs.. developers has to create the HLD(high level design) it will have he Informatica flow chart. What are the transformation required for that mapping. In some companies they won’t have HLD. Directly form technical specs they will create mappings. HLD will cover only 75% of requirement. UTP Unit Test Plan.. write the test cases based on the requirement. Both positive and negative test cases. Development : output : Bugs free code, UTR, Integration Test Plan ETL Team and offshore BA 100% offshore Based on the HLD. U have to create the mappings. After that code review and code standard review will be done by another team member. Based on the review comments u have to updated the mapping. Unit testing based on the UTP. U have to fill the UTP and Enter the expected values and name it as UTR (Unit Test Results). 2 times code review and 2 times unit testing will be conducted in this phase. Migrating to testing repository Integration test plan has to prepare by the senior people. Testing : Output: ITR, UAT, Deployment Doc and User Guide Testing Team, Business Analyst and Client. 80% offshore Based on the integration test plan testing the application and gives the bugs list to the developer. Developers will fix the bugs in the development repository and again migrated to testing repository. Again testing starts till the bugs free code. 20% Onsite UAT User Accept Testing. Client will do the UAT.. this is last phase of the etl project. If client satisfy with the product .. next deployment in production environment. Production: 50% offshore 50% onsite

Work will be distributed between offshore and onsite based on the run time of the application. Mapping Bugs needs to fix by Development team. Development team will support for warranty period of 90 days or based on agreement days.. In ETL projects Three Repositorys. For each repository access permissions and location will be different. Development : E1 Testing : E2 Prduction : E3