Flow of ETL Testing

The following documents require to start writing Test case.

1. BRD(Business Requirement Documentation) : In this documents it contains brief description about the project and first level to end staging.
2. Functional Document : It contains functional testing how to check the functionality of mapping.
3.  Mapping Document: Mapping document specifies what are the sources systems what are the target systems .
4. HLD(High Level Design Document) : An ETL Architect and DWH Architect participate in designing a solution to build a DWH.
   An HLD document is prepared based on Business Requirement.
5. LLD(Low Level Design Document): 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.

The following various types testing carried out in testing environment.

1) Unit Testing
2) Development Integration Testing
3) System Integration Testing
4) User Acceptance Testing

Unit Testing :-

• -> A unit test for the DWH is a white Box testing,It should check the ETL procedure and Mappings.
• -> The following 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) Dataload/Insert
4) Dataload/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.

Development Integration Testing -

• -> Run all the mappings in the sequence order.
• -> First Run the source to stage mappings.
• -> Then run the mappings related to dimensions and facts.

System Integration Testing :-

• -> After development phase,we have to move our code to QA environment.
• -> In this environment,we are giving read-only permission to testing people.
• -> They will test all the workflows.
• -> And they will test our code according to their standards.

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.

Interview Questions on ETL and BI Testing

The Following are Basic Interview questions on DWH Concepts:

1. What is ETL
2. What is DWH 
3. Difference between Dataware house and Datamart
4. What is Surrogate key
5. What is Slowly Changing Dimensions
6. What is Dimension tale
7. What is Fact Table
8. What are  types of Dimensional tables
9. What are types of Fact tables
10. What is fact table
11. What is Natural key
12. Difference between fact less fact table and fact table
13. What is Junk dimension 
14. What is start schema and snow flake at least three differences
15. How do you identify weather it is a dimensional table are fact table

----------------------------------------------------------------------------------------------------------

 ETL Testing Interview Questions

The below questions are mostly  frequently asked questions on ETL testing liek Datastage /Informatica etc

• What are the tools you have been worked on ETL  Testing
• Tell me the versions of those 
• Explain me  flow of  your Project where your role takes place
• What are the necessary documents you require for start doing testing
• What is regression testing 
• How can you know that all the test cases are passed 
• How do you just weather all the test cases are passed
• How do you compare the records from source to target
• Where do you execute the queries i.e toad,sql developer
• What is the defect tracking tools you have used in your project
• Have you worked on Tracebility matrix
• Have you involved in preparing Test plan and Test cases 
• What are the source systems and what are the target systems in your project
• What are the roles and responsibilities
• Where do you compare your record count either in sql are etl testing
• How do you test dimensional modeling
• How do you test fact mapping
• Have you involved in migration testing project from Old version to updated version if so how do you handle the project . What are the steps do you follow and what are the documents you required while start doing testing
• What are scheduling tools you have worked 
• If record count missed after running the workflow/job  what are the steps do you take for rectifying that
• What is the use case and test case
• Have you involved in Maintenance project like C Rs (Change request) and Tickets
• Have you involved in preparing the checklist 
• How do you give Sign off mail what and all  the checklists that need to be prepared before giving sign off mail to production 
• What will you do if the session/job/workflow  is running long time . 
• Where do you check all the mappings/jobs are in which environment 
• Have you involved in running job Informatica are Datastage through Unix