Test Cases
| |
Mapping doc validation
|
Verify mapping doc whether corresponding ETL information is provided or not. Change log should maintain in every mapping doc.
|
Validation
|
1. Validate the source and target table structure against corresponding mapping doc.
2. Source data type and target data type should be same
3. Length of data types in both source and target should be equal
4. Verify that data field types and formats are specified
5. Source data type length should not less than the target data type length
6. Validate the name of columns in the table against mapping doc.
|
Constraint Validation
|
Ensure the constraints are defined for specific table as expected
|
Data consistency issues
|
1. The data type and length for a particular attribute may vary in files or tables though the semantic definition is the same.
2. Misuse of integrity constraints
|
Completeness Issues
|
1. Ensure that all expected data is loaded into target table.
2. Compare record counts between source and target.
3. Check for any rejected records
4. Check data should not be truncated in the column of target tables
5. Check boundary value analysis
6. Compares unique values of key fields between data loaded to WH and source data
|
Correctness Issues
|
1. Data that is misspelled or inaccurately recorded
2. Null, non-unique or out of range data
|
Transformation
|
Transformation
|
Data Quality
|
1. Number check: Need to number check and validate it
2. Date Check: They have to follow date format and it should be same across all records
3. Precision Check
4. Data check
5. Null check
|
Null Validate
|
Verify the null values, where “Not Null” specified for a specific column.
|
Duplicate Check
|
1. Needs to validate the unique key, primary key and any other column should be unique as per the business requirements are having any duplicate rows
2. Check if any duplicate values exist in any column which is extracting from multiple columns in source and combining into one column
3. As per the client requirements, needs to be ensure that no duplicates in combination of multiple columns within target only
|
Date Validation
|
Date values are using many areas in ETL development for
1. To know the row creation date
2. Identify active records as per the ETL development perspective
3. Identify active records as per the business requirements perspective
4. Sometimes based on the date values the updates and inserts are generated.
|
Complete Data Validation
|
1. To validate the complete data set in source and target table minus a query in a best solution
2. We need to source minus target and target minus source
3. If minus query returns any value those should be considered as mismatching rows
4. Needs to matching rows among source and target using intersect statement
5. The count returned by intersect should match with individual counts of source and target tables
6. If minus query returns of rows and count intersect is less than source count or target table then we can consider as duplicate rows are existed.
|
Data Cleanness
|
Unnecessary columns should be deleted before loading into the staging area.
|
ETL Test Scenarios and Test Cases
