When comparing a number field between a source and target table, exact matches aren’t always realistic — sometimes a difference within an acceptable range should still count as a pass. This post walks through a hands-on ETL Validator example: building a Component Test Case with DB components for the source and target tables, calculating the difference between them, then applying a Data Rules Component to define the variance threshold. Records within the acceptable range pass; records outside it fail.
Key Takeaways
- Exact matches aren’t always the right test — when comparing numeric fields across systems, a defined acceptable variance can be more realistic than requiring an exact match.
- Component Test Case handles the full workflow — source and target tables are wrapped as separate DB components, each producing a named result table for later steps.
- A third DB component calculates the actual difference — a simple query joins the two result tables and computes the delta between matching records.
- The Data Rules Component applies the pass/fail logic — a rule (e.g. “Quantity is greater than 10”) is defined on the difference column, and running the test case immediately shows which records fall outside the acceptable variance.
How This Use Case Came Up
At a recent event, one prospect at our booth asked for a demo of a use case that turned out to be genuinely interesting: testing for acceptable variance rather than an exact match. After a similar request from another prospect, it seemed worth writing up how to do this using ETL Validator.
Problem: This is a common database testing scenario: there is a source table and a target table, and the attendee wanted to find the difference in a number field between them. In addition, he was also interested in specifying an acceptable variance and defining a rule on that. If the difference is within the limits, then he wanted the test case to be marked as success. If the difference exceeds the variance, then, he wanted the test case to be marked as failure. In just few minutes, we were able to demonstrate this use case using 3.4 version of ETL Validator.
Solution:
| Step | What Happens |
|---|---|
| 1–2 | Create sample source and target tables and populate them with a few test records. |
| 3 | Launch ETL Validator and create a new Component Test Case. |
| 4 | Drag and drop DB components to wrap the source and target queries. |
| 5 | Add another DB component to calculate the difference between matching records. |
| 6 | Add a Data Rules component to define the acceptable variance threshold. |
| 7 | Run the test case and review which records pass or fail the defined variance rule. |
Conclusion
Not every data comparison should demand a perfect match — when source and target systems have small, expected differences from timing, rounding, or system latency, testing for an acceptable variance is often the more realistic check. ETL Validator handles this without custom scripting: wrap the source and target as DB components, calculate the difference with a third component, then apply a Data Rules Component to define exactly where the pass/fail line sits. What looked like a complex one-off request at a conference booth turned out to be a straightforward, repeatable pattern — proof that even edge-case testing scenarios usually fit within a standard component-based workflow.
Frequently Asked Questions
1) Why would you allow variance instead of requiring an exact match in database testing?
Real-world data comparisons—such as between a live transactional system and a data warehouse—often have small, expected differences due to timing or rounding, so testing for an acceptable variance is more realistic than demanding an exact match.
2) How do you set up a variance test in ETL Validator?
Using a Component Test Case, create DB components wrapping the source and target table queries, add another DB component that calculates the difference between them, then apply a Data Rules Component to define the acceptable variance threshold.
3) How is the acceptable variance rule defined in ETL Validator?
The rule is applied directly on the calculated difference column—for example, defining a condition such as “Quantity is greater than 10” to flag differences that exceed the acceptable threshold.
4) What happens when a record exceeds the defined variance?
When the test case is run, ETL Validator displays the results and clearly marks which records exceeded the acceptable variance, making it easy to identify failures without manually scanning the data.
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.
