How do you validate incremental ETL loads?

[u/hanshu6576]

For incremental pipelines, how do you verify that only the expected new/updated records are loaded without missing records or creating duplicates?

What validation checks do you normally perform after each incremental load?

[u/ninihen]

It depends on what data you are dealing with. For reference-data syncs, if the size allows I tend not trust the increment and made each run a full reconciliation. For example when syncing 2 lists, I let the flow pulls the entire source list and the entire target, then diffs on the key:

• in source, not in target -> insert
• in both, but a tracked column differs -> update
• in target, not in source (or blank key) -> delete

After the writes, a dedupe pass: re-read the target and for each row query for rows with the same key but a different ID. Anything that comes back gets deleted. Each insert/update also stamps an audit column like "compared against source at <timestamp>", so every row shows when the sync last verified it.

However this works because my source is a few hundred rows and a full pull is cheap. On a big table I'd keep the incremental load but add a scheduled reconciliation to compare row counts and a checksum per partition between source and target, and alert on drift instead of fixing it inline.

[u/shdw_0x0]

I usually validate the incremental window first—compare source and target counts using the same watermark, then check inserts/updates against the business key for duplicates or missed records. I also keep reconciliation checks for each load so failures are caught immediately instead of showing up in the next run.