A pipeline I inherited had been running green for eleven months. Every job succeeded, every dashboard rendered, nobody had an alert. It was also dropping about 4% of records, silently, because an upstream system had started sending a field as an empty string instead of null and a filter was quietly excluding those rows.
Nobody found it through monitoring. A finance analyst found it because a number felt low.
That is the thing about data engineering that surprises people coming from application development. In a web service, failure is loud — a 500, a timeout, an angry user. In a data pipeline, the default failure mode is a job that succeeds while being wrong. Everything in this discipline that looks like paranoia is a response to that.
ETL and ELT: The Order Changed for a Reason
ETL — extract, transform, load — was the standard for decades. Pull data out of the source, reshape it on a machine in the middle, and load the finished result into the warehouse. It made sense when warehouse storage and compute were expensive: you only stored what you had decided you needed.
ELT — extract, load, transform — flips the last two steps. Land the raw data in the warehouse first, then transform it there using the warehouse's own compute.
The shift happened because cloud warehouses made storage cheap and compute elastic. But the reason I prefer ELT is not cost. It is that raw data you kept is data you can reprocess. When you discover a bug in a transformation from six months ago — and you will — ELT means re-running the transformation. ETL means going back to a source system that may no longer have the data, may have changed, or may not let you query that far back.
The pattern I use on nearly every project is three layers. A raw layer that is an append-only copy of what the source sent, never edited. A cleaned layer with types fixed, duplicates removed and naming standardised. A modelled layer with the business logic, aggregations and the tables analysts actually query. Each layer is reproducible from the one before it, which means a bug is a re-run rather than an archaeology project.
Idempotency Is the Whole Discipline
If you take one thing from this: a pipeline that produces a different result when run twice is broken, even when it succeeds.
Jobs get retried. Backfills get run. Someone triggers a DAG manually to test something. If any of those double your rows, you have a data quality incident that will be discovered weeks later by someone who noticed a total looked wrong.
The practical rules are short. Never append blindly — write to a partition and replace it, or merge on a key. Make every job take an explicit time window as a parameter rather than reading the clock, so re-running yesterday means re-running yesterday and not re-running today. And keep a natural key on every row so a merge has something to match on.
-- Reprocessing one day should replace that day, not add to it.
MERGE INTO analytics.orders AS target
USING staging.orders_2026_08_24 AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET
status = source.status, amount = source.amount,
updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT
(order_id, customer_id, status, amount, created_at, updated_at)
VALUES (source.order_id, source.customer_id, source.status,
source.amount, source.created_at, source.updated_at);
Boring, and it eliminates an entire genre of 2am problem.
Warehouse, Lake, or Both
The terminology has been muddied by vendors, so here is the distinction that matters operationally.
A data warehouse stores structured, modelled data optimised for analytical queries. You define the schema when you write. It is fast to query, easy for analysts, and it enforces a shape.
A data lake stores files in object storage in whatever format arrives. Schema is applied when you read. It is cheap, it accepts anything, and it will happily become a swamp of undocumented files nobody can interpret.
The honest guidance: most companies do not need a lake. If your data is transactional records, application events and a handful of SaaS exports, a warehouse alone is simpler and will serve you for years. Lakes earn their place with genuinely large volumes, unstructured content like images and audio, or a need to retain raw data cheaply for a long time.
Table formats like Iceberg and Delta have narrowed the gap by adding transactions, schema evolution and time travel on top of files in object storage. That is a real improvement, and it is also more moving parts. Start with the warehouse. Add the lake when you can name the specific thing the warehouse cannot do.
Spark: Powerful, and Usually Premature
Apache Spark is superb at what it is for — distributed processing of data that genuinely does not fit on one machine. Terabyte joins, large-scale transformations, workloads that need a cluster.
But the amount of Spark I have seen running over datasets that would fit comfortably in memory on a laptop is remarkable. A few million rows is not big data. DuckDB or Polars will process that faster than Spark can start its cluster, on one machine, with no distributed debugging.
My rule: if the data fits in RAM on a reasonably large single machine, use a single machine. The threshold keeps moving upward, and it is much higher than most people assume. Reach for Spark when you have measured that you need it, and be aware you are also taking on cluster tuning, shuffle performance, skewed partitions and a category of failure that only appears at scale.
Orchestration and the Thing It Does Not Solve
You need something to run jobs in order, handle dependencies, retry failures and let you backfill. Airflow, Dagster, Prefect — pick one, ideally the one someone on the team already knows.
What orchestration does not give you is any assurance that the data is correct. This is the gap that produced my eleven-month silent failure. The scheduler cares whether the job exited zero. It has no opinion about whether the numbers make sense.
So every pipeline I build now has assertions in it, and they fail the job:
Row count within an expected range. Today's load being 3% of yesterday's should stop the pipeline, not quietly overwrite a dashboard.
Null rates on important columns. A field that is normally 1% null arriving 40% null is a schema change upstream.
Uniqueness on keys. Duplicates are how the same revenue gets counted twice.
Freshness. The maximum timestamp should be recent. A source that silently stopped sending looks exactly like a quiet day.
Referential sanity. Orders pointing at customers that do not exist means something ran out of order.
These take an hour to write and they are the difference between finding a problem the day it happens and finding it in a board meeting.
Schema Changes Are the Recurring Enemy
Nearly every pipeline incident I have investigated traces back to an upstream change nobody communicated. A column renamed. A type widened. A status value added. An API field that started arriving as a string.
You cannot prevent this — the application team is doing their job and has no idea your pipeline exists. What you can do is fail fast and loudly. Validate incoming data against an expected schema at the ingestion boundary and reject what does not match, rather than coercing it and hoping. A pipeline that stops with "unexpected column type" is a twenty-minute fix. A pipeline that silently coerces is a month of wrong numbers.
It also helps enormously to know the application engineers and be on the channel where they announce releases. The best data quality tool I have used is a working relationship with the team that owns the source.
What I Would Set Up First
On a new data platform, in order: land raw data immutably before you transform anything. Put the transformations in version control with tests. Add the five assertions above to every table that matters. Make sure someone gets a message when a job fails, and that the message says which table and which day. Then, and only then, worry about the tooling everyone argues about.
Data engineering rewards suspicion. The pipelines I trust are not the ones that have never failed — they are the ones that have proved they will tell me when something is wrong.
