Data Engineering: Getting the Numbers to the Decision, Correctly

Two people open two dashboards and see two different revenue figures for the same month. Both dashboards are working exactly as built. The meeting stops being about revenue and becomes about whose number is right.

That is the problem data engineering exists to solve. Not moving data — moving it in a way that keeps it correct, current and explainable.

Why the numbers disagree#

Almost always one of five reasons, and it is worth being able to name them:

Different definitions. Finance counts revenue when the invoice is issued; Sales counts it when the deal closes. Both are right. Nobody wrote it down.

Different timing. One report refreshed at 2am, the other at noon. Six hours of transactions sit between them.

Different filters. One excludes cancelled orders, internal accounts and test data; the other does not.

Late-arriving data. A transaction dated Monday that reached the warehouse on Wednesday. Monday's number changed after Monday's report was sent.

Silent failure. A pipeline failed, and the dashboard cheerfully showed yesterday's data as though it were today's — with no indication anything was wrong.

Only the last is a bug in the usual sense. The other four are definition and communication problems, which is why buying a better tool rarely fixes them.

The shape of a pipeline#

Four stages, whatever the technology:

Source ──▶ Ingest ──▶ Store ──▶ Transform ──▶ Serve
 (app,      (extract   (raw,     (clean,       (dashboard,
  API,       on a       exactly   join,         report,
  file)      schedule)  as sent)  aggregate)    model)

The important convention is in the middle: store the raw data exactly as received, before you change anything. When a number looks wrong six weeks later — and it will — the raw layer is the only way to determine whether the source was wrong or your transformation was. Storage is cheap; re-deriving lost history is not.

ELT rather than ETL. Load raw data first, transform inside the warehouse afterwards. Modern warehouses are fast enough, and it means a transformation bug is fixable by re-running rather than by re-extracting from a source that may no longer have the data.

The quality checks that earn their place#

Most data quality frameworks are too elaborate to survive contact with a deadline. Five checks catch the overwhelming majority of real problems:

1. Freshness. Is the newest record as recent as it should be? This single check catches silent pipeline failure — the highest-impact failure mode, because nobody notices until a decision has been made on stale numbers.

2. Volume. Is today's row count within the expected range? Half the usual rows means a partial load. Double means duplicates.

3. Uniqueness. Are the keys that should be unique actually unique? Duplicate rows silently inflate every aggregate downstream.

4. Nulls where there should not be any. A field that is normally populated suddenly arriving empty usually means a source change nobody told you about.

5. Reconciliation against the source. Do the totals match the system of record? This is the only check that catches transformation logic errors, and it is the one most often skipped.

Run these automatically, on every load, and fail loudly. A pipeline that fails silently is worse than one that does not run — at least the second is obvious.

A dashboard showing stale data confidently is more damaging than a dashboard showing an error, because the first gets acted upon.

Definitions are the actual product#

The most valuable artefact a data team produces is not a pipeline. It is an agreed, written definition of each important metric.

For every metric that appears in a decision, record: what it means in business terms, the exact calculation, what is included and excluded, which source is authoritative, who owns the definition, and when it was last agreed.

This is unglamorous and it is the difference between a data function people trust and one they argue with. When two dashboards disagree, the question becomes "which definition does each use?" rather than "which team is wrong?"

Design decisions that matter#

Idempotency. Running the same job twice must produce the same result, not double the rows. This sounds obvious and is violated constantly. Without it you cannot safely re-run after a failure — and you will need to re-run after a failure.

Handle late data explicitly. Decide whether a record arriving three days late updates the original day or lands on the arrival day. Both are defensible; silence is not, because the number will change under someone.

Keep history. When a customer changes their address or a product changes category, do you overwrite or keep both? For anything used in trend analysis, overwriting silently rewrites the past.

Do not put business logic in the dashboard. A filter applied in one report and not another is exactly how the two-different-numbers problem starts. Calculate once, upstream, and let every consumer read the same result.

Version the transformations. They are code. They belong in version control, with review, like any other code that produces something people rely on.

Scale honestly#

The tooling conversation usually runs ahead of the requirement. A reasonable ladder:

Most organisations describing themselves as having "big data" have a few hundred million rows, which a modern warehouse handles without exotic architecture. Adopting streaming infrastructure for data consumed in a daily report is a common and expensive mismatch.

A sensible starting point#

Pick one decision that matters and is currently made on unreliable numbers. Trace it end to end: which source, which transformation, which definition, who reads it.

Build that single path properly — raw layer, tested transformation, the five quality checks, a written definition, an alert when it fails. Then do the next one.

This is slower than buying a platform and pointing it at everything, and it produces something people trust. The alternative — connecting every source quickly — usually produces a lot of dashboards and the same arguments.

FAQ#

What is the difference between a data warehouse and a data lake?#

A warehouse stores structured, modelled data optimised for querying. A lake stores raw files of any shape, cheaply. Most organisations end up with both: raw data lands in the lake, modelled data lives in the warehouse. Starting with only a lake often produces a store nobody can query usefully.

Do we need a data engineer, or can analysts do it?#

Analysts can build a great deal, and often should — they are closest to the questions. You need engineering when reliability matters: scheduling, failure handling, testing, and pipelines that must not break silently. The transition point is usually when someone else's decision depends on it.

How current should our data be?#

As current as the decisions require, and no more. Daily is sufficient for most reporting. Real-time costs substantially more in both infrastructure and complexity, and is genuinely necessary for a narrow set of operational use cases. Ask what decision changes if the data is an hour old — often the honest answer is none.

Why do our dashboards disagree?#

Almost always different definitions, different filters, or different refresh times — not a bug. Compare the definitions before debugging the pipeline; you will usually find the answer there.

How do we know a pipeline broke?#

Only if you check. Freshness and volume checks on every load, with an alert that reaches a person. Without them, you find out when someone questions a number, which is typically days later and after a decision.

Should we buy a platform or build?#

Buy the infrastructure — warehouse, orchestration, ingestion connectors are commodity and building them is rarely a good use of time. Build the transformations and definitions, because those encode how your business works and no vendor can supply them.

What is the most common expensive mistake?#

Not keeping raw data. When a number is questioned months later, teams without a raw layer cannot determine whether the source or the transformation was wrong, cannot correct history, and end up rebuilding pipelines from scratch. It is cheap to avoid and painful to retrofit.

What else is coming for Data Engineering