ETL Validations in Pentaho
We present a framework for validation. In our case very focused to validate that ETL load has worked well.
The purpose and use (desired) for this STValidations (free download for previous link), is to automate the validation routine and sometimes we fail to do just that. For routine.
Its use is quite simple and are in a version 0.1, so it can evolve much and that we have few use cases how to make sure their robustness and to cover all needs. Hope this code from Stratebi helps you.
ETL is a process that reads from a table a list of queries to be executed and the expected result. After that, run the query and compares it with the expected result if the result is correct is a log of executions correct and if the result is not expected is a log of botched executions. At the end sends an email with the two records to the user to decide so you can review the implementation.
Here you can see the aspect of work in general:
The purpose and use (desired) for this STValidations (free download for previous link), is to automate the validation routine and sometimes we fail to do just that. For routine.
Its use is quite simple and are in a version 0.1, so it can evolve much and that we have few use cases how to make sure their robustness and to cover all needs. Hope this code from Stratebi helps you.
ETL is a process that reads from a table a list of queries to be executed and the expected result. After that, run the query and compares it with the expected result if the result is correct is a log of executions correct and if the result is not expected is a log of botched executions. At the end sends an email with the two records to the user to decide so you can review the implementation.
Here you can see the aspect of work in general:
And the detail of the validation:
Simple ... powerful and versatile. From simple queries like "select count (*) from table" to validate that you have records to complex queries for comparison of values in different tables. In fact, if what I see is data in a table so I think that most of the validations can be performed using this method.
You can follow instructions in this video tutorial:
You can check more videos in our Youtube Channel
The process, in step settings, read the configuration data from configuration.properties, check that there is a validation table and if not creates and inserts a validation sample. Then, the process reads all existing queries in the table, runs and compare the result with the expected result.
The lookup table looks like and filled manually (we are at version 0.1, remember .....):
You can follow instructions in this video tutorial:
You can check more videos in our Youtube Channel
The process, in step settings, read the configuration data from configuration.properties, check that there is a validation table and if not creates and inserts a validation sample. Then, the process reads all existing queries in the table, runs and compare the result with the expected result.
The lookup table looks like and filled manually (we are at version 0.1, remember .....):
We did that because we had some problems of inconsistency in some projects that made clear that the loads must be validated and is a tedious and repetitive work.
- We must always validate charges. We have detected errors due to inconsistent or unexpected data input formats. And that can not be controlled unless the charges are validated.
- Loads must be validated especially after making a change in the ETL and it is pretty boring, perform the same query validation.
- We must make a series of routine consultations to ensure that the data is equal to the source.
- If you can automate ... I'd rather be doing other things.
Strengths and weaknesses:
- It is a version 0.1 we have tested in a couple of clients and so far so good. But we know that, for example, still can not compare data from different sources faith.
- Queries can become heavy and this is a potentially VERY expensive in terms of resource consumption. That's why ETL is a process that can be run independently attached to the ETL process or in another moment of time with little load. We do it well. The process does not run right after the ETL but at 6:00 a.m. how a separate process.
- Do not rely on the BI server. Simple and useful.
Well, I think it can be very useful in projects and we hope you find it useful too!!
By the way ... this is the error log to send:
