Row Counts Are Not Data Validation

Ask a data team how they know last night's load was correct and the answer is usually some version of: the job succeeded and the row counts match. Both statements are true and neither is about the data.

A pipeline reports success when the code finished without raising. That is a claim about execution. Whether the values that landed mean what they meant at the source is a different question, and nothing in a green run has asked it.

What a green run does not tell you

Consider a load that moves order totals from an operational database into a warehouse. The job runs, the counts match, the dashboard refreshes. Any of the following can be true at the same time:

• Every amount was rounded from four decimal places to two, shifting totals by a fraction of a percent that compounds across millions of rows.
• A currency column was joined on a code that changed case, so a subset silently defaulted to the reporting currency.
• Two hundred rows failed a constraint, were routed to a rejects table nobody monitors, and two hundred rows from a previous day's replay took their place in the count.
• A transformation that should have excluded cancelled orders excluded refunded ones instead, because the specification used both words and the implementer picked one.

The last is the important category. It is not a data movement failure at all, it is a logic defect, and it will be reproduced perfectly on every subsequent run. Comparing today's output against yesterday's confirms only that the pipeline is consistently doing the same thing.

Test against the specification, not against yourself

This is the single most valuable change most teams can make. A transformation should be verified against the mapping specification that defines what it is supposed to do, expressed as assertions a human can read, rather than against a snapshot of its own earlier behaviour.

It is more work up front, because it forces the specification to be precise enough to test, and that is exactly where the value is. The ambiguity between 'cancelled' and 'refunded' surfaces while someone is writing the assertion, not during a quarter-end review.

A minimum set worth having

None of this is difficult. It is version-controlled SQL and a little Python, running in the pipeline rather than in a quarterly Manual reconciliation that eventually stops happening. The hard part is deciding it is worth doing before something expensive proves it was.

• Reconciliation: counts by partition, plus checksums and aggregate comparison on the columns that carry money, identity or time.
• Integrity: uniqueness where uniqueness is claimed, referential checks across tables, and an explicit assertion about what NULL is allowed to mean.
• Distribution: compare the shape of key columns against the source, which catches truncation and coercion that totals can hide.
• Freshness: assert the effective date of the data, not the load time of the job, which are different numbers that are frequently confused.
• Schema drift: compare environments and fail on unexpected differences before they reach production.

The organisational tell

If two dashboards disagree and the conversation is about which team's number to trust rather than which pipeline is wrong, the data layer has no tests. That is not a criticism of the people, it is a description of what happens when correctness is nobody's explicit deliverable.

We build reconciliation suites that live in your repository and run on every load, and we hand back findings in writing, including the ones that are awkward. That is what ETL and data warehouse Testing is for, and an assessment on a single pipeline is usually enough to show whether it is a problem worth funding.