Why Pipelines Exist

One chart, 3 attempts to produce it

A small online kitchenware shop sells pans and knives, along with kettles, through a website that writes every purchase to a Postgres table called orders: around 6,200 rows a day, 2 million in total. The finance lead wants a chart of yesterday's revenue by product category, refreshed before she arrives. Each of the 3 attempts to give it to her exposes 1 of the 3 gaps a pipeline exists to close.

The first attempt runs a report query straight against the checkout database. It returns the right numbers, and it scans 2 million rows on the machine that is also taking payments. Postgres serves a checkout with thousands of reads and writes of 1 row each, so a scan over 2 million rows competes for the same disk and memory. On a busy morning the report pushes checkout latency from 200 milliseconds to 2 seconds while it runs. That is the load gap: the system that records the data is tuned for a workload the opposite of analysis, and asking it to do both slows the business it serves.

The second attempt moves the work onto a person. An analyst exports yesterday's orders as a CSV at 7:30 and converts the amount column from text to numbers. She drops the 20 or so test orders developers placed from company email addresses, then sums by category and emails the chart. The conversion and the sum are needed because orders holds 1 row per purchase with the amount stored as a string, and the chart needs 1 row per category per day with a decimal total. That's the shape gap: data is recorded in the shape that made the sale fast to write, and read in the shape that makes a question fast to answer, and the 2 shapes differ in almost every real system.

The analyst's version holds for 3 weeks, until she takes a holiday. The chart shows Friday's numbers until Wednesday, because the export lived in 1 person's calendar and nobody else owned it. That is the schedule gap: the work recurs every day at a fixed time, and anything that depends on a human remembering it is eventually skipped. The finance lead made 2 pricing decisions that week on numbers 4 days old.

What a pipeline is

A data pipeline is a sequence of automated steps that moves data from the system where it is produced to the system where it is used, reshaping it on the way. For the shop that means a process that copies each day's purchases out of Postgres during the quiet hours and converts and cleans them. It stores the result in a system built to scan millions of rows, then refreshes the chart, all on a timer and with nobody in the loop. The copy closes the load gap and the timer the schedule gap. Cleaning and summing close the shape gap.

The word covers a 40-line Python script on a cron timer and a graph of 300 coordinated jobs equally. Whatever its size, a pipeline starts itself: the run on 15 March begins at 02:00 because a scheduler started it. It's repeatable, so the same day's input produces the same output on every run. And it is owned: a named person or team is paged when it fails. A script someone runs by hand when they remember is a report, however much code it contains.

The support team runs a query at 9:00 every morning against the checkout database to count yesterday's refunds grouped by reason, then pastes the counts into a shared sheet before standup. Which gaps does this task show?

About this lesson

This section is part of the Pipeline Anatomy: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.