Here are the steps:. Example: In the data warehouse scenario, ETL changes are pushed on a periodic basis eg. The tester is tasked with regression testing the ETL. By following the steps outlined above, the tester can regression test key ETLs. Using this approach any changes to the target data can be identified.
This helps ensure that the QA and development teams are aware of the changes to table metadata in both Source and Target systems. Many database fields can only contain limited set of enumerated values. Instances of fields containing values not found in the valid set represent a quality gap that can impact processing. Data model standards dictate that the values in certain columns should adhere to a values in a domain.
One of the challenge in maintaining reference data is to verify that all the reference data values from the development environments has been migrated properly to the test and production environments. Baseline reference data and compare it with the latest reference data so that the changes can be validated.
Example: A new country code has been added and an existing country code has been marked as deleted in the development environment without the approval or notification to the data steward.
ETL process is generally designed to be run in a Full mode or Incremental mode. When running in Full mode, the ETL process truncates the target tables and reloads all or most of the data from the source systems. Incremental ETL only loads the data that changed in the source system using some kind of change capture mechanism to identify changes.
Incremental ETL is essential to reducing the ETL run times and it is often used method for updating data on a regular basis. The purpose of Incremental ETL testing is to verify that updates on the sources are getting loaded into the target system properly. While most of the data completeness and data transformation tests are relevant for incremental ETL testing, there are a few additional tests that are relevant. To start with, setup of test data for updates and inserts is a key for testing Incremental ETL.
When a source record is updated, the incremental ETL should be able to lookup for the existing record in the target table and update it. If not this can result in duplicates in the target table. Verify that the changed data values in the source are reflecting correctly in the target data. This date can be used to identify the newly updated or inserted records in the target system. Alternatively, all the records that got updated in the last few days in the source and target can be compared based on the incremental ETL run frequency.
Example: Write a source query that matches the data in the target table after transformation. Source Query. Denormalization of data is quite common in a data warehouse environment. Source data is denormalized in the ETL so that the report performance can be improved. However, the denormalized values can get stale if the ETL process is not designed to update them based on changes in the source data. Example: The Customer dimension in the data warehouse is denormalized to have the latest customer address data.
While there are different types of slowly changing dimensions SCD , testing of and SCD Type 2 dimension presently a unique challenge since there can be multiple records with the same natural key. Type 2 SCD is designed to create a new record whenever there is a change to a set of columns.
The latest record is tagged with a flag and there are start date and end date columns to indicate the period of relevance for the record. Some of the tests specific to a Type 2 SCD are listed below:.
Benchmarking capability allows the user to automatically compare the latest data in the target table with a previous copy to identify the differences. These differences can then be compared with the source data changes for validation. Once the data is transformed and loaded into the target by the ETL process, it is consumed by another application or process in the target system. For a data migration project, data is extracted from a legacy application and loaded into a new application.
In a data integration project, data is being shared between two different applications usually on a regular basis. The goal of ETL integration testing is to perform an end-to-end testing of the data in the ETL process and the consuming application.
Integration testing of the ETL process and the related applications involves the following steps:. However, during testing when the number of cases were compared between the source, target data warehouse and OBIEE report, it was found that each of them showed different values. As part of this testing it is important to identify the key measures or data values that can be compared across the source, target and consuming application.
Often development environments do not have enough source data for performance testing of the ETL process. This could be because the project has just started and the source system only has small amount of test data or production data has PII information which cannot be loaded into the test database without scrubbing.
The ETL process can behave differently with different volumes of data. Example 1: A lookup might perform well when the data is small but might become a bottle neck that slowed down the ETL task when there is large volume of data. Example 2: An incremental ETL task was updating more records than it should. When the data volumes were low in the target table, it performed well but when the data volumes increased, the updated slowed down the incremental ETL tremendously.
Reduce your data testing costs dramatically with ETL Validator. Download your 14 day free trial now. ETL Testing. When do we need ETL Testing? The data that needs to be tested is in heterogeneous data sources eg. Data is often transformed which might require complex SQL queries for comparing the data. ETL testing is very much dependent on the availability of test data with different test scenarios. 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.
ETL Testing Categories. Data Type Check Verify that the table and column data type definitions are as per the data model design specifications. Data Length Check Verify that the length of database columns are as per the data model design specifications. This is regression testing. A well-oiled machine only stays that way when it is continuously re-oiled.
But that is a topic for a future blog …. Coherent Solutions is a software product development and consulting company that solves customer business problems by bringing together global expertise, innovation, and creativity. Why is ETL testing so important? The difference is this: Database testing compares data from source to target tables. Data warehouse or ETL testing traces the accuracy of information throughout the data warehouse.
Diagram 1 Looking at Diagram 1 above , you can see that data has to pass through a variety of processes from source to BI reporting. The transfer points where data can be lost or transferred incorrectly are in the ETL process: From source to staging area From staging area to the data warehouse From the data warehouse to data marts It is vital that we test at all of these points. These are: Data completeness. Verifying that all the expected data is loaded into the target from the source.
Data transformation. Verifying that data is transformed correctly according to business requirements and S ource T o T arget mapping documents.
Data quality. Verifying that invalid data has been corrected or eliminated in accordance with requirements. Performance and scalability. Verifying that the system is scalable and can sustain further growth, handling new data and subsequent queries up to acceptable performance limits. Integration testing.
Verifying that the application fits into the overall architecture without violating the integrity of the system. ETL testing ensures that the transfer of data from heterogeneous sources to the central data warehouse occurs with strict adherence to transformation rules and is in compliance with all validity checks.
It differs from data reconciliation used in database testing in that ETL testing is applied to data warehouse systems and used to obtain relevant information for analytics and business intelligence. Effective ETL testing detects problems with the source data early on—before it is loaded to the data repository — as well as inconsistencies or ambiguities in business rules intended to guide data transformation and integration.
The process can be broken down into eight stages. ETL testing fits into four general categories: new system testing data obtained from varied sources , migration testing data transferred from source systems to data warehouse , change testing new data added to data warehouse , and report testing validate data, make calculations. Testing during the ETL process can also include user acceptance testing, GUI testing, and application migration tests to ensure the ETL architecture performs well on other platforms.
Incremental ETL tests can verify that new records and updates are processed as expected. Identifying challenges early in the ETL process can prevent bottlenecks and costly delays. Creating a source-to-target mapping document and establishing clear business requirements from the start is essential.
0コメント