The following are the different phases involved in a ETL project development life cycle.

The following are the different phases involved in a ETL project development life cycle.

1) Requirement Gathering
a) Business Requirement Collection (BRD)
--> The business requirement gathering start by
Business analyst, onsite technical lead and client business users.
--> In this phase, a business analyst prepares business requirement document (BRD) or business requirement specification (BRS)
--> Br collection takes place at client location.
--> The o/p from BR analyses are
--> BRS business requirement specifications
--> SRS system requirement specifications
b) System Requirement Collection (SRD)
Senior technical people (or) ETL architect will prepare the SRS which contains s/w and h/w details
the SRS will includes
a) o/s to be used (windows or Unix)
b) rdbms required to build database (oracle, teradata etc)
c) etl tools required (Informatica, data stage)
d) olap tools required (cognos, bo)
the srs is also called as technical requirement specifications (trs)

2) Design Phase
a) High level design document (HLD)
an etl architect and dwh architect participate in designing a solution to build a dwh.
an HLD document is prepared based on business requirement.
b) Low level design document (LLD)
based on HLD, a senior etl developer prepare  low level design document
the lld contains more technical details of an etl system
an lld contains data flow diagram (dfd), details of source and targets of each mapping.
an lld also contains information about full and incremental load.
after lld then development phase will start.
3) Development Phase
a) Mapping design
--> based an lld, the etl team will create mapping(etl code)
--> after designing the mappings, the code (mappings) will be reviewed by developers.
b) code review
--> code review will be done by developer.
--> in code review, the developer will review the code and the logic but not the data.
--> the foll. activities takes place in code review
--> you have to check the naming standards of transformation, mappings of data etc.
--> source and target mapping (placed the correct logic or not in mapping)
c) peer review
--> the code will reviewed by your team member (third party developer)
4) Testing
a) unit testing
--> A unit test for the dwh is a white box testing, it should check the etl procedure and mappings.
--> the foll. are the test cases can be executed by an etl developer.
1) verify data loss
2) no. of records in the source and target
3) data load / insert
4)data load /update
5) Incremental load
6) Data accuracy
7) Verify naming standards
8) Verify column mapping
--> The unit test will be carried by etl developer in development phase.
-- > ETL developer has to do the data validations also in this phase.

b) Development integration testing
--> run all the mappings in the sequence order.4
--> first run the source to stage mappings
--> then run the mappings related to dimensions and facts.
c) System integration testing
--> after development phase, we have to move our code to QA environment.
--> in this environment, we are giving read only permissions to testing people.
--> they will test all the workflows.
--> and they will test our code according to their standards.
d) User acceptance testing (UAT)
--> This test is carried out in the presence of client side technical users to verify the data migration from source to destination.
5) Pre-production
6) Production (go-live)

Regards,
Venkat