A source-to-target ETL test compares what a pipeline loaded with what its mapping says it should load. Each mapping rule becomes a test case with an input row, an expected result, and a pass or fail outcome. This article works through one example: a customer table loaded with five transformation rules.
For the nine types of ETL testing and the full process, start with the Datagaps ETL testing guide and the ETL testing complete guide. The question here is narrower: what do you test, and what should the answer be?
Key Takeaways
- A source-to-target ETL test is a mapping rule, an input row, an expected result, and a check. Write the expected result from the rule, never from what the target happens to contain.
- This example loads a customer table through five rules and tests it with four rows covering blanks, NULLs, and negative values.
- ETL Validator drafts the comparison queries from the mapping with AI, through the Generate Dataflow wizard or Mapping Manager, and Data Compare, Data Rules, and Metadata Compare check the results.
- Data professionals report spending 40% of their time checking data quality (Wakefield Research, 2022).
How Is the Source-to-Target Mapping Set Up for This ETL Test?
Picture a pipeline that loads customer records from a source customer table into a customer dimension table. A source-to-target mapping is the specification for that load: for every target column, the source column that feeds it, the rule that transforms the value, and the data types on each side.
Figure 1 shows that mapping in the Mapping step of the Generate Dataflow wizard. The left side lists the 11 source columns with data types, the right side the 11 target columns, and the footer confirms 11 mapped and 0 unmapped. The checked box beside CustomerKey marks the unique key, which ETL Validator uses to match each source row to its target row. The source key is varchar and the target is int, so the load must convert it.
Six columns, including BirthDate, Gender, and YearlyIncome, move across unchanged. The other five carry a transformation rule, recorded in the mapping file as a business rule, where sc is the alias for the source table. In plain terms, the rules say:
- CustomerKey: convert the key to upper case, UPPER(sc.CustomerKey).
- FirstName: join the title, first name, middle name, and last name into one value, CONCAT(sc.Title, sc.FirstName, sc.MiddleName, sc.LastName).
- EmailAddress: turn a blank email into NULL, NULLIF(sc.EmailAddress, โ).
- TotalChildren: replace a negative count with NULL, CASE WHEN sc.TotalChildren < 0 THEN NULL ELSE sc.TotalChildren END.
- NumberChildrenAtHome: apply the same negative-value rule to its own column.
Each of these five rules is a test waiting to be written.
How Do You Write ETL Test Cases and Expected Results From a Mapping?
Start from the rule, not the target. Pick a few source rows that exercise each ruleโs edge cases. Then work out the expected target values by hand from the rule. If you derive them by looking at the loaded target, the test can only confirm what the pipeline already did.
Take one test row to see how this works. Row 1002 is a good choice because it has NULL name parts, a blank email, and a negative count, so most rules have something to do. The row is illustrative, and the integer columns are signed so the negative-value rule can fire. Figure 2 follows it through the five rules to its expected values.
Three more rows complete the test set:
- Row 1001 is a clean row. Expected: FirstName MsAnnaKRao, with the email and both counts unchanged.
- Row 1003 has a NULL email, a count of zero, and a negative home count. Expected: FirstName DrCarlaMDiaz, email NULL, TotalChildren 0, NumberChildrenAtHome NULL.
- Row 1004 is a second clean row. Expected: FirstName MrDevSPatel, with everything else unchanged.
The below table traces each transformed column to its source, its test rows, and its check.
| Target column | Comes from | Test rows that exercise it | ETL Validator check |
|---|---|---|---|
| CustomerKey | sc_CustomerKey (varchar) | All four rows | Data Compare on the unique key; Check Uniqueness |
| FirstName | Title, FirstName, MiddleName, LastName | All four rows; 1002 has NULL parts | Data Compare on the transformed query |
| EmailAddress | sc_EmailAddress | 1002 (blank), 1003 (NULL) | Data Rules; Data Compare |
| TotalChildren | sc_TotalChildren | 1002 (-1), 1003 (boundary at 0) | Data Rules; Data Compare |
| NumberChildrenAtHome | sc_NumberChildrenAtHome | 1003 (-2) | Data Rules; Data Compare |
| Six other columns | Same-named source columns | All four rows | Metadata Compare; Data Compare |
Two details matter. The CONCAT rule has no separator, so row 1002 yields BenLee, and a reviewer who wants a space should change the rule, not the test. NULL handling depends on the engine: SQL Serverโs CONCAT converts a NULL to an empty string (Microsoft Learn), and other engines may return NULL. State the engine next to the expected result.
The same rows give aggregate expectations: four rows in the target, two NULL emails (rows 1002 and 1003), one NULL TotalChildren, one NULL NumberChildrenAtHome, and four distinct keys.
How Does ETL Validator Automate the ETL Validation?
Figure 3 summarizes the path every rule follows. Two routes produce the queries: the Generate Dataflow wizard and Mapping Manager.
In the Generate Dataflow wizard, the user imports the mapping Excel file (up to 50 files or 50 MB), and the AI drafts a source query and a target query from the business rules (Figure 4). The wizard supports JDBC sources and targets and SELECT queries only, and a reviewer approves each query.
How Does Mapping Manager Turn Source-to-Target Mappings Into ETL Tests?
Mapping Manager is the managed route: a module in DataOps Suite where mappings live and become tests. It links a target table from a Data Model, holds the mapping for each column, drafts the rule queries with AI, and creates the dataflow. It needs ETL Validator access, a configured Data Model, and an AI provider.
In the Mapping section you define each target columnโs source by dragging fields, using AI-powered mapping, or importing a spreadsheet whose headers you align to the required fields. Each column has a Business Transformation, which says in plain language what the rule does, and a Technical Transformation, which says how it is built, so analysts and engineers review the same mapping (Figure 6).
Transformation Checks drafts the rule queries with AI. Figure 7 shows one checkโs source and target queries, with the AI Assistant beside them for refinements. Only SELECT queries are allowed.
Create Dataflows turns the checks into a dataflow. The wizard sets the environment, such as dev or QA, and the compare types: records only in A, only in B, mismatched rows, and column-wise mismatches (Figure 8). In the customer example, DimCustomer is the target table and its five rules become the transformations. Either route ends with a dataflow ready to run.
Which Checks Does the Generated Dataflow Run?
Whether it comes from the wizard or Mapping Manager, the dataflow runs four checks against the expected results from the test rows:
- Metadata Compare confirms matching types and lengths for the pass-through columns and the documented varchar to int change on CustomerKey.
- Data Profile and Metrics Compare confirm four rows, the NULL counts above, and four distinct keys.
- Data Compare on CustomerKey confirms that every transformed source value equals the target value in all four rows. Check Uniqueness confirms the key is unique across the full dataset, and a result below 100% flags duplicates.
- Data Rules confirm no empty strings in EmailAddress, no negative values in TotalChildren or NumberChildrenAtHome, and no duplicate keys.
When source and target store a value differently, such as a timestamp and a string, a Custom Expression normalizes both sides before comparison. The AI Assistant drafts it from a prompt such as convert to date, and Test shows values before and after casting (Figure 9).
What Do ETL Test Pass and Fail Results Look Like?
Run the dataflow and each check returns Pass or Fail. A pass means zero differences, zero rule violations, and matching aggregates. A fail points to the rule that broke. Three illustrative defects in this example show how:
- The load skips NULLIF: row 1002 loads an empty email. The Data Rules check flags the blank, and Data Compare reports a value difference on EmailAddress.
- The load drops the CASE logic: rows 1002 and 1003 load negative counts, the negative-value rule fails, and the NULL counts no longer match.
- A retried batch loads row 1004 twice: Check Uniqueness falls below 100%, the duplicate-key rule fails, and the row count is no longer four.
Figure 10 shows the result screens you would read, taken from sample runs on other data.
- Panel A: a Data Compare run where all 5,555 records matched. Its Mismatched Rows, Only in A, Only in B, and Duplicates tabs are where a skipped NULLIF, missing row, or doubled row would appear.
- Panel B: a Data Rules run with a Failed status and a Fail on one rule.
- Panel C: a Metrics Compare run that marks each metric Pass or Fail against its source value, target value, and threshold.
In a sandbox exercise on the same DimCustomer table, Data Compare checks a Power BI visual of Sum of Yearly Income by Total Children against SQL Server. Most aggregates match; the one for Total Children 2 does not. It compares a report with a table, not an ETL load.
How Do You Run These Data Validation Checks on Every Load?
Save the checks in a dataflow, schedule it, and call it from CI/CD through the command line or REST API. Results can update Azure DevOps Test Plans, notifications go out by email or Microsoft Teams, and a failed batch run can open a Jira ticket. When a mapping changes, update the expected results first, then regenerate the queries.
See also testing Type 2 slowly changing dimensions for dimension tables with history, AI-driven ETL testing for data warehouses and the ETL testing framework for enterprise pipelines for pipeline design, and cloud data test automation and data reconciliation for platforms and record matching.
Conclusion: How Do You Know the Load Did What the Mapping Said?
Give every mapping rule an input row, an expected result, and an automated check. The five rules here needed four rows and four checks, and the pattern scales to every table.
ETL Validator, part of Datagaps DataOps Suite, drafts the queries from your mapping and keeps the reports as evidence. The ROI calculator estimates your savings, and teams planning a platform move can continue with data migration testing automation.
Frequently Asked Questions
What Is a Source-to-target ETL Test?
A source-to-target ETL test compares loaded target data with the source data after applying the mapping rules. Each rule has an input row, an expected result, and a pass or fail outcome.
How Do You Write Expected Results for an ETL Test?
Derive them from the mapping rule and the source row before the load runs. Include NULLs, blanks, and boundary values, and state the database engine, because functions such as CONCAT treat NULL differently.
Can ETL Validator Generate Tests From a Mapping Document?
Yes. The Generate Dataflow wizard and Mapping Manager both draft source and target queries from the business rules in a mapping. The wizard supports JDBC connections and SELECT queries only, and a reviewer approves each query.
Book a personalized 30-minute demo, or start a 14-day free trial.
RajMohan Achanta
Associate Product Manager, Datagaps
Associate Product Manager at Datagaps. Shapes the product experience across ETL Validator, BI Validator, and DataOps Data Quality.
Avinash Keshri
Head, Product Marketing
Head of Product Marketing at Datagaps and IIM Bangalore alumnus. 13+ years of experience in commercializing AI and data platforms across global markets.
