How to get ETL pipelines right
There is no clever trick in this one, just a handful of decisions worth making deliberately. The short answer to ETL pipelines is that it is mostly a sequence of small decisions, not one big one.
Most data problems are ownership problems that turned into technical ones. If it only works because one person remembers to do something, it does not work yet.
The reason this keeps coming up
Pipelines fail silently unless you design them not to. In practice this is a scheduling problem more than a technical one. Anything you cannot measure here, you are deciding by taste, which is fine as long as everyone knows it.
The steps
- Establish what you have today before changing anything
- Make every run re-runnable without duplicating data
- Validate at the boundary, not three steps later
- Write down the decision so the next person does not re-litigate it
Validate at the boundary, not three steps later. In practice this is a scheduling problem more than a technical one. It is worth deciding this deliberately rather than inheriting whatever the last person set up.
Where to go from here
The short version
Numbers get quoted in meetings long after anyone remembers how they were calculated. Three things worth confirming about ETL pipelines before you move on:
- Someone can say what the current setup is without going to look
- Validate at the boundary, not three steps later — and you know whether that is true here
- There is a way to tell whether the last change to this helped
None of this needs a rewrite. Most of it is a morning's work once someone decides to do it.