Data Engineering: Building Reliable Data Pipelines

Data engineering has a peculiar failure mode. The pipeline runs, the dashboard loads, the number appears — and it is wrong. Nothing crashed, nobody was paged, and the mistake is discovered three weeks later in a board meeting. That is what makes this discipline different from most software: correctness is silent, and so is its absence.

This guide covers how to build pipelines that produce numbers people can act on. It walks through ingestion, storage, transformation, orchestration, quality and observability — the whole path from a source system to a trustworthy metric — and explains not just what the components are, but which failure each one exists to prevent.

• The layered architecture almost every mature data platform converges on
• Batch versus streaming, and how to tell which one you actually need
• How to model data so business questions are answerable without heroics
• Idempotency, backfills and late-arriving data — the three hard problems
• Data quality testing and contracts that stop bad data at the door
• What to monitor, and the mistakes that make pipelines untrustworthy

1. What a data pipeline is for
2. The layered architecture
3. Ingestion: getting data in without lying about it
4. Batch, streaming, and the honest middle ground
5. Storage and file layout
6. Transformation and modelling
7. Orchestration and the three hard problems
8. Data quality and contracts
9. Observability for data
10. Cost, performance and the physics of scanning
11. Ten failure patterns
12. A worked example: one pipeline, end to end
13. Team, ownership and how platforms decay
14. Frequently asked questions

1. What a data pipeline is for

A pipeline moves data from where it is produced to where it can be asked questions, transforming it along the way so those questions are answerable. That is the whole job. Everything else — the tooling, the file formats, the orchestrators — is implementation detail in service of one property: the number on the dashboard should be the same number you would get if you counted by hand.

Data engineering differs from application engineering in three ways that shape every decision:

• Failures are silent. An application that breaks throws an error. A pipeline that breaks produces a plausible number, which is far more dangerous.
• You do not control your inputs. Source systems change schemas without warning, backfill history, and occasionally send the same record twice.
• Reprocessing is normal. Application code runs once per event. Pipeline code reruns over historical data whenever logic changes — which means every transformation must be safely repeatable.

2. The layered architecture

Almost every mature platform converges on the same layering, usually described as bronze, silver and gold — or raw, cleaned and curated. The names vary; the discipline does not.

The critical rule is that raw is immutable. If a transformation is wrong, you fix the transformation and rebuild the layers above it. If you have edited raw data, you cannot rebuild anything, and every downstream number becomes unverifiable. This single discipline separates platforms that recover from mistakes in an hour from those that recover in a quarter.

Why three layers rather than transforming straight to the answer? Because the failure modes are different and should be isolated. Ingestion problems are network and schema problems. Cleaning problems are quality problems. Modelling problems are business-definition problems. When they are mixed together, every incident becomes an archaeology exercise.

3. Ingestion: getting data in without lying about it

Extraction sounds trivial and is where most correctness bugs originate.

Full versus incremental

A full extract copies everything every run: simple, always consistent, and impossible past a certain size. An incremental extract copies only what changed since last time: efficient, and dependent on a change marker you must trust.

The trap is the updated-at column. It fails when a row is modified without the column being touched, when clocks differ between systems, when a transaction commits after a later-timestamped one, and when rows are hard-deleted, which leaves no trace at all. Two defences: overlap your window — re-read the last few hours as well as the new period, relying on idempotency to absorb duplicates — and periodically reconcile counts against the source.

Change data capture

Reading the database's transaction log gives you every insert, update and delete in commit order, without polling and without touching the source's performance. It is the most correct approach available and the most operationally involved: log retention windows, schema evolution and the initial snapshot all need handling. Worth it when data volume is high or deletes matter.

Events

Applications emit events directly to a stream. You get real-time data and semantics designed for analysis rather than reverse-engineered from a database schema. The cost is that you now depend on application teams to emit correct events — which is an organisational commitment, not a technical one.

Files and third-party APIs

Both are common and both misbehave. Files arrive late, arrive twice, arrive partially written, and change column order without warning. APIs are rate-limited, paginate inconsistently, and quietly cap historical windows. Defend with the same tools every time: land the raw payload first, record what you fetched and when, and make reprocessing safe.

Write the untouched source payload to storage before any parsing. It costs almost nothing, and it means a parsing bug discovered next month is a reprocessing job rather than a permanently lost week of data. This is the highest-return habit in data engineering.

4. Batch, streaming, and the honest middle ground

Streaming is more interesting to build and more expensive to operate. The right question is not "can we do real time?" but "what decision changes if this data is two minutes old instead of two hours?"

Most organisations need real streaming for a small number of use cases and hourly batch for everything else. The common mistake is adopting streaming platform-wide for a single genuine requirement, then paying the operational cost on all one hundred pipelines. Run streaming where the decision genuinely depends on it, and batch elsewhere.

Event time versus processing time

Every streaming system forces this distinction. Event time is when the thing happened; processing time is when your system saw it. A mobile app offline for six hours delivers events with a six-hour gap between the two. If you aggregate by processing time, your Tuesday numbers contain Monday's activity, and nobody will notice until they compare against another system. Aggregate by event time, define a watermark that says how late is too late, and decide explicitly what happens to stragglers — dropped, sent to a side output, or triggering a restatement.

5. Storage and file layout

Columnar formats

Analytical queries read a few columns from many rows. Columnar storage stores each column together, so a query touching three of eighty columns reads three columns' worth of bytes. Combined with compression and per-block statistics that let engines skip entire regions, this is routinely an order of magnitude faster and cheaper than row-oriented files. Use a columnar format for anything that will be queried; keep raw payloads in whatever form they arrived.

Partitioning

Partitioning splits data into directories by a column — almost always a date — so a query for last week reads seven partitions instead of five years. Two rules keep it useful: partition by the column your queries actually filter on, and keep partitions large enough to be efficient. Thousands of tiny files are slower than a few large ones, because per-file overhead dominates. Partitioning by an identifier with millions of values is the classic self-inflicted wound.

Table formats

Modern open table formats add a metadata layer over your files, providing atomic commits, schema evolution, time travel to previous snapshots, and row-level updates and deletes. They solve the problems that made file-based lakes painful — half-written tables visible to readers, no way to delete one customer's rows for a privacy request, no way to see yesterday's version. If you are building a lake today, start with a table format rather than bare files.

6. Transformation and modelling

ETL or ELT

Classically, data was transformed before loading because storage and compute were expensive. Modern warehouses invert this: load raw data, then transform inside the warehouse using SQL. This is simpler to debug, lets you re-derive everything from raw, and puts transformation logic in a language analysts can read and contribute to. Transform-before-load still makes sense when you must not store sensitive raw fields at all.

Dimensional modelling

The pattern that has outlasted every technology cycle. Split the world into facts — events with measures, such as an order line or a page view — and dimensions — the descriptive context, such as customer, product, date, store. Facts are long and thin and grow forever; dimensions are short and wide and change slowly.

This works because business questions have a consistent shape: a measure, sliced by attributes, filtered by others. Revenue by product category by month for one region maps directly onto one fact table joined to three dimensions. Analysts can answer new questions without a new pipeline, which is the entire point.

Grain first

Before writing a line of a fact table, state its grain in one sentence: "one row per order line per shipment." Every measure must be true at that grain. Mixing grains produces double counting — the most common serious bug in analytics, and one that produces numbers that look plausible for months.

Slowly changing dimensions

A customer moves from Manchester to Leeds. Should last year's orders now appear under Leeds? For finance, usually no — the order happened while they were in Manchester. Overwriting the value gives you the current picture and rewrites history; keeping versioned rows with valid-from and valid-to dates preserves history at the cost of complexity. Decide per attribute, and write the decision down, because someone will ask why two reports disagree.

7. Orchestration and the three hard problems

An orchestrator runs tasks in dependency order, retries failures, and provides visibility. Which one you use matters far less than whether your jobs have the properties that make orchestration safe.

Problem 1: Idempotency

Running a job twice must produce the same result as running it once. Without this, every retry risks duplicated rows, and retries are guaranteed.

The failing pattern is a job that simply appends its results: run it twice and every row exists twice, with nothing in the data to indicate which run produced it. The working pattern gives each run exclusive ownership of one partition — usually one day — and has it remove and rewrite that partition wholly. Better still, where the storage engine supports it, the removal and rewrite happen as a single atomic operation, so a reader never observes an empty window mid-run.

The same principle applies upward. A job that appends to a summary table cannot be rerun; a job that recomputes a summary for a bounded window and replaces it can be rerun any number of times. Designing every step this way is what makes retries, backfills and recovery from a bad deployment routine rather than dramatic.

Problem 2: Backfills

Logic changes, and two years of history must be recomputed. Design for this on day one: parameterise every job by the date window it processes, never by "now"; ensure a backfill of one day touches only that day; and make the run granularity small enough that a partial failure does not force starting again. A job that reads current_date() internally cannot be backfilled at all, which is discovered at the worst possible moment.

Problem 3: Late-arriving data

Monday's data arrives on Wednesday. Three legitimate policies exist, and the mistake is not choosing one. Restate: recompute affected historical partitions, giving correct numbers that change after publication. Cut off: ignore anything past a deadline, giving stable numbers that are slightly wrong. Adjust: record it in the current period with a correction marker, which accounting often prefers. Pick per dataset, document it, and make sure dashboards say which policy is in force.

8. Data quality and contracts

Testing data differs from testing code because the code can be correct while the data is wrong. You need both.

Test categories, in order of value

1. Freshness. Has this table been updated when expected? Catches the largest class of incidents — the pipeline that silently stopped.
2. Volume. Is today's row count within a plausible band? Catches partial loads and upstream outages.
3. Uniqueness and nullness. Is the key unique, are required fields populated? Catches join fan-out and schema changes.
4. Referential integrity. Does every foreign key resolve? Catches ordering problems between pipelines.
5. Distribution. Has the mix of values shifted unreasonably? Catches subtle upstream logic changes.
6. Reconciliation. Does the total match the source system? The only test that proves end-to-end correctness, and the one most often skipped.

Fail loudly, and in the right place

Tests should run before publishing to the curated layer, not after. A failed test should block promotion so consumers keep yesterday's correct data rather than receiving today's wrong data. Stale-but-correct beats fresh-but-wrong in nearly every business context, and this preference should be encoded in the pipeline rather than left to judgement during an incident.

Data contracts

A contract is an agreement between a producing system and the pipeline: these fields, these types, these guarantees, this notice period for changes. It converts "the schema changed and everything broke" from a recurring incident into a versioned, negotiated change. Enforce it at ingestion — validate incoming records against the schema and route violations to a quarantine area rather than letting them poison the table.

9. Observability for data

Application monitoring asks "is it up?" Data observability asks "is it right?" You need five things visible:

• Freshness per table, with an expectation attached so lateness is detectable automatically.
• Volume trends, so a drop to forty percent of normal raises an alert rather than a shrug.
• Schema changes, detected and announced rather than discovered by a broken dashboard.
• Lineage — which tables feed which, so an incident can be traced upstream and its blast radius announced downstream.
• Run history — duration, cost and failure rate per job, so gradual degradation is visible before it becomes an outage.

Lineage deserves emphasis. When a source system changes, the first question is always "what does this affect?" Without lineage, answering takes a day of searching; with it, the answer is a query. It also makes deprecation possible, which is the only way a platform stops accumulating tables forever.

10. Cost, performance and the physics of scanning

Nearly all analytical cost reduces to one quantity: bytes scanned. Every optimisation is a way of scanning fewer bytes.

Two operational habits matter as much as any technique. Set query and job cost limits so a single bad query cannot consume a month's budget — this happens to everyone eventually. And review your most expensive jobs monthly; the distribution is always heavily skewed, and a handful of jobs will account for most of the bill.

11. Ten failure patterns

1. Mutating raw data. Destroys your ability to rebuild anything. Raw is append-only, permanently.
2. Non-idempotent jobs. Every retry becomes a duplication event.
3. Trusting updated-at blindly. Silent data loss; use overlapping windows and reconcile.
4. Mixed grain in a fact table. Double counting that looks plausible for months.
5. Aggregating by processing time. Numbers that disagree with the source system for reasons nobody can explain.
6. Tests that run after publishing. Consumers get the bad data first, and trust is spent.
7. Partitioning by a high-cardinality column. Millions of tiny files; queries slow down as data grows.
8. No lineage. Every incident and every deprecation becomes an archaeology project.
9. Streaming everything. Operational cost of always-on stateful systems applied to reports nobody reads before nine in the morning.
10. Business logic duplicated in dashboards. Five definitions of "active customer", all defensible, none matching. Define metrics once in the curated layer.

12. A worked example: one pipeline, end to end

Consider a concrete requirement: daily revenue by product category, available by nine in the morning, matching the finance system exactly. It sounds simple, and it exercises nearly every idea above.

Ingestion. Orders live in a transactional database. A full extract is impractical at several hundred million rows, so we take an incremental extract on updated_at with a six-hour overlap, landing raw JSON to dated storage paths. Deletes do not appear in that column, so a weekly full-key reconciliation identifies rows that vanished from the source and marks them deleted downstream. The raw payload is written before parsing, so a mistake in parsing costs a rerun rather than a lost day.

Cleaning. The raw layer becomes a typed table: timestamps normalised to a single timezone, currency amounts stored as integers in minor units to avoid floating-point drift, duplicates resolved by keeping the latest version per order line, and rows failing validation routed to a quarantine table rather than dropped. Quarantine matters — silently discarded rows are how totals quietly diverge from the source.

Modelling. A fact table at the grain of one row per order line per day of order, joined to product, customer and date dimensions. The product dimension is versioned, because categories get reorganised and last quarter's revenue should stay in the category it was reported under. The grain is written in the table's documentation, and every measure is checked against it during review.

Orchestration. The job is parameterised by date, deletes and rewrites its own partition, and can be rerun for any historical day without side effects. A backfill of ninety days is ninety independent runs that can execute in parallel, which is only possible because no step reads the current date internally.

Quality. Before publishing, four tests run: freshness (raw data for the day exists), volume (row count within a band of the trailing four-week average for that weekday), uniqueness (order line key is unique) and reconciliation (total revenue matches the finance system within a tolerance). A failure blocks publication and pages the on-call engineer, leaving yesterday's correct table in place.

Late data. Orders occasionally arrive two days late. The policy chosen here is restatement: the previous seven partitions are recomputed each night, and the dashboard displays a note saying figures for the last week may adjust. That policy is written in the table's documentation, so nobody has to rediscover it during a disagreement.

None of these steps is complicated individually. Reliability comes from the fact that each one is explicit — including the ones most teams leave implicit, like what happens to a deleted row or a late order.

13. Team, ownership and how platforms decay

Data platforms rarely fail suddenly. They decay, and the decay follows a recognisable pattern worth naming so you can spot it early.

It starts with an urgent request that bypasses the curated layer — an analyst queries a cleaned table directly because the model does not have what they need today. That is reasonable once. Repeated for a year, half the dashboards depend on intermediate tables that were never intended as contracts, and any change to a cleaning step now breaks reporting unpredictably. The fix is not to forbid the shortcut but to make the curated path fast enough that the shortcut is rarely worth taking, and to periodically promote popular ad-hoc queries into modelled tables.

The second decay pattern is metric drift. Two teams need "active customers", each writes a definition in their own dashboard, and both are defensible. Six months later the numbers differ by fifteen percent in a meeting and confidence in the whole platform drops. Defining metrics once, in code, in the curated layer, and having dashboards reference that definition rather than reimplement it, is the only durable fix.

The third is orphaned pipelines. Someone builds a table for a project that ends; the pipeline keeps running for three years, costing money and occasionally failing at three in the morning. Usage tracking plus a deprecation process — announce, monitor for consumers, disable, delete after a grace period — is unglamorous and saves more money in most platforms than any query optimisation.

On ownership, the arrangement that works is: source system teams own the correctness and stability of what they emit, under a contract; the data platform team owns ingestion, transformation, the curated model and the tooling; analysts own the questions and the dashboards. What fails is the arrangement where a small central team is accountable for the correctness of data produced by twenty teams it has no influence over. That is not a staffing problem, it is a structural one, and no amount of additional tooling resolves it.

14. Frequently asked questions

Data warehouse, data lake, or lakehouse?

A warehouse stores structured, modelled data optimised for SQL. A lake stores files of any shape cheaply. A lakehouse adds a transactional table layer over lake storage so it behaves like a warehouse while keeping open formats. For most teams today the lakehouse pattern is the sensible default: warehouse ergonomics without vendor lock-in on storage. Choose a pure warehouse if your data is entirely structured and moderate in size, since it is simpler.

How do we start if we have nothing?

One source, one pipeline, one dashboard that someone has committed to using. Land raw, clean it, model it, test freshness and row count, schedule it daily. Resist building a platform before you have a single working end-to-end path — platforms designed in advance of real use consistently solve the wrong problems.

Do we need a dedicated orchestrator?

Not immediately. Scheduled jobs with sensible logging carry a handful of pipelines fine. Adopt an orchestrator when dependencies between jobs become real — when B must not run before A finishes — or when backfills and retries start being managed by hand. That threshold usually arrives somewhere around ten to twenty pipelines.

How much history should we keep?

Raw: as long as storage cost allows, because it is your only insurance against transformation bugs. Cleaned: enough to rebuild curated tables comfortably. Curated: as long as the business asks questions of it, which is usually longer than anyone predicts. Set retention deliberately per layer, and remember that privacy law may require deletion regardless of analytical value — which is a strong argument for a table format supporting row-level deletes.

Who should own data quality?

The team that produces the data owns its correctness at source; the data team owns correctness of transformation and publishes the tests. The arrangement that fails is one where the data team is expected to guarantee quality of data it neither produces nor controls — that ends in an endless cycle of firefighting. Data contracts exist specifically to make this boundary explicit.

Is SQL enough, or do we need a programming language?

SQL is enough for the overwhelming majority of transformation work and has the large advantage that analysts can read and contribute to it. Reach for a general-purpose language for ingestion, complex procedural logic, machine learning features and anything requiring calls to external services. A platform where transformation is mostly SQL and orchestration is mostly code ages well.

How do we handle personal data?

Classify at ingestion rather than later, and keep sensitive fields in separate columns or tables so access can be granted at that granularity. Prefer pseudonymisation for analytics — join on a hashed identifier rather than an email address. Track where personal data flows using lineage, because deletion requests require you to find every copy. Designing this in is straightforward; retrofitting it is a project.

What is the single highest-value thing to add to an existing platform?

Freshness and volume alerts on your most-used tables. Most data incidents are not subtle logic errors; they are pipelines that stopped running or loaded a fraction of the expected rows, and nobody noticed for days. Two tests per table, checked before publishing, catch a large share of real incidents for an afternoon of work.

Key takeaways

• Raw is immutable. The ability to rebuild from source is what makes every other mistake recoverable.
• Idempotency is not optional. Design every job so a rerun replaces rather than appends.
• State the grain. One sentence per fact table prevents the most common serious analytics bug.
• Test before publishing. Stale but correct beats fresh but wrong, and the pipeline should enforce that.
• Stream only where the decision requires it. Batch is cheaper, simpler and easier to reprocess.
• Lineage turns incidents into queries. Without it, every question about impact costs a day.

The measure of a data platform is not how much data it moves. It is whether, when someone questions a number, you can trace it back to its source in minutes and say with confidence where it came from.