Every morning, someone on a data team opens a dashboard full of green checkmarks. It shows that every overnight job finished successfully, with no failures. It's tempting to read that as, "the data is right." But that assumption is worth a second look. A green checkmark tells you a job ran, not that it loaded everything it should have.

‍A green job doesn’t necessarily mean correct data

Most pipelines define success the same way: the workflow was completed and nothing raised an error. Many also log how many rows they loaded. That feels like validation, but a row count with nothing to compare it to is a log entry, not a check.

The gaps that slip through are usually rare. A transaction arrives late or an error occurs halfway through a run, and the next one doesn’t look back. That rarity is the real problem. The rarer an error is, the more valuable verification becomes because it can flag the issue before it grows. A daily check finds the gap within hours, while the evidence is still fresh.

Incremental loads only look forward

Reloading everything every night is the safest approach, and for small tables it's often the right one. At data warehouse volumes, though, it takes too long. So most pipelines load incrementally, remembering where the last run stopped and picking up only what changed since.

Both common ways of detecting change have blind spots. A last-modified timestamp only helps if the source system reliably updates it. Built-in change data capture is more precise, but not every database offers it.

More importantly, incremental loading only looks forward. Once the marker moves on, nothing goes back. A missed row stays missed, and nothing reports it. That isn't a bug in one pipeline – it's how the approach works, and it's why it needs a partner that looks back.

Build the reconciliation loop

Accountants solved this long ago. Nobody trusts a ledger just because every entry was posted.  It gets reconciled against the bank statement. A data pipeline could benefit from similar verification: a separate process that runs after the load, compares source and destination, and reports where they disagree.

That check must be independent. If it reuses the load's queries or mapping, it risks inheriting the same mistakes. Ideally, someone other than the pipeline's author would design it. If that isn't possible, writing separate code that reads directly from the source, rather than the staging data already produced by the load, can help maintain that independence.  

What data is compared depends on the resources available. Primary keys verify that every record exists on both sides, while a last-updated date verifies that each record is current. A column checksum is the most thorough, but every new column means updating the check. Checks can start simple and be adjusted over time: loosened if they raise false alarms and tightened if they let something through. Both directions should be checked: rows missing from the destination and rows still there after being deleted at the source.

A count is a fine first output. But whenever an investigation requires digging for records by hand, the check should be updated to return them directly. And whenever the pipeline changes, the check should be reviewed too.

Keep checks sustainable

A check only helps if it runs every day, so it needs to use resources efficiently. Scheduling checks after the nightly load has finished but before business hours gives the team a heads-up before anyone opens a report. If checking all history is too slow for a daily run, a recent window can be checked each day, with a full comparison run over the weekend. Checks can also be layered, starting with counts and digging into individual keys only when those counts reveal discrepancies.

Start small, and fix the cause

Checks don’t need a big framework, and overly ambitious designs often go unfinished.

Start with one table. A good time is right after a data bug is fixed. A small check can confirm the issue is gone, and additional checks can build from there. Otherwise, the starting point can be the table where complete data matters most to the people who rely on it.

A first run is a useful baseline. If it finds anything, the downstream impact can be assessed and priorities agreed on with business owners before repairs are made.

Once the check returns missing keys, it can be tempting to feed them back automatically. That should be a last resort for underlying causes that can’t be fixed. A gap is a symptom; the real fix belongs in the pipeline.

Nobody writes a reconciliation loop into the acceptance criteria. The people who ask for a pipeline assume the data will be complete, and they should be able to. Making sure it is, even in the rare cases when something goes wrong, is the engineers’ job.

Like backups, a reconciliation loop is the kind of habit that lets a data team sleep at night.

‍