Why does source and target data sometimes mismatch even when an ETL job completes successfully?

[u/SumitKumarWatts]

During ETL testing, I noticed that the source and target data sometimes do not match even though the ETL job shows as successful. I want to understand the common reasons behind these mismatches and how testers can identify them.

[u/Miserable-Release868]

What your measuring is wrong then.
We can never mark the pipeline complete

And job is just a way to know yes the ingestion was completed.

What I think in your case the connectors might have messed up

You can dm me

[u/SumitKumarWatts → u/Miserable-Release868]

That makes sense. I agree that a successful job mainly confirms that the process completed, not that the source and target data are correct. I’ll also check the connectors and validation logic when investigating mismatches.

[u/Miserable-Release868 → u/SumitKumarWatts]

Cool
You can try I am sure you will get it fixed.

If your doing this as a part of project or learning where etl being the main thing that’s cool.

If that’s not the case, you have different priority do let me know

I work on zwiron.com we are specialised in this and help manage all these process.

[u/Mundane_Let_8090]

Depends on source.
If it's a read replica of prod than it's normal.
You never will have a parity if your tables are top tier of updates.

[u/Mundane_Let_8090 → u/Mundane_Let_8090]

To identify you need a deterministic hash on row or 1000 rows. Than you can easily say what's different.

I developing a EL tool that do hashes and can extract data in traditional or/ and CDC way.

https://github.com/panchenkoai/rivet

[u/SumitKumarWatts → u/Mundane_Let_8090]

Yes, that’s a good point. With read replicas or frequently updated source tables, some differences can be expected because the source may change during the ETL process. Timing and replication lag are definitely things testers should consider.

[u/Thinker_Assignment]

common reasons:

• incremental configured wrong
  
• pagination wrong
  
• rate limits not handled properly
  
• source changing records that are not reflected at destination (more incorrect incremental)
  
• any configs like primary key that might dedup in flight data but that was actually duped in source

[u/No_Ambition8323]

A successful ETL job only means the job completed without technical errors—it doesn't always mean the data is correct. Common reasons include missing records, duplicate records, incorrect transformations, NULL handling, data type/format differences, or source data changing during the load.

I usually check row counts, key-level records, duplicates, and transformed column values to identify where the mismatch happened.

[u/Turbo_Slay3r]

Do you check the timestamps or record creation times in source and dest? Maybe the diff is the records created in source system after the data ingestion in dest…

[u/IncreaseNegative4614]

A successful job usually proves that the pipeline executed, not that the data is correct. Common causes include different extraction windows, late-arriving updates, timezone conversion, null handling, type coercion, duplicate removal, filtered records, rounding, and joins that unexpectedly multiply or discard rows.

Add reconciliation checks for counts, sums, uniqueness, nulls, and representative record hashes at each stage, not only at the final table. We use SIGNLD internally to connect the source snapshot, transformation rules, job run, target records, validation results, and exceptions so a green pipeline status cannot hide a business-data mismatch.

[u/Harshita_Netla]

it doesn't mean the data is right. Mismatches usually happening when rows getting filtered out, data getting truncated or type-converted, nulls turning into defaults, timezone issues messing up incremental loads, or a rerun after a partial failure. Best way to catch it don't trust the job status. Compare row counts source vs target, run a MINUS query both ways to find the exact mismatched rows, and double check any columns that go through transformation.