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?