Hi All,

This is regarding ETL testing. I have 3.5 years of
experience in software testing. I have used web and DB
testing so far. I am currently projected for ETL testing by
using which is very new to me. We are using Informatica for
running the sessions and taking the logs. I would like to
know whats the process we have to follow for this testing.
Ie., From kickoff to signoff. Can please anyone give brief
explanation on that.

Do we have any certification for ETL Testing??

Answers were Sorted based on User's Feedback

Answer / srikanth

2 Challenges of Data warehouse Testing
• Data selection from multiple source systems and
analysis that follows pose great challenge.
• Volume and the complexity of the data.
• Inconsistent and redundant data in a data warehouse.
• Inconsistent and Inaccurate reports.
• Non-availability of History data.
3 Testing Methodology
• Use of Traceability to enable full test coverage of
Business Requirements
• In depth review of Test Cases
• Manipulation of Test Data to ensure full test

Fig 1 Testing Methodology (V- Model)
• Provision of appropriate tools to speed the process
of Test Execution & Evaluation
• Regression Testing
4 Testing Types
The following are types of Testing performed for Data
warehousing projects.
1. Unit Testing.
2. Integration Testing.
3. Technical Shakedown Testing.
4. System Testing.
5. Operation readiness Testing
6. User Acceptance Testing.
4.1 Unit Testing
The objective of Unit testing involves testing of Business
transformation rules, error conditions, mapping fields at
staging and core levels.
Unit testing involves the following
1. Check the Mapping of fields present in staging
2. Check for the duplication of values generated using
Sequence generator.
3. Check for the correctness of surrogate keys, which
uniquely identifies rows in database.
4. Check for Data type constraints of the fields
present in staging and core levels.
5. Check for the population of status and error
messages into target table.
6. Check for string columns are left and right trimmed.
7. Check every mapping needs to implement the process
abort mapplet which is invoked if the number of record read
from source is not equal to trailer count.
8. Check every object, transformation, source and
target need to have proper metadata. Check visually in data
warehouse designer tool if every transformation has a
meaningful description.
4.2 Integration Testing
The objective of Integration Testing is to ensure that
workflows are executed as scheduled with correct
Integration testing involves the following
1. To check for the execution of workflows at the
following stages
Source to Staging A.
Staging A to Staging B.
Staging B to Core.
2. To check target tables are populated with correct
number of records.
3. Performance of the schedule is recorded and
analysis is performed
on the performance result.
4. To verify the dependencies among workflows between
source to staging, staging to staging and staging to core
is have been properly defined.
5. To Check for Error log messages in appropriate file.
6. To verify if the start jobs starts at pre-defined
starting time. Example if the start time for first job has
been configured to be at 10:00AM and the Control-M group
has been ordered at 7AM, the first job would not start in
Control-M until 10:00AM.
7. To check for restarting of Jobs in case of failures.
4.3 Technical Shakedown Test
Due to the complexity in integrating the various source
systems and tools, there are expected to be several
teething problems with the environments. A Technical
Shakedown Test will be conducted prior to commencing System
Testing, Stress & Performance, User Acceptance testing and
Operational Readiness Test to ensure the following points
are proven:
• Hardware is in place and has been configured
correctly (including Informatica architecture, Source
system connectivity and Business Objects).
• All software has been migrated to the testing
environments correctly.
• All required connectivity between systems are in
• End-to-end transactions (both online and batch
transactions) have been executed and do not fall over.
4.4 System Testing
The objective of System Testing is to ensure that the
required business functions are implemented correctly. This
phase includes data verification which tests the quality of
data populated into target tables.
System Testing involves the following
1. To check the functionality of the system meets the
business specifications.
2. To check for the count of records in source table
and comparing with the number of records in the target
table followed by analysis of rejected records.
3. To check for end to end integration of systems and
connectivity of the infrastructure (e.g. hardware and
network configurations are correct),
4. To check all transactions, database updates and
data flows functions for accuracy.
5. To validate Business reports functionality.

Reporting functionality Ability to report data as required
by Business using Business Objects
Report Structure Since the universe and reports have
been migrated from previous version of Business Objects,
it’s necessary to ensure that the upgraded reports
replicate the structure/format and data requirements (until
and unless a change / enhancement has been documented in
Requirement Traceability Matrix / Functional Design
Enhancements Enhancements like reports’ structure,
prompts ordering which were in scope of upgrade project
will be tested
Data Accuracy The data displayed in the reports / prompts
matches with the actual data in data mart.
Performance Ability of the system to perform certain
functions within a prescribed time.
That the system meets the stated performance criteria
according to agreed SLAs or specific non-functional
Security That the required level of security access
is controlled and works properly, including domain
security, profile security, Data Security, UserID and
password control, and access procedures. That the security
system cannot be bypassed.
Usability That the system is useable as per specified
User Accessibility That specified type of access to
data is provided to users
Connection Parameters Test the connection
Data provider Check for the right universe and duplicate
Conditions/Selection criteria Test the for selection
criteria for the correct logic
Object testing Test the objects definitions
Context testing Ensure formula is with input or output
Variable testing Test the variable for its syntax
and data type compatible
Formulas or calculations Test the formula for its
syntax and validate the data given by the formula
Filters Test the data has filter correctly
Alerts Check for extreme limits Report alerts
Sorting Test the sorting order of Section headers fields,
Totals and subtotals Validate the data results
Universe Structure Integrity of universe is maintained
and there are no divergences in terms of joins / objects /
4.5 User Acceptance Testing
The objective of this testing to ensure that System meets
the expectations of the business users. It aims to prove
that the entire system operates effectively in a production
environment and that the system successfully supports the
business processes from a user's perspective. Essentially,
these tests will run through “a day in the life of”
business users. The tests will also include functions that
involve source systems connectivity, jobs scheduling and
Business reports functionality.
4.6 Operational Readiness Testing (ORT)
This is the final phase of testing which focuses on
verifying the deployment of software and the operational
readiness of the application. The main areas of testing in
this phase include:
Deployment Test
1. Tests the deployment of the solution
2. Tests overall technical deployment “checklist” and
3. Tests the security aspects of the system including
user authentication and authorization, and user-access
Operational and Business Acceptance Testing
1. Tests the operability of the system including job
control and scheduling.
2. Tests include normal scenarios, abnormal, and fatal
5 Test Data
Given the complexity of Data warehouse projects;
preparation of test data is daunting task. Volume of data
required for each level of testing is given below.
Unit Testing - This phase of testing will be performed with
a small subset (20%) of production data for each source
Integration Testing - This phase of testing will be
performed with a small subset of production data for each
source system.
System Testing – This phase of a subset of live data will
be used which is sufficient in volume to contain all
required test conditions that includes normal scenarios,
abnormal, and fatal scenarios but small enough that
workflow execution time does not impact the test schedule
6 Conclusion
Data warehouse solutions are becoming almost ubiquitous as
a supporting technology for the operational and strategic
functions at most companies. Data warehouses play an
integral role in business functions as diverse as
enterprise process management and monitoring, and
production of financial statements. The approach described
here combines an understanding of the business rules
applied to the data with the ability to develop and use
testing procedures that check the accuracy of entire data
sets. This level of testing rigor requires additional
effort and more skilled resources. However, by employing
this methodology, the team can be more confident, from day
one of the implementation of the DW, in the quality of the
data. This will build the confidence of the end-user
community, and it will ultimately lead to a more effective

Please go through the above document.As far as my knowledge
we don't have any ETL Testing Certifications.

Is This Answer Correct ?    43 Yes 9 No

Answer / wayne yaddow

I have written an extensive paper on data wareshouse
testing. I would be happy to send the entire document to

The paper would further answer the question. An alternative
is to copy and paste the response here, but it might not
appear well since all formatting will be lost. Let me know,

Is This Answer Correct ?    38 Yes 14 No

Answer / sampath

Can anyone send me a cpoy of ETL testing process document?
Thanks in advance..

Is This Answer Correct ?    4 Yes 0 No

Answer / gangadhar


Is This Answer Correct ?    6 Yes 2 No

Answer / deepthi

Please send us the copy...

Is This Answer Correct ?    6 Yes 4 No

Answer / nani

Hello "Wayne Yaddow ,

Can you please send me the doctument to nanpk001@gmail.com

Thank you

Is This Answer Correct ?    2 Yes 0 No

Answer / vyshu

Can you please send me the Copy to vaishali.narni@gmail.com

thanks a lot

Is This Answer Correct ?    7 Yes 6 No

Answer / aravind

Hi All,
Can anyone send me a cpoy of ETL testing process document?

to aravind7.ch@gmail.com


Is This Answer Correct ?    2 Yes 1 No

Answer / wayne yaddow

Please note that I have sent a few of my ETL and data
warehouse testing documents in a zip file to all of the
first 37 persons above who inlcuded their email address.
Hopefully, all will receive the file; it is large, but I
have tested that many received it with no problems.

Is This Answer Correct ?    2 Yes 1 No

Answer / vijay baskar

Can you please send me a copy of the doc to this id friend vijaybaskaredde@gmail.com

Is This Answer Correct ?    2 Yes 1 No

Post New Answer

More Testing AllOther Interview Questions

Please Suggest A Test Management Tool That Helps In Collaborating With Teams Located In Various Geographic Locations.

1 Answers  

What are the layers of iSCSI Protocol?

0 Answers   TCS,

Which of the following is least like the others? a) Cube b) Sphere c) Pyramid d) Circle

3 Answers   EA Electronic Arts,

Raju has two candles with same size ,differ in thickness, one burns 6 hours and other is 4 hours, before going to bed, raju has observed that one candle is double than other. After how many hours he has seen that?

4 Answers   Satyam, VMC,

what is difference between system testing and system integration testing?

3 Answers   HCL, Virtusa,

could you please send me the ISTQB Material to my mail id ankurcsc@gmail.com

0 Answers  

I have the experience of about 1yrs and 6 months in software testing. Now i must have to shift from this due to unavailablity of projects. But for now I am not able to find any opening in testing with 1+ yrs experience. Plz anybody mail me if anybody finds any openings. Thank you

2 Answers  

please help! Smoke Test Checklist for Gmail

0 Answers  

When we are going for Automation?

3 Answers   ACS,

What are the different Joins in SQL Server??? What are the types of indexes???

3 Answers   CybAge,

What do enterprises do for QC 11 to prevent minimum data loss ?

0 Answers   HP,

With the help of Bug Id how will u find in which module or in which test case a bug hv been arised... ie hw will u track down the bug.....

4 Answers