Data Pipeline

Before the phrase existed, moving data around was still a job — a person just did it by hand. If Monday's meeting needed numbers, someone opened the payment system's admin screen and downloaded last week's transactions as a file, asked whoever ran the servers to pull the access logs, exported the ticket list out of the support tool, and pasted all three into one spreadsheet.

Pagedeck worked this way for its first year. Mia spent most of every Friday afternoon on it. The downloading was the easy part. Each of the three files pointed at users differently — payments used an account number, access logs used an internal identifier, support tickets used an email address — so stitching three rows back into one person took over an hour on its own.

Three problems sat underneath the lost time, and each of them was worse than the time.

The first is that the number could not be produced twice. When someone in the meeting asked to see last month on the same basis, Mia had to remember which rows she had dropped and what she had joined to what. A month later that memory was mostly gone.

The second is that the number changed when the person changed. Hand the same three files to a colleague and ask for the same metric, and one small judgment — do refunds come out of the total or not — moves the result by a few percent. Neither person is wrong, and nobody in the room can tell which version the slide is showing.

The third is that the work stopped when the person did. A week of vacation meant a week with no numbers at all, and the one colleague who tried to cover produced a revenue figure eleven percent off the usual one, which nobody caught until the following month.

A data pipeline writes that manual work down as code once and lets a machine run it on a schedule. Where to pull from, which rules reshape the values, where the result lands — write it down once and the same steps run in the same order at four every morning. The place the moved data accumulates is an analytical data store. People stop touching the middle and look only when a run fails. And because the judgment calls now live in code, the question of what last month's basis was gets answered by reading a file rather than by remembering.

The vocabulary under this term splits four ways. Whether you clean the data before moving it or after is the method. How often the flow turns is the cadence. What executes the many small pieces in the right order is the tooling. And what you do when a stretch of it comes out wrong is operations.

Method splits on where the transformation happens. Transformation means reshaping what came out of the source into something usable: normalizing date formats, converting a payment that arrived in dollars, dropping internal test accounts. There are two branches.

1. Extract-transform-load (ETL) — pull from the source, transform in the middle, load into the store. Only cleaned data ever lands, so storage and compute bills stay small. The cost arrives six months later, when somebody asks how many of those filtered test accounts there were. The filtered rows were never kept, so answering means going back to the source.
2. Extract-load-transform (ELT) — flip the last two steps. Load the source data almost untouched, then transform it inside the store. The original is still sitting there, so a changed definition means recalculating in place instead of extracting all over again.

Which one fits depends on what compute costs in your store and on how settled your definitions are. Large source volumes with stable definitions favor trimming before the load. A team still arguing about what to count favors keeping the raw rows. Early-stage teams usually land on ELT, because in year one the definitions change faster than the data grows. The choice is not permanent either. Plenty of pipelines start as ELT and gain a trimming step in front of the load once one source starts arriving in millions of rows a day and the monthly bill for scanning it stops being ignorable.

Cadence splits on how often the flow turns. The question that settles it is whether the number is allowed to be a few minutes old.

1. Batch processing — handle everything that piled up since the last run, all at once, on a schedule. Three in the morning, yesterday's full day. It is simple to build, and a failure means running it again after breakfast, which is why most metrics come out of batch.
2. Stream processing — handle each record the moment it arrives. This is what you need when a payment has to appear in an on-screen total within seconds of clearing. The trade is repair. Re-running only yesterday between two and three in the afternoon is far fiddlier in a continuous flow than in a batch.

Most teams start with batch and peel one piece off into a stream only when something genuinely has to be known within seconds. Building everything as a stream from day one multiplies both the cost of writing it and the cost of fixing it later.

Tooling is the branch that actually runs the thing. A workflow orchestrator executes many jobs in the right order, respecting what depends on what. A pipeline is rarely one block; it is dozens of small jobs with a sequence between them. Pulling payment records has to finish before payments can be joined to users, and that has to finish before the revenue table can be built. The orchestrator holds that map, and when an upstream job fails it refuses to start the downstream ones and alerts whoever owns them. Without it, the downstream jobs run anyway and quietly publish a table that is half full. It also keeps the schedule honest: rather than guessing that the payment pull is probably done by four fifteen and starting the join then, the join starts when the pull reports that it finished.

Operations is the branch for pipelines already in flight, and its two terms are a pair. A backfill reprocesses a past stretch to fill it in. You reach for it when the pipeline failed quietly for three days, or when a new metric has to be calculated the same way across the last twelve months. An idempotent pipeline is one where running the same stretch twice does not change the answer. Without that property the backfill becomes the incident itself: re-run May 1st and that day's revenue lands on top of what was already there, doubling it. This is why pipelines are usually written to delete the results for a stretch and rewrite them rather than to append. Run it once or five times and the stretch still holds one value.

The four branches run in order. Method and cadence fix the shape, tooling runs the shape, and the moment it starts running, failure and reprocessing arrive. What the whole flow protects is data quality. One step going quietly wrong bends every calculation sitting on top of it, so half of building a pipeline is not the code that moves rows — it is the machinery that makes a person notice when what arrived looks wrong.

"That pipeline failed last night, so today's dashboard is a day behind."

The numbers on the screen are not wrong; they just stop at yesterday. Nothing to debug in the query — the run needs to go again.

"Changing the metric definition means a backfill too, so budget two days."

Applying a new basis only going forward leaves a visible kink in the chart on the day it changed, so the past has to be recalculated the same way.

"That data isn't in the pipeline yet, so we can't look at it today."

The values exist in the source system, but nothing carries them into the store. This is an engineering request wearing an analysis request's clothes, and the honest estimate is days rather than the afternoon the asker had in mind.

The loudest version of this at Pagedeck started when Mia asked to see paid conversion broken out by day of the week. It looked like an hour of work until Theo opened the table. The signup timestamp column did not hold the moment a user signed up; it held the moment the pipeline processed the row. Since the batch ran at four in the morning, eight months of signups were all stamped four a.m., and splitting them by weekday said nothing at all.

The real timestamps were still in the service database, so it was fixable. But fixing it meant rewriting the pipeline and then re-running eight months, and that pipeline appended rather than replaced — a re-run would have counted every payment twice. So the order inverted. The processing was rewritten to be safe to re-run first, and the backfill went second. A one-line request took a week, not because the request was hard, but because nobody decided which timestamp to keep on the day the pipeline was first written.

The most common mistake is treating a pipeline as something you install once. It makes no noise while it runs, so it reads as finished. But everything it leans on keeps moving. The payment system renames a field in its response, a new feature adds a record shape nobody planned for, empty values start appearing in a column that never had them. Most of those changes do not stop the run — they only bend the output. That is why pipelines ship with checks beside them: alert if today's row count is under half of normal, alert if a required field is empty more often than usual. A pipeline that fails loudly gets fixed that morning. A pipeline that quietly produces half-right numbers gets discovered in a quarterly review, by which point several decisions have already been made on those numbers.

The second is reaching for the query first when a dashboard number looks off. The query is the visible part, so suspicion lands there naturally. The cause usually sits further upstream. The order worth following is whether the value exists in the source at all, whether the pipeline succeeded last night, whether the metric definition changed recently — and only then whether the calculation is wrong. Reversed, you spend three days reading a query that was correct the whole time and learn at the end that the pipeline has been failing since the weekend. The order is not arbitrary. Each step upstream can break everything below it, so checking from the top costs one glance per layer and checking from the bottom costs a rewrite per layer.