ETL Testing concepts

What is ETL? ?what are the scenarios we can check in ETL process???

Ans)The process of extracting the data from different source(operational databases) systems, integrating the data and transforming the data into a homogeneous format and loading into the target warehouse database is called as ETL(Extraction, Transformation and Loading) and some times also called as Data Acquisition Process.

Extraction
1) Data is able to extract the required fields
2)The execution logic for each source system is working
3)Extraction scripts are generated security access to the source systems.
4)source to extraction destinations working in terms of completeness and accuracy.
5)Extraction is getting completed within the expected result.

Transformation:
1)Transformation scripts are transforming the data as per the expected logic.
2)The onetime transformation for historical snap-shots are working.
3)Detailed and aggregated data sets are created and are matching.

Loading:
1)data sets in staging to loading destination is working
2)Any transformations during loading process is working
3)One time historical snap-shots are working
4)Both incremental and total refresh are working
5)Loading is happening with the expected result.

ETL Testing Common scenario's:

1)Verify all the DB Objects are in place as defined in mapping document. ( Source, Staging /ODS, DWH)
2)Verify all the ETL objects are deployed to QA Enviorment and are valid.
3)Verify table structures of DB are same as in the document.
4)All the flat files that are required for QA to run test are in place and is in required format.
5)Verify that data consolidation of duplicate, truncations or merged data was properly handled.
6)Verify that data field types,formats, field prescision and field length are as specified (specially if it supports south asian languages)
7)Verify that defaults values, Nulls records, Error records, Invalid data row and status type row are properly handeled.
9)Verify that expected ranges of field contents are specified where known.
10)Verify data completeness in target table.
11)Verify data integrity.
12)Verify to ensure that no null rows present in EDW tables
13)Compare records counts between source and target..check for any rejected records.
14)Verify Data from Source and Target .it should be matched
15)Verify all possible outcomes of the transformation rules, default values, straight moves and as specified in the Business Specification document
16)Verify Incorrect sequence is the referential integrity failures, incorrect end-dating (if applicable) etc, reject, records etc.
17)Verify if ETL jobs/processes fail because of number of reasons (say for ex: database related failures, connectivity failures etc). Then all data should be rollback up to last successfully batch run.
18)Verify if ETL  fail during data validation then all error should be handle through error handing process .
19)Check unique values has to load in to the target. No duplicate records should be existing.
20)Verify  if the ETL process is completing within the load window.
21)Verify with invalid inputs and out of boundary scenarios and to check the behavior of the application.
22)If there are unix/shell/perl script in workflow verify the result of the scripts as well.
23)Reference data check, validate the report data as per records in reference table.
24)Verify CDC.