Automating tests for ETL flow

Question

I created am ETL that parse various files, transform file's data and then push the lines into a DB, until now I did a manual tests and check that all the values parsed correctly and all the lines (that needed) are insert into DB, of course it's become frustrating and error prone and I want to automate those tests.

I thought to create a "tests files" with "foretold" data and then, parse them and try to valid(verify) all the values on the DB, but I'm not sure if I should to test the DB's values or the "transformers" functions in my code.

For example:

I have 3 types of CSV files that contain:

1. File 1
   • Birth date - DD/MM/YYYY (23/04/1992)
   • Height - cm (173)
   • Weight - Kg (70)
2. File 2
   • Birth date - MM/DD/YYYY (04/23/1992)
   • Height - inch (68.11)
   • Weight - pounds (154.324)
3. File 3
   • Birth date - YYYY-MM-DD (1992-04-23)
   • Height - cm (173)
   • Weight - Kg (70)

So the code extracts the lines from each file and then (according to file's mapping) from column value and creates an instance of the appropriate transformer, the transform values insert into DB, in my example, after parsing those files in DB will have 3 rows with same values (23/04/1992, 173, 70)

Maybe there is a more correct way to do the testing?

Answer 1

What you're describing is an end-to-end test. That is one way of validating your system, and it does have some benefits - it ensures that every part of the system is working.

The downside is that it hits every part of your system, which can make it slow to set up, and it will tend to be brittle (changing one thing can break numerous other parts).

A common approach, and what I would recommend, is to break out the transformation functions and test those explicitly with unit tests. Doing that will require you to write your program in a way that permits you to invoke the transformation code without the file-reading or database-access code. This has some major benefits: if, in a few years, someone decides that they want these transformations to occur within another application, they can reuse just the transformation code (without having to figure out how to break that out separately from the rest).

So, you'll end up with a bunch of unit tests, which test small specific portions of the code - for example, "an input of 3 records should result in 3 database insertions", "an input of 3 records, one of which already exists in the database, should result in only 2 database insertions", "invoking the transformation on a record that's missing a required field should throw an exception", etc.

In order to set this up, you'll likely have to make the file reader and database writer into interfaces. You then implement the version that works "for real", as well as setting up a version that pretends to be doing work but actually is just confirming that its expected code was called. You don't have to write those fake classes out explicitly, most languages have libraries that will handle this process (called "mocking") for you.

So, once you've got it all set up, you'll have a bunch of unit tests that confirm specific pieces of functionality within your transformation code.

There's a lot more that goes into writing testable software well, but this should at least get you thinking in the right direction. If you want to edit your question to include what language the software is in, I can point you to some specific tools for your use case. Without knowing your precise use case, I can at least recommend that you search for "writing testable software" and read up a bit.

Answer 2

I'm not sure if I should test the DB's values or the "transformers" functions in my code

Do both.

We don't know your design but, what we have here are three different concerns that -in theory- should be possible for you to test independently from one another.

I assume that -at least- you have implemented the unit tests for every E(xtract), T(ransform) and L(oad) as the developments as processed. These are your very first automated tests.

Another sort of tests I consider a must are integration tests.

At this point, by integration, I mean "the assembly" of the components. How they all behave working together without the DB or the CSV files.

Inputs and outputs can be performed in memory. For example, using arrays as inputs and collections as outputs. It will depend pretty much on your design. For example, if you have implemented the repository pattern, should be easy for you to mock up load and save methods. Same for the CSV readers, basically because these files are also a data source which access can be abstracted too.

These are your second automated tests.

When I say automated, I meant that these tests are executed automatically during the building or packaging phase. I'm not familiar with PHP stack technology, but I assume that PHP already has several libs and tools addressed to this purpose.

As for the end-to-end tests, If you can't afford to deploy an in-memory database, I don't see why you should not do tests against the real DB. I would not consider this to be testing DB values. Just make sure that you can restore the state of the DB at the end of the test plan (for example, deleting the rows).

How to automate end-to-end tests is a fairly broad question. You will find several tools addressed to this purpose. I especially like those that allow me to design use cases declaratively. For example by scripts. I found them to be easy to integrate with CI.

---

Here a visual representation of the test plan suggested above. Each colour belongs to a different set of tests.