Data Migration/Transformation testing tips

Data migration/transformation projects are actual in s/w industry especially because systems mature, systems integration, needs in transforming data from legacy systems, for data improvement, consolidation, separation and purifying tasks. Some data migration activities may be so complex that require involvemnet of significant resources, skilled professionals and special tools. It can be truly noticed that data transformation itself in modern projects is sophisticated and challengable solution requiring individual approach and process modeling.

Data migration testing runs as grey or even white-box testing at most cases. Simple verification against on presentatoin layer can't gurantee good coverage, discovering hidden defects and this testing itself is not effective as requiring intensive data verification without deep inside research.

Here are my tips for testers who run or are going to test data migration:

- learn purpose and business model of project and Company to realize migration target

- study transformation schemas and scripts(XSD, SQL scripts)

- study development algorithms which operate over original data to produce transformed one. Notice you have to correspond the algorithms with specifications and requirements on migration process

- study database schemas (all from original to final through intermidiate states)

- develop and check your own scripts (SQL, XML, XLS, XSLT) to retrieve data for testing

- design sufficient and efficient data sets for testing data validity and integrity

- store your scripts and data sets under version control and link with versions of AUT

- run tests and store actual outputs in test logs.

- Also consider saving test logs as database records in testing DB with linking on defect entries. This approach will allow to revisit test run results as soon as you need in structured view

- Keep your knowledge about everything in product changes up to date

- Check data transformation in all points where it's being transformed in a whole product (DB, presentation, services, batch jobs, intermidiate mediums, etc)

- Make sure and double check correspondence and compatibility of original data types and transformed types for each entity, field and macro-data. This is a part of getting familiar with database architecture and schemas

- Run test frequenly against various data sets representing various combinations and tricky moments (like using special characters, sontrol symbols, code snippets, too long strings, invalid types and mixed content; strings that potentially can be truncated, encoded or echoed)

- More CREATIVITY and Hacking! You should BREAK IT!

For those who need to continue learning this topic, take a look at Wikipedia's related pages and this article about Data warehouse testing

http://www.developeriq.in/articles/2010/mar/10/data-warehouse-testing/