A short guide to ETL pipelines

The version of this that works is simpler than the version most people imagine. Everything we would tell a client about ETL pipelines in the time it takes to drink a coffee.

Data outlives the applications built on top of it, which is why the model deserves more thought than the screens. 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. It is worth being explicit about, because assumptions differ quietly. Budget a little time for it every quarter and it never becomes a project of its own.

How to approach it

Make every run re-runnable without duplicating data. This is the sort of thing that compounds, quietly, in both directions. The version that survives contact with a real deadline is the simple one.

Where it usually goes wrong

Validate at the boundary, not three steps later. The cost of getting this wrong is rarely visible on the day it happens. Doing this properly once is usually cheaper than doing it approximately three times.

In practice

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
• Pipelines fail silently unless you design them not to — and you know whether that is true here
• There is a way to tell whether the last change to this helped

The point is not perfection, it is knowing which of these you have consciously chosen to skip.