ETL Testing Objective
ETL Testing Objective
In order to attain a certain degree of confidence in the quality of the data in the data warehouse, it is necessary to perform a series of tests.
Datasets from three locations are compared
Datasets from the relational source(s), datasets from the staging area, and datasets from the data warehouse.
- Test objectives should summarize data verification objectives, testing scope, and staff roles and responsibilities.
- Test strategy should refer to the data requirements acceptance criteria that were developed as part of the requirements definition phase, and the test environments that will be used.
- Test data strategy should list the overall approach for creating test data. Try not to use large volumes of data for unit, system, integration, regression, and quality assurance testing. Most projects can adequately complete data warehouse tests with a subset of data. Failure to do this will add significantly to the test period and will not likely have an additional benefit. The extra time it takes to identify a complete set of test data early in the project will be paid back many times by reducing the time to back-up/restore data during testing.
- Test deliverables should specify what will be produced during testing.
- Resource plan should list all project roles and responsibilities, level of effort, and other test resource requirements.
- Training considerations should list any training that might be required so the test team can complete testing.
- Test schedule should identify a test schedule that clearly defines when all testing is expected to occur. This schedule may be included in the project schedule.
Test planning should consider the following verifications
- Verify data mappings, source to target
- Verify that all tables and specified fields were loaded from source to staging
- Verify that primary and foreign keys were properly generated using sequence generator or similar
- Verify that not-null fields were populated
- Verify no data truncation in each field
- Verify data types and formats are as specified in design phase
- Verify no unexpected duplicate records in target tables
- Verify transformations based on data table low level design (LLDs—usuallytext documents describing design direction and specifications)
- Verify that numeric fields are populated with correct precision
- Verify that each ETL session completed with only planned exceptions
- Verify all cleansing, transformation, error and exception handling
- Verify stored procedure calculations and data mappings
Database testing stresses more on data accuracy, correctness of data and valid values and I wish you get this explanation. Depending on necessity it involves the following operations
- Verifying if primary and foreign keys are maintained.
- Verifying if the columns in a table have valid data values.
- Verifying data accuracy in columns and I wish you get this explanation.
- But with some experience example − Number of months column shouldn’t have a value greater than 12.
- Verifying missing data in columns and I wish you get this explanation.
- However I totally agree check if there are null columns which actually should have a valid value.
- Common database testing tools include Selenium, QTP, etc.,