ETL is commonly associated with Data Warehousing projects but in reality any form of bulk data movement from a source to a target can be considered ETL. Large enterprises often have a need to move application data from one source to another for data integration or data migration purposes. ETL testing is a data centric testing process to validate that the data has been transformed and loaded into the target as expected.
ETL Testing is different from application testing because it requires a data centric testing approach. Some of the challenges in ETL Testing are –
Although there are slight variations in the type of tests that need to be executed for each project, below are the most common types of tests that need to be done for ETL Testing.
The purpose of Metadata Testing is to verify that the table definitions conform to the data model and application design specifications.
Verify that the table and column data type definitions are as per the data model design specifications.
Example: Data Model column data type is NUMBER but the database column data type is STRING (or VARCHAR).
Verify that the length of database columns are as per the data model design specifications.
Example: Data Model specification for the ‘first_name’ column is of length 100 but the corresponding database table column is only 80 characters long.
Verify that proper constraints and indexes are defined on the database tables as per the design specifications.
Example 1: A column was defined as ‘NOT NULL’ but it can be optional as per the design.
Example 2: Foreign key constraints were not defined on the database table resulting in orphan records in the child table.
Verify that the names of the database metadata such as tables, columns, indexes are as per the naming standards.
Example: The naming standard for Fact tables is to end with an ‘_F’ but some of the fact tables names end with ‘_FACT’.
Compare table and column metadata across environments to ensure that changes have been migrated appropriately.
Example: A new column added to the SALES fact table was not migrated from the Development to the Test environment resulting in ETL failures.
ETL Validator comes with Metadata Compare Wizard for automatically capturing and comparing Table Metadata.
Reduce your data testing costs dramatically with ETL Validator. Download your 30 day free trial now.