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.
At a recent event, one of the prospects at the attendees came over to our booth and asked me to demonstrate a use case that was kind of interesting. I got a similar request from another prospect recently and thought that it might be a good idea to blog and show how it can be done using ETL Validator.
Problem: There is a source table and a target table. The attendee wanted to find the difference in a number field between two tables. In addition, he was also interested in specifying an acceptable variance and define 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.
Step 1: To simulate the source, we created a simple table and inserted few records.
- Created table table_a (Segments Varchar2(20), Counts number);
- Data Insertion
- insert into table_a (books, quantity) values (’Tier 1′, 10);
- insert into table_a (books, quantity) values (’Tier 2′, 15);
- insert into table_a (books, quantity) values (’Tier 3′, 12);
Step 2: To simulate the target:
- Created a table table_b (Segments Varchar2(20), Counts number);
- Inserted few sample records
- insert into table_b (books, quantity) values (’Tier 1′, 10);
- insert into table_b (books, quantity) values (’Tier 2′, 25);
- insert into table_b (books, quantity) values (’Tier 3′, 30);
Ok, now the sample data set was ready, it was time to showcase the Component Test Case in ETL Validator:
Step 3: I launched ETL Validator
- Opened up ETL Validator and started with the Component Test Case since, one of my favorites.
Step 4: I dragged and Dropped to create the DB components in the test case wizard.
- Had to create DB component to reflect the Source. It was a wrapper on a simple query (select * from table_a)
- Noted the name of the “Result Table Name” in properties for that component. {TCOMP_40531_40508}
- Had to create DB component to reflect the Target. It was a wrapper on a simple query (select * from table_b)
- Noted the name of the “Result Table Name” in properties. {TCOMP_40531_40509}
Step 5: Had to Drag and Drop another database component to capture the differences.
- select a.books, (a.quantity-b.quantity) as Quantity from T_COMP_40531_41058 a, T_COMP_40531_41059 b where a.books=b.books
- Now, we were ready to apply the rules on top of this component.
Step 6: Drag and drop a “Data Rules Component” to define rules on the variance. This is something unique to ETLV.
- Double click on the entity and define a “Data Rule” on the Quantity column. The rule was something like “Quantity is greater than” 10.
Step 7: Then, I just got into the run mode and tapped on “Run” at the bottom of the screen.
The results were displayed and clearly showed the records that did not match. With very minimal steps, we could demonstrate how to achieve a relatively complex scenario within a single test case in ETL Validator; pretty cool.
Frequently Asked Questions: Variance Testing in ETL Validator
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 like “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.