ETL Testing: Importance, Process, and ETL Testing Tools

What is ETL testing and why do we need it?

  • invalid values in source databases that result in missing data at a destination
  • dirty data that doesn’t conform to data mapping rules
  • nonstandard formats and inconsistent formats between source and target databases
  • input/output bugs when invalid values are accepted and valid ones are rejected
  • system performance bugs when multiple users or high data volumes are not supported, and so on.

ETL test preparation

  • what data elements will be integrated, their sources, types, destinations, and formats
  • what transformation rules will be used for transforming and cleaning the data
  • how and where the data will be loaded
  1. Check that all expect data is loaded into target database.
  2. Compare the number of records between source and target tables.
  3. Check if there are any rejected tables.
  4. Check that the data is displayed in full in target database.
  5. Check boundary value analysis.
  6. Compare unique values of key fields between source and target tables.

ETL test types

  • data quality and completeness, metadata
  • data transformation process compliance
  • performance and integration testing
Testing operations within the ETL workflow

Data quality and completeness

Data transformation success

ETL performance and integration testing

  • if data loads and queries are executed within the expected timeframe
  • if the maximum data volume is loaded within the expected timeframe
  • load times for different data volumes.
  1. Developers start by estimating the expected data volume in all sources for the next few years.
  2. They then generate the expected volume of data either by scrubbing the production data or using data generation tools.
  3. They load test data and execute the process.
  4. Developers then review the run times for every individual tasks and execute task dependencies to see how they run in parallel.
  5. They review task load times to identify bottlenecks.

Automated ETL testing and testing tools

The ETL workflow and PowerCenter’s tools used at each stage
iCEDQ features demo

Final tips and best practices

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
AltexSoft Inc

AltexSoft Inc

Being a Technology & Solution Consulting company, AltexSoft co-builds technology products to help companies accelerate growth.