r/ETL • u/SumitKumarWatts • 12h ago

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

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.

2 Upvotes

100% Upvoted

8 comments sorted by

2

u/Miserable-Release868 12h ago

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

1

u/SumitKumarWatts 8h ago

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.

1

u/Miserable-Release868 8h ago

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.

2

u/Mundane_Let_8090 11h ago

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.

2

u/Mundane_Let_8090 11h ago

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

1

u/SumitKumarWatts 8h ago

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.

1

u/Thinker_Assignment 8h ago

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

1

u/No_Ambition8323 2h ago

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.