1

I receive a variety of flat files that need to be transformed and aggregated in several stages of a custom ETL process before loading it into a SQL Server database.

After each stage, I'd like to verify the data in several ways, and I'm looking into existing technologies that can help.

Upon receiving the data, it needs to be validated for things such as truncated data, date formatting and generally ensuring the data is ready for transformation.

After the data is cleaned in this way, I want to verify the data. This would consist of comparing values such as row counts, % nulls, average values etc. to previous loads, or predefined values. If the verification fails, the developer should be alerted.

tSQLt, the database unit testing framework, has several assertions that can be used to do what I want. It's easy to set up and has decent documentation. This is the nearest tool I can see, but it's a long way from what it's designed for.

The alternative is to create my own tool, but I want to know - does something like this already exist?

1

After a bit of searching I found a commercial solution which I think would solve the problem: QuerySurge. There are a couple of similar tools like this (ETL validator), though it claims to be unique software.

It works by:

  • Using set comparison between 2 queries, raising errors if they do not match. This could be row counts before/after transformations, or simply checking a result returns nothing.

  • Queries can be performed against any JDBC compliant data source using ANSI SQL and any connection specific SQL. The results are stored on a separate server using a MySql backend and you can choose to either host this yourself or use their servers.

  • It permits command line usage and therefore supports continuous integration tools.

  • A nice feature is the grouping of tests (test suites), although it is not clear how the results of a group would affect an overall test.

  • The built-in reporting tools also look nice.

That's the majority of what I gleaned from the website. I haven't downloaded the trial as the software itself is outside of my price range.

The tool is not complicated in principal, and we'll be developing our own framework to cope.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.