Data & Analytics
A Data Pipeline Can Finish on Time and Still Be Wrong
Validate a data pipeline beyond job status using record reconciliation, freshness checks, and downstream acceptance criteria.
Accumentum® ·
Reconcile business keys and monetary totals as well as row counts. A missing transaction and a duplicate can leave the count unchanged. Decide with the data owner which discrepancies block publication and how to replay safely.
A scheduled data load reports success, the file has the expected number of rows, and the dashboard refreshes. None of those observations proves that the records are correct. Data engineers are responsible for maintaining meaning across ingestion, transformation, and consumption. A small change in date formats, duplicate handling, or source definitions can make a pipeline quietly wrong.
Validation should examine completeness, uniqueness, validity, consistency, timeliness, and reconciliation with authoritative totals. The appropriate checks depend on the business data. A row-count test is useful but insufficient for finance, identity, or regulated records. Track the batch or event identity and support idempotent retries where feasible so a failed or repeated run does not silently duplicate transactions.
How to practice
- Inspect the contract: Document schema, types, valid values, timezone, grain, and expected delivery timing.
- Validate content: Check nulls, range limits, duplicates, referential integrity, and authoritative totals.
- Quarantine exceptions: Define what fails the batch, what can be held for review, and who accepts partial data.
- Verify consumers: Reconcile a representative report and record freshness, lineage, and the status communicated to users.
The workplace scenario
A supplier sends exactly the usual row count, but a date column changes from month/day/year to day/month/year. Some values remain syntactically valid while representing the wrong dates. A robust response pauses affected downstream use, inspects the actual source contract, tests parsing with ambiguous examples, and coordinates a corrected load. The engineer should explain whether earlier data must be repaired.
Reconcile value as well as volume
Suppose a daily file contains 1,000 payment rows and the destination also contains 1,000. During loading, one $125 transaction was omitted and an unrelated $25 record appeared twice. A row-count check passes. The total is short by $100, and two record-level conditions are wrong. Build a reconciliation at the grain the business actually uses. If transaction identifiers are unique only within a source system, include the source in the key before interpreting a duplicate.
In a practice file, compare source and destination counts, distinct business keys, and amount totals for the same accounting window and currency. Isolate missing keys and unexpected keys in separate outputs. If a refund is a legitimate separate event, document how its sign and identifier work rather than removing it as a duplicate. Ask the data owner whether late-arriving transactions belong in the original reporting day or a later adjustment period.
Define what happens to a failed delivery
A useful acceptance rule names the consumer as well as the check. For this exercise, hold the affected revenue partition, label the dashboard with the last accepted refresh, and route the discrepancy to the pipeline owner. Before releasing a replay, verify that the corrected batch restores both the missing key and the total without counting a prior delivery twice. Keep the rejected and accepted reconciliation summaries for comparison.
A common mistake
Never infer correctness from "green" orchestration status. Also avoid quietly converting unknown values to null without logging and business approval. This can turn source defects into understated metrics.
Try it yourself
Write an acceptance checklist for a fictional daily revenue file. Include a schema test, a duplicate test, a reconciliation test, and an exception-response plan. State which results would block publication to the executive dashboard.
Should a pipeline always fail on one invalid row?
Not necessarily. The correct policy depends on materiality, contract requirements, ability to quarantine safely, and whether partial results could mislead downstream decisions.
Put it into practice
PathHire's Data Engineer pathway presents recoverable pipeline and reconciliation decisions. Accumentum data training can strengthen the relevant technical fundamentals.