I am working on healthcare project. I need to verify the data present in staging tables, DWH tables and target tables. I am checking manually for few records but my management asking to look for a solution which solves to verify the data completeness for thousands or millions of records without a paid tool. Is it possible to check million records data...???
Given you're talking about "millions" it makes sense to use a load testing tool which can do multiple actions in parallel. If you don't have a better candidate yet you can consider Apache JMeter which provides:
- CSV Data Set Config - so you can read "expected" data from file(s) in your local file system
- JDBC Request sampler - to execute database queries
- Response Assertion - to apply custom pass/fail criteria, i.e. ensure that the data from CSV is in the DB
- And perform it all with multiple threads, the number can be configured in Thread Group
It depends how complex the transformations are, if you can query both the input and the target tables than maybe a simple record count, a column sum and checking first, last and a row in the middle could be sufficient to tackle most risks.
Checking Data Completeness is done to verify that the data in the target system is as per expectation after loading.
The common tests that can be performed for this are as follows −
Checking Aggregate functions (sum, max, min, count),
Checking and validating the counts and the actual data between the source and the target for columns without transformations or with simple transformations.
- Count checks if you have all the records
- Summing a column checks of all the values total to the same value on both sides
- Checking a couple of rows checks that the data is not skewed
These checks are really fast even on large datasets.
I think it is a risk-based question, you could develop a tool that checks all the transformations and all the rows, but this would just duplicate the development efforts. It is a Health care project, so if people could die from wrong data, maybe it is worth investing more, if not maybe just do the simple checks.
The tutorialspoint website has a lot of testing ideas for ETL, it has helped me a lot in the past.