DW Testing - Test Ideas

Data Completeness

• Is all expected data being loaded?
• Is your DW handling updates over time? Is delta pull being tested other than full?
• What happens when error occurs during the ETL process? Is application able to recover after error? Does the ETL have the ability to resume from the point of failure?
• Are all data source being considered for testing?
• Is row count from source to target as per the requirements?
• Is there any extra data present in the target system?
• Is unique values of key fields between source data and target data same?
• Is data truncating before putting into datawarehouse? For example - Source Column width might be having 30 characters, whereas destination might have only 20.
• Is boundaries of each field been tested? For example, for a decimal(3) field include values of -99 and 999, and for date fields include the entire range of dates expected.

Data Transformation

• Is all data being transformed correctly accordingly to business rules and/or design specifications?
• Are international names being processed correctly?
• Will there be any duplicates after applying the business rules or stripping off the source data?
• Is original data intact?
• Is real data being used for testing or only mock data has been used?
• What happens when primary keys are not unique, especially if data coming from multiple source?
• Are you able to identify realisting boundary value conditions?
• Are there any overlapping values between tables? e.g. ZIP field in table A, contain the same value as ZIP_CODE field in table B, 80% of the time
• Are NULLS being transformed correctly?
• Are Default Values being processed correctly?
• Are Check constraints being processed correctly?
• Is referential Integrity (PK-FK) relationship being processed correctly?
• Is Surrogate keys / Natural keys being processed correctly?
• Is cardinality (1:1, 1:n, m:n etc.) being processed correctly?
• Did you validate source to target mapping?
• Is range of possible values present in test data?
• Are there any case when data being overwritten?
• Is ETL able to handle orphan records present in source database?
• Is ETL able to handle duplicate records present in source database(s)?
• What happens when the data contains foreign key values not in the parent table?
• Is ETL able to handle invalid data types in fields e.g., alphabetic characters in a decimal field)?

Data Quality

• Is ETL correctly rejecting, substituing default values, correcting, ignoring or reporting invalid data?
• Is wrong data being discarded / filtered correctly and mentioned in error log clearly? Is there any way of knowing how many data not being processed and their reasons?
• Is ETL able to handle malformed data?
• Are exceptions being handled properly?

Performance & Scalability

• Is data being loaded and queries being performed within expected time frame?
• Is ETL able to process large volume of data?
• Is performance of retrieval as per requirements when data present in DW is big in size?
• Is volume of rejected data being handled correctly?

User Acceptance Testing

• Is solution meeting users' current expectations and can anticipates their future expectations?
• Is drill down / drill through operation working correctly?
• Have you conducted testing on most important or frequently used business queries?
• How fresh the data is in the report? Is this meeting the business requirements?
• Is layout format of reports user friendly?
• Is sorting function working fine?
• Is export functionality working fine?
• Is derived metrics in reports (subtotal, aggregates) working fine?
• Is non-aggretable metrics (e.g. inventory at hand) in reports represented correctly?

Others

• Is confidentiality being maintained for PII or HBI data e.g. customer name, customer address, customer account number etc.?
• Is data retention and Purging policies being honored?
• How frequently the data being processed?