I write a lot of Python that moves data from one place to another — pulling from APIs and files, reshaping it, and landing it somewhere queryable. On paper this is the simplest kind of programming: read, transform, write. In practice, data pipelines are where hope goes to die, because the input is never as clean as the spec promised and the failure always happens on the run you weren't watching.
Over time I've settled on a structure that isn't clever, but is durable. The goal isn't elegance. The goal is that when something breaks — and it will — I can find out what, where, and why in minutes rather than hours.
Separate ingest, transform and load — really separate them
Everyone nods at "ETL," then writes a single function that fetches a record, cleans it, and inserts it, all in one loop. It works until it doesn't. The moment the database write fails halfway through, you have no idea which records made it and which didn't, and re-running risks duplicating everything.
I keep the three stages as genuinely distinct steps with a durable boundary between them:
- Ingest pulls raw data from the source and writes it down unchanged — to a file, a staging table, whatever. Its only job is to capture exactly what the source said, so I can replay it later without hitting the source again.
- Transform reads the raw capture and produces clean, validated records. It is pure in spirit: same input, same output, no side effects on the outside world.
- Load takes clean records and writes them to their destination, and this is the only step allowed to mutate the target system.
The payoff is enormous. When the transform logic is wrong, I fix it and re-run transform against the raw capture I already have — no need to re-fetch. When the load fails, I know the clean data is safe and I just retry the write. Each stage fails independently and recovers independently.
Validate at the boundary, not in the middle
The worst pipeline bugs are the ones where bad data flows halfway through the system before something chokes on it, and now the error message points at a place far from the actual problem. The fix is to validate aggressively at the point where data enters a stage, and reject it there with a clear message.
In Python I lean on explicit schema validation — libraries like pydantic turn "this dict should have these fields with these types" into a single line that either produces a clean typed object or raises an error naming exactly which field was wrong. That error, right at the boundary, is worth ten stack traces from deep inside a transform.
A pipeline's reliability is mostly a function of how early it refuses bad data.
Design every run to be safe to repeat
The single most important property of a pipeline is idempotency: running it twice with the same input should leave the system in the same state as running it once. Without this, every retry is a gamble and every recovery is a manual clean-up job.
In practice this means writes should be "upserts" keyed on something stable — insert if new, update if seen before — rather than blind inserts that pile up duplicates. It means processing a batch should be an all-or-nothing transaction where you can, so a half-finished run doesn't leave a mess. And it means giving each run an identity, so you can ask "did we already process this batch?" before doing it again. Once a pipeline is idempotent, operating it becomes calm: something failed, you run it again, it heals.
Make failure observable
Here is the rule I wish someone had told me early: a pipeline that fails silently is worse than one that crashes. A crash at least tells you something is wrong. Silent partial failure — where a run "succeeds" but quietly dropped a tenth of the records — corrupts your data and your trust in it.
So I instrument the boring numbers. Every run logs how many records it read, how many it transformed, how many it rejected and why, and how many it loaded. When "read 10,000, loaded 9,300" shows up, I know immediately that 700 records fell out somewhere and I can go find them. Structured logs with these counts, plus an alert when a stage throws, catch the vast majority of problems before a human notices the data looks off.
Keep configuration out of the code
Source URLs, credentials, batch sizes, date ranges — none of that belongs hard-coded in the logic. Pulling it into environment variables or a config file means the same pipeline code can run against staging and production, backfill an old date range, or adjust a batch size without a code change. It also keeps secrets out of the repository, which matters more than people admit until the day it doesn't.
The structure in one breath
Capture raw input before you touch it. Validate hard at every boundary. Keep transform pure and load isolated. Make every run idempotent so retries are safe. Log the record counts so failures are visible, not silent. Push configuration outside the code.
None of this makes the pipeline faster or more impressive. What it does is make 2am boring — and in data engineering, boring is the highest compliment there is.