I have an ETL scenario where my source is Database and Target is flat file. How best can I test it to make bug free?

Question

The flat file has millions of records and the source query is joining at least 10 tables.

The Testing I have done is:

1. Structure validation of the file. Like name & number of fields, delimiter, naming convention, Header or Trailer records.
2. Check for duplicate records.
3. Select one row from the target file and for that record alone, run the source query and then compare the Source output and the target record in the flat file manually comparing each field one at a time.
4. Check for data truncation.

The concerns I have is:

1. I can't employ minus approach between source and target because target is in flat file.
2. I tried loading data into excel and compare but excel has record limitation and hence not all data can be loaded.
3. In the where clause of the source query outer join is used. How to ensure that data is loaded accordingly.
4. Is there a way to automate this testing process.
5. Though I have written test cases dedicating one test case for fields sourced from one table. Is there any better approach. Pls provide some samples.

Thanks a bunch for reading this through and waiting for the suggestions.

Answer 1

I would split your question into two parts:

1. What needs to be tested?

You have already covered some critical scenarios. Besides these following should also be covered-:

-Ensure that all expected data is loaded into target table.

-Compare record counts between source and target.

-Check for any rejected records

-Null, non-unique or out of range data

-Verify default values

-Verify Field Boundaries, range/distribution of value

-Verify unique keys, primary keys

-Verify any derivations & Calculation in the tables

-Verify the data Load technique (Incremental/ Full Refresh)

2. How can this be tested?

In my opinion, there are three ways to test this scenario-:

i) Using Third Party utility. You can use programming languages like Python/ Java for loading the data in some test database and doing the data comparison.

ii) Manual comparison -: For some subset of data You can stare and compare the data between source & Target Databases. Or You can download samples of data from Source & Target in Excel and use Macros for data Comparison & Validation. But as this involves manual steps, this will be time consuming & will not give good data coverage.

iii) Using ETL test Automation tools – You can check some tools like Query Surge , BI Validator which can do file to DB comparison.

Answer 2

How complicated is the ETL logic? Are you bringing over every record? Do you transform the data? I would create targeted test data to test the logic itself.

Once you know what you know exactly which kind of data is brought over and exactly how the data is transformed you can absolutely automate it. Start with an empty database, insert to source for your first test case, run the ETL and then compare the file to what you expected it to be.

Once you feel good about the basic functionality on a small scale you can expand your testing to larger datasets.

Answer 3

The best way to ensure the completeness and correctness of data is by testing it in SQL. Can you try creating a database on your machine and use import\export wizard in SQL server and import the source and target file in two tables? I think then you will be able to validate the data transformation and ETL logic

I am also curious to know if there is any intermediate database where transformed data is kept in your case.

The above suggested approach should further enable to perform following tests:

• Record count validation
• Validation of correctness of data
• Validation of transformation logic
• Validation of data for duplication