Data Engineering

How to Know Your Data Pipeline Is Actually Trustworthy

Aetherisys Consulting #data-engineering#data-quality#pipelines

“The numbers look wrong” is one of the worst messages an engineering leader can get on a Monday morning. Worse still is not being able to answer it quickly. Most pipelines do not fail loudly. They fail quietly: a column goes null, an upstream API changes a field type, a join silently drops half the rows, and the dashboard keeps rendering. Nobody gets paged. The data is simply wrong, and it stays wrong until someone notices.

Trust in a pipeline is not a feeling. It is a property you build, measure, and defend. Here is how to do that.

Start with a data contract

If you cannot describe what “correct” looks like, you cannot test for it. A data contract is a written, version-controlled agreement about the shape and semantics of data crossing a boundary — between an upstream service and your ingestion job, or between your warehouse and a downstream consumer.

A useful contract specifies:

  • Schema — column names, types, nullability.
  • Semantics — what status = 3 means, what timezone created_at is in, what the grain of the table is.
  • Guarantees — freshness (updated every 15 minutes), completeness (no gaps), uniqueness keys.
  • Ownership — who to contact when it breaks, and who is allowed to change it.

The point is not the document. The point is that a contract turns a vague expectation into something a machine can enforce. When the upstream team changes a field, the contract check fails in CI, not three weeks later in a board deck.

Test the data, not just the code

Unit tests on your transformation logic are necessary but not sufficient. Your code can be perfectly correct and still produce garbage because the input was garbage. You need tests that run against the data itself, on every load.

Four categories cover most real incidents:

  • Schema checks — the table has the columns you expect, with the types you expect. Catch breaking changes at the door.
  • Volume checks — today’s row count is within a sane band of the trailing average. A load that brings in 10 rows instead of 10 million should never reach a consumer.
  • Freshness checks — the newest record is recent enough. A pipeline that silently stops is more dangerous than one that crashes, because it looks healthy.
  • Distribution checks — null rates, category cardinality, and numeric ranges stay within expected bounds. A revenue column that suddenly contains negatives is telling you something.

A blunt but effective freshness assertion, runnable straight after a load:

SELECT
  MAX(updated_at) AS latest,
  CASE
    WHEN MAX(updated_at) < NOW() - INTERVAL '2 hours'
    THEN 'STALE' ELSE 'OK'
  END AS status
FROM analytics.orders;

Wire these into the pipeline so a failure blocks publication rather than just logging a warning. A check nobody acts on is theatre.

Make jobs idempotent

A pipeline you cannot safely re-run is a pipeline you cannot trust, because the day you most need to re-run it is the day it has already half-failed. Idempotency means running the same job twice produces the same result — no double-counting, no duplicate rows.

In practice that means partitioned writes that replace rather than append, MERGE keyed on a stable business identifier, or delete-and-reload within a bounded window. Once a job is idempotent, recovery stops being a delicate manual operation and becomes “run it again”. That is the difference between a calm incident and a long one.

Reconcile against a source of truth

Internal consistency is not the same as correctness. Your pipeline can pass every schema and volume check and still be wrong, because it is faithfully transforming the wrong numbers.

Reconciliation closes that gap. Pick a metric that matters — daily revenue, active accounts, units shipped — and compare your pipeline’s figure against an independent source: the operational database, the payment processor, the finance team’s ledger. Automate the comparison, run it daily, and alert on drift beyond a tolerance. When the gap is zero, you have evidence, not optimism. When it is not, you find out before your stakeholders do.

Track lineage so you can answer “why”

When a number looks wrong, the first question is “where did it come from”. Without lineage you answer that by reading code and guessing. With lineage — captured automatically by your transformation tool, or modelled explicitly — you can trace any field back through every table and join to its raw source in minutes.

Lineage also makes impact analysis honest. Before changing an upstream table, you can see exactly which downstream models and dashboards depend on it, instead of finding out from the people whose reports just broke.

Observability: know before your users do

The goal is simple: you should learn about a data problem from your own monitoring, not from a stakeholder. That means every run emits metrics — rows processed, duration, check results — and those metrics are visible on a dashboard and alert when they break their expected band.

Treat data incidents like production incidents. They get a severity, an owner, and a short write-up afterwards. Most data bugs are repeat offenders; a habit of asking “what check would have caught this” turns each incident into one more permanent assertion.

It compounds

None of this is exotic. Contracts, data tests, idempotency, reconciliation, lineage and observability are well-understood practices — the hard part is applying them with discipline before an incident forces the issue. Done together, they change the question from “do I trust this pipeline” to “here is the evidence that I can”. Fast does not mean careless: a well-instrumented pipeline is faster to change precisely because you find out immediately when you have broken something.

This is the kind of work our data engineering practice does — building pipelines that are correct by construction and stay that way.

If you own a pipeline and cannot say, with evidence, that its output is correct, get in touch and we will help you close that gap.

← Back to blog