Continuous data validation ensures that the same metric returns the same number no matter which system it’s queried from — source, warehouse, or report. This post covers two complementary testing approaches: Glassbox validation, which compares data end-to-end using knowledge of the underlying dataflows, and Blackbox validation, which checks data quality using predefined rules or AI-based anomaly detection without needing to understand the pipeline internals. It also introduces the seven Data Quality dimensions and explains how Data Observability catches issues that fixed rules can’t predict in advance.
Key Takeaways:
- Validate from the whole to the part. Metrics and totals should be reconciled before drilling into detailed data — for example, confirming revenue totals match across Finance, CRM, and Reporting before checking line-level records.
- Glassbox Data Validation Testing requires understanding the reports, dataflows, and dependencies feeding them, then writing tests that compare source, target, and report data directly — allowing for expected variance (e.g., a 0.5% tolerance) where near-real-time ETL causes minor timing differences.
- Blackbox Data Validation Testing checks data quality without needing to understand the underlying dataflows, using two approaches: predefined Data Quality Rules and AI-based Data Observability.
- Data Quality Rules (like “Not Null,” duplicate checks, referential integrity checks, and delta rules) validate data against fixed, predefined conditions and map to seven Data Quality dimensions: Completeness, Validity, Unicity, Consistency, Conformity, Accuracy, and Timeliness.
At a recent TDWI virtual summit on “Data Integration and Data Quality”, I attended a session titled “Continuous Data Validation: Five Best Practices” by Andrew Cardno.
In this session, Andrew Cardno, one of the adjunct faculty at TDWI talked about the importance of validating data from the whole to the part, which means that the metrics or total should be validated before reconciling the detailed data or drill-downs. For example, revenue totals by product type should be the same in Finance, CRM, and Reporting systems.
Attending this talk reminded me of a Data Warehouse project I worked on at one of the federal agencies. The source system was a Case Management system with a Data Warehouse for reporting. We noticed that one of the key metrics “Number of Cases by Case Type” yielded different results when queried on the source database, the data warehouse, and the reports. Such discrepancies undermine the trust in the reports and the underlying data. The reason for the mismatch can be an unwanted filter or wrong join or error during the ETL process.
When it comes to the federal agency this report is sent to congress and they have a congressional mandate to ensure that the numbers are correct. For other industries such as Healthcare and Financial, compliance requirements require the data to be consistent across multiple systems in the enterprise. It is essential to reconcile the metrics and the underlying data across various systems in the enterprise.
Andrew talks about two primary methods for performing Data Validation testing techniques to help instill trust in the data and analytics.
- Glassbox Data Validation Testing
- Blackbox Data Validation Testing
I will go over these Data Validation testing techniques in more detail below and explain how the Datagaps DataOps suite can help automate Data Validation testing.
Glassbox data validation is in a way similar to the Whitebox testing in software testing. Whitebox testing in software engineering requires an understanding of the internal structure of the software. Glassbox data validation also requires the analyst to have a good understanding of the reports, the dataflows feeding the reports, and their dependencies. Based on this understanding, data validation tests can be written for comparing the data from the source to the target or reports.
Going back to the above example of “Number of Cases by Case Type” in a simplified version of the dataflow with a source (case management system), target (data warehouse) and reporting system, the numbers might look like below:
It is evident from the numbers that data between the Data Warehouse and the Source systems do not match at all. This is quite often the case because the transaction database is part of a live application where the data can get modified at any given time. Unless the ETL process to refresh the data warehouse is near real-time, the data in Data Warehouse will differ slightly from the transactional data expected. What we need to do is compare the numbers between the transactional data and data warehouse allowing for minor differences or variations. Assuming a 0.5% variance in numbers can still be considered as a pass, the same data comparison will result in the failures as shown below.
Datagaps DataOps suite and BI Validator can be used to automate this type of test. BI Validator has built-in integration to read the report data from popular BI tools such as Power BI, Tableau, etc. The Data Compare test case in BI Validator provides an easy-to-use wizard for reading the report data and comparing it with the SQL query output. Please reach out to us or download a trial to learn more information on BI Validator and DataOps suite.
Blackbox Data Validation like the name indicates is similar to Blackbox testing in the world of application testing. Instead of trying to understand the dataflows and how the data got to the target or reports, BlackBox testing is about validating the data using a set of data quality rules. For the previous example in a case management system, this might mean that validating the data in the data warehouse or the reports without comparing it with the source system. There are two broad categories of data validation testing that can be performed for this type of testing as listed below.
Data Quality Rules are SQL queries that validate the datasets for a predefined set of conditions to identify good or bad records. Data Observability is a new term that is being referred to as the usage of anomaly detection techniques for data validation.
- Data Quality testing using predefined Data Quality Rules
- Data Observability using AI-based anomaly detection
Data Validation using Data Quality Rules
Data Quality rules are data quality standards that can be validated using SQL queries in most cases. The simplest of all such rules is the “Not Null” rule. This rule checks if a particular column in a dataset has values for all the records. This rule can be categorized into a Data Quality dimension called “Completeness”. Some of the other types of rules include Duplicate checks, Referential Integrity checks, Delta rules to compare previous values to current values, and reference data checks. Data Quality and Data Governance go hand-in-hand.
What are Data Quality Dimensions?
Data Quality dimensions are a means to categorize Data Quality rules and measure data quality. Data Quality score is a measure of good data in the dataset that the rule is validating. There are seven Data Quality dimensions as listed below:
|
|
|
Read more about Data Quality Dimensions and Data Quality Score in this article:
DataOps Data Quality makes it easy to define Data Quality rules and assess the quality of your datasets. It automatically computes a Data Quality score based on the results and displays a trend of Data Quality scores in a dashboard.
Data Observability and Anomaly Detection
While Data Quality Rules measure the quality of the data based on predefined criteria, Data Observability uses AI to identify anomalies in data based on historical data. Take, for example, the daily COVID cases for each city and the total number of COVID cases reported so far. When a data analyst looks at this data on a daily basis, it is easy to make the following observations:
- The number of cases on the weekend is generally less than on weekdays
- Each city has its own wave of increasing or decreasing number of cases on a daily basis
- The total number of cases has to be greater than or equal to the previous day’s number
It is difficult to predefine the range for the number of daily cases in a given city and create Data Quality rule. This is where Data Observability comes into play. Using AI-based algorithms or statistical-based methods such as Standard Deviation or IQR, the number of daily COVID cases can be automatically predicted. If the number of cases does not fall in the predicted range, the number can be considered an anomaly.
As you ingest incremental data into your Data Lake or Data Warehouse, it is important to use a combination of Data Quality rules and Data Observability to determine the quality of the data before it is processed further. It is easier and cheaper to fix the data quality issues while the data is getting ingested compared to fixing it after the data has been ingested into the data warehouse and data marts.
DataOps Data Quality can be used to automatically detect anomalies in the data being ingested. It uses apache spark and an AI-based time-series prediction algorithm to come with the prediction that takes into account the seasonality of the data.
A recent survey by TDWI found that 66% of organizations are looking for ways to improve data quality and trust. Using the aforementioned Glassbox data validation testing and Blackbox data validation testing can ensure that the data is consistent across systems and data quality is validated accurately. Datagaps DataOps suite is the most comprehensive data validation testing tool that can be used for Data Quality testing, Data Observability, and Data Reconciliation testing.
Try Datagaps ETL Validator Free for 14 days
Rated as one of the best Data Validation Tools by its customers and partners.
Conclusion
Continuous data validation isn’t a one-time checkpoint — it’s an ongoing discipline that combines Glassbox testing (comparing data across source, target, and reports with full knowledge of the dataflow) with Blackbox testing (validating data quality through rules and AI-driven observability, without needing that dataflow knowledge). Used together, these methods catch both the mismatches you can predict and the anomalies you can’t, keeping the same metric consistent no matter where in the organization it’s queried from.
Frequently Asked Questions
1) What does “validating from the whole to the part” mean?
It means validating high-level metrics or totals before reconciling the detailed data or drill-downs beneath them — for example, confirming that revenue totals by product type match across Finance, CRM, and Reporting systems before checking individual transaction records.
2) What is Glassbox Data Validation Testing?
Glassbox validation requires understanding the reports, the dataflows feeding them, and their dependencies. Based on that understanding, tests are written to compare data from source to target or to reports — similar to Whitebox testing in software engineering.
3) What is Blackbox Data Validation Testing?
Blackbox validation checks data using a set of data quality rules, without needing to understand or trace the underlying dataflows — similar to Blackbox testing in application testing.
4) Why would data differ between a transactional source system and a data warehouse?
The transactional database belongs to a live application where data can change at any time, while the data warehouse is refreshed on an ETL schedule. Unless that refresh is near real-time, the warehouse’s data will differ slightly from the source, which is why comparisons should allow for a small variance (e.g., 0.5%) rather than expecting an exact match.
5) What are Data Quality Rules?
Data Quality Rules are largely SQL-based checks that validate a dataset against predefined conditions to identify good or bad records. Common examples include “Not Null” checks, duplicate checks, referential integrity checks, delta rules comparing previous values to current ones, and reference data checks.
Rajesh Kumar A
Digital Marketing Manager, Datagaps
Digital Marketing Manager at Datagaps. Drives data-driven growth through content, performance campaigns, and marketing technology.
S P S Murthy Akella
Director, Technology Strategy, Datagaps
Director of Technology Strategy at Datagaps. Business solutions architect and Certified Scrum Master in data engineering, responsible AI, and ML across BFSI, telecom, aviation, and energy.
