Source-to-Target Testing: The Most Ignored Step in Data Engineering

Ask a data engineering team if they test their pipelines and the answer is almost always yes. They have unit tests for transformation logic. They have dbt tests checking for nulls and uniqueness.

They have monitoring dashboards that confirm pipeline jobs ran. They have row count checks.

What most teams don't have is source-to-target testing — the systematic, field-level comparison of data as it existed in the source system against data as it exists in the target system, after every transformation has been applied.

This is not the same as checking that a job ran. It is not the same as checking that the row count matches. It is the verification that every value that left the source arrived in the target correctly — transformed according to the defined mapping, with no silent truncation, no type coercion error, no business rule applied incorrectly, and no record lost between systems.

It is the most important test in data engineering. And it is, by a considerable margin, the least consistently implemented one.

"We tested everything. The pipeline ran perfectly. We just didn't test that the right data ended up in the right place." — A data engineering post-mortem, repeated in almost identical form across the industry.

What Source-to-Target Testing Actually Is

Source-to-target testing (also called S2T testing, or source-to-target mapping validation) is the process of verifying that data in a target system accurately reflects data in a source system — accounting for every transformation, filter, type conversion, business rule, and enrichment applied in between.

It operates at three levels:

Level 1 — Structural Validation

Does the target schema match the source-to-target mapping document? Column names, data types, lengths, nullability constraints, and primary/foreign key definitions. This is the cheapest level to validate and the most commonly done — usually as part of DDL review.

Level 2 — Volume Validation

Does the target contain the expected number of records? Row count parity between source and target, accounting for any intentional filtering, deduplication, or aggregation applied during transformation. Also commonly done — usually as the final check before declaring a migration or load complete.

Level 3 — Field-Level Value Validation

Does every field in every record in the target contain the correct value — the value that the source-to-target mapping says it should contain, given the source value? This level is what most teams skip. It is also where the majority of real-world data quality failures originate.

A pipeline can pass Level 1 and Level 2 validation while catastrophically failing Level 3. The schema is correct. The row count matches. The data is wrong.

⚠  Row count parity is not proof of data correctness. It is proof that the same number of records exist in both systems. Those records can contain systematically wrong values and still pass a row count check.

Why Source-to-Target Testing Gets Skipped

The reasons source-to-target testing is consistently deprioritised are structural, not technical. The technical challenge is real but solvable. The structural reasons are more persistent.

1. It Requires Access to Both Systems Simultaneously

To compare source to target, you need live query access to the source system at the same time as the target. In many organisations, source systems are production databases with access restrictions, different infrastructure teams, and change windows. Getting simultaneous query access to compare millions of records is operationally more complex than running a dbt test against the warehouse alone.

2. It Doesn't Fit Neatly Into the Standard Testing Toolchain

Most data quality tools — Great Expectations, dbt tests, Soda — are designed to test the data in one system against a defined expectation. Source-to-target testing requires comparing two systems against each other. This is a different architectural pattern that most standard toolchains don't support out of the box.

3. The Volume Makes Manual Testing Impossible

At 10 million rows with 50 columns per table, field-level source-to-target comparison produces 500 million individual comparisons per table. Manual sampling catches the systematic errors only if the sample happens to hit the affected records — which, for errors introduced by a specific transformation condition, it often doesn't. Teams rationalise that sampling is good enough. It isn't.

4. The Cost of the Failure Isn't Visible Until It's Too Late

Source-to-target failures are silent. The pipeline doesn't fail. No error is thrown. The data lands in the target looking structurally correct. The failure only becomes visible when a business user notices that a number looks wrong, or when a downstream ML model starts behaving unexpectedly, or when an audit finds discrepancies between what the system reports and what the source actually said. By then, the problem has been in production for weeks.

What Source-to-Target Failures Actually Look Like

Before making the case for why source-to-target testing is worth the investment, it helps to understand what the failures look like in practice. These are not edge cases.

Transformation Logic Applied Incorrectly to a Subset of Records

A business rule says: "where order_status = 'cancelled', set revenue = 0". The transformation logic has a bug: it applies revenue = 0 to all records where order_status IS NOT NULL — which is every record. Row counts match. Schema matches. Revenue figures are zero for every order in the target. A source-to-target comparison catches this immediately. A row count check does not.

Silent Type Coercion Causing Precision Loss

A source column contains DECIMAL(18,6) financial values. The target mapping specifies DECIMAL(18,2). The load completes without error — Snowflake silently rounds to 2 decimal places. For individual transaction amounts, the difference is fractions of a cent. For aggregated revenue across billions of transactions, it compounds into millions of dollars of discrepancy in financial reports. Source-to-target field comparison catches the precision delta on the first record.

Date Field Timezone Shift

A source system stores created_at in Eastern Time. The target warehouse is configured to store timestamps in UTC. The migration doesn't apply timezone conversion. Every created_at value is shifted by 5 hours. Day-boundary transactions (those occurring between 7 PM and midnight Eastern) are attributed to the wrong date in the target. Weekly and monthly reports are wrong. No schema error. No row count mismatch. Source-to-target date comparison reveals the shift in the first 100 records.

Lookup Substitution That Silently Drops Values

A mapping document says: substitute product_category_code with the human-readable category_name from a lookup table. The lookup table is missing 12 category codes that exist in the source data. The ETL applies a LEFT JOIN — records with missing lookups get NULL for category_name. 340,000 records in the target have null categories. Source-to-target comparison catches it. The business discovers it three weeks later when category reports show an inexplicable spike in uncategorised revenue.

Building Source-to-Target Testing Into Your Pipeline

The framework for source-to-target testing has three components: the mapping document, the comparison logic, and the remediation workflow. All three need to be in place for testing to be effective.

Component 1: The Source-to-Target Mapping Document as a Test Spec

Every data pipeline should have a source-to-target mapping document — a specification that defines, for every target field, what source field it comes from and what transformation is applied. In many teams this document exists but is not kept in sync with the actual pipeline. The first discipline of source-to-target testing is treating this document as the authoritative test specification, not as an artefact produced at the start of a project and never updated.

For each mapping, the document should specify:

• Source system, table, and column name

• Target system, table, and column name

• Transformation rule (verbatim — not "apply business logic" but the actual formula)

• Expected data type and length in both source and target

• Handling for nulls, defaults, and edge cases

• Test case: at least one known input-output pair that can be used to validate the mapping

Component 2: Automated Field-Level Comparison at Scale

Manual comparison of source and target fields doesn't work above a few thousand records. The comparison logic needs to be automated and needs to run against the full dataset, not a sample.

At a minimum, the automated comparison should:

• Compare every mapped field, record by record, 

        joining on the primary key defined in the mapping document, and flagging any record where the target value does not match the expected transformation of the source value.

• Produce a failure report at field level — 

        not just "table X has mismatches" but "column revenue in table orders has 340,000 mismatches; top failure: source value 1247.89 → expected target value 1247.89 → actual target value 0.00".

• Run on every load, not just at initial deployment. 

        Source-to-target testing is not a migration acceptance test — it is a regression test that should run every time data flows through the pipeline, because transformations change, source schemas evolve, and lookup tables get updated.

A basic source-to-target comparison query pattern:

-- Source-to-target field comparison: orders.revenue

SELECT

  s.order_id,

  s.unit_price * s.quantity            AS expected_revenue,

  t.revenue                             AS actual_revenue,

  ABS((s.unit_price * s.quantity)

      - t.revenue)                      AS delta

FROM source_db.orders s

JOIN target_db.orders t ON s.order_id = t.order_id

WHERE ABS((s.unit_price * s.quantity) - t.revenue) > 0.01

ORDER BY delta DESC;

Component 3: Remediation Workflow

A source-to-target test that fails and triggers no action is not a test — it's a metric nobody acts on. Every failure must have a defined remediation path:

Failure Type	Immediate Action	Root Cause Category
All records for a field mismatch	Halt pipeline; do not load subsequent tables	Transformation logic error
Subset of records mismatch (pattern)	Quarantine affected records; investigate condition	Business rule edge case or lookup gap
Subset of records mismatch (random)	Log and continue; investigate source data anomaly	Source data quality issue
Precision/rounding mismatch	Review data type mapping; check target column definition	Type coercion or precision loss
Date/timestamp mismatch	Check timezone configuration on source, ETL, and target	Timezone handling inconsistency

The Source-to-Target Testing Checklist

Use this at every stage of a pipeline build or migration:

• Source-to-target mapping document is complete, current, and version-controlled

• Every mapped field has a defined transformation rule — not "as-is" for complex fields

• Test cases exist for every transformation with at least one known input-output pair

• Automated field-level comparison configured for all mapped fields

• Comparison runs on full dataset — not a sample

• Failure threshold defined: what percentage of mismatches triggers a halt vs a flag

• Failure report is field-specific, not just table-level

• Remediation workflow defined for each failure category

• Test runs on every load cycle, not just initial deployment

• Results are logged and auditable — not ephemeral

The Bottom Line

Source-to-target testing is not exotic. It is not optional for teams that care about data quality. It is the fundamental verification that a data pipeline does what it claims to do — that data which left the source arrived in the target correctly.

The reason it gets skipped is not that teams don't understand its value. It's that the tooling to do it at scale, automatically, against live systems, has historically required significant custom engineering. That gap is closing — automated source-to-target validation platforms can now run field-level comparisons across entire datasets without custom code, producing structured failure reports that your pipeline can act on.

If your current pipeline testing strategy doesn't include source-to-target field-level comparison — on every load, across the full dataset, with automated remediation — you have a gap between what you think your pipeline does and what it actually does. The business will eventually find that gap for you.

→  See Vexdata Data Transformation Validation: vexdata.io/data-transformation-validation

→  Book a demo: vexdata.io/contact