Database Migration Testing | ETL testing with Interview Questions and Answers

Hello and welcome to this video on database migration testing. Data migration testing is a back end testing. In this testing tutorial, we will learn the
basics of data migration, data migration testing scope, testing approach, what to put in a
database migration testing bug report, database testing challenges and possible solutions. First, what is Database Migration? Database migration testing means the movement
of data from an old database or databases to a new database. We refer to the old database as the source
database or legacy database. We call the new database the target database
or the destination database. We use an automated ETL (Extract Transform Load)
process to do the Db migration. The ETL tool also has business rules to increase
data quality. Why do we do database migration testing? The reasons include checking Is each business
rule implemented correctly in the ETL tool? Are all live entities (meaning not expired)
for example customer records and order records migrated to the correct tables in the target
database? Each live entity should be migrated exactly
once. Is each attribute of each entity in the source
database migrated to the target database? Does the db migration process work quickly? Now, let us see the database migration testing
approach. It consists of the following activities. Design the validation tests: The validation
tests include SQL queries to test database migration. We need these validation queries to cover
the db migration scope. Also, we need queries for both the source
and target databases. We should order our queries for example we
should test if all the Customer records are migrated and only then test if all Order records
are migrated. Because Order records depend on Customer records. Set the test environment: We test the database
migration in a test environment. Our test environment should contain the source
database copy, the ETL tool and as needed, a blank or partially populated target database. Then we run the database migration process
under test. Then we run our validation tests
Lastly, we analyze test results and report the defects. Now, let us see the data that we should include
in every database migration testing defect report. The entity that failed, total number of rows
or columns failed, the validation test query that we used, database error number and description,
if any, the User account that we used to log into the source and target databases, the
date and time of our db migration run, and the date and time of validation test query
run. Now let us see some potential problems and
solutions in database migration testing or ETL testing. The first challenge is that the source data
may change during the test. We should backup the source and target database. This should help if we need to re-start the
data migration. The second problem is that the source data
is partially corrupt for example some data is incomplete or contains junk values. The solution is that the ETL tool should handle
such corrupt data. Our validation tests should confirm the ETL’s
handling of corrupt data. Another problem is that the source to target
mapping across the tables and fields may change. The solution is that we should design validation
queries for the unchanged or stable mappings first. We should design validation queries for the
remaining mappings once they are finalized. Another potential problem is that the data
migration fails but partially. This can happen if part of the source data
is accepted and the other part is rejected. The solution is that we need to find the failing
data. We can do this if we have modular validation
queries which we can run one by one. The last potential challenge is the data migration
takes a lot of time. This can happen if the source data is huge
or the ETL process is complex. The solution is that we can include our validation
queries in the ETL tool so they run automatically. Now let us see if you know about database
migration testing or you should view this video again. In database migration testing, what is the
final or resulting database called? It is called the target database or destination
database. What is ETL? ETL means Extract-Transform-Load. ETL is the process that queries the source
data, checks this data according to business rules, transforms the data if needed and loads
the data into the target database. What do we test in database migration (in
other words what is ETL testing scope)? We test if each business rule is implemented
in ETL correctly. Are all live source entities migrated to the
target database? Are all attributes of every live entity migrated
to the target database? Is the ETL reasonably fast? What data is needed in an ETL testing bug
report? The important data includes the name of the
failed entity for example OrderDetails, the number of rows or columns that failed, validation query
used and the error number and description. Database migration testing is a type of database
testing. In this ETL testing tutorial for beginners,
we learnt what is db migration, what is the database testing scope to test if it was a
successful data migration, database testing approach to test data migration and what to
put in every ETL testing bug report. Then we learnt about some data testing problems
and solutions. And questions and answers to test our knowledge of database migration testing. That’s all in this video. Thank you and see you in my other videos.

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *