What a Data Pipeline Is & Why It Matters

Your shop app saves every order in its own database. The boss wants a report of paid revenue for each day.

Someone must copy the orders to a second database and add them up. A data pipeline does this work every day, with nobody clicking buttons.

This chapter follows one small pipeline from start to end. A daily job copies the orders to a warehouse, then builds a "daily revenue" table.

A warehouse is a database built for analysis. You met it in the OLTP vs OLAP chapter. The shop is the same shop as in the SQL chapters.

In this chapter you will learn:

1. What a pipeline is, in plain words.
2. Why people build pipelines instead of copying by hand.
3. The five stages every pipeline has.
4. How to follow the daily job, stage by stage, with real rows.
5. Why running a job twice must give the same result.
6. The four ways pipelines fail, and how to explain a pipeline in one minute.

A data pipeline moves data from where it is made to where it is used. It works in steps, and it runs on a schedule.

Our shop pipeline has three parts:

• Where the data is made, called the source. Here it is the orders table in the app database.
• Where the data is used, called the destination. Here it is a daily revenue table that a dashboard shows. A dashboard is a screen of charts and numbers.
• The steps in between: copy the orders, keep the paid ones, add them up, save the result.

A job is one program run that does the pipeline work. A schedule says when the job runs. Ours runs at 02:00 every night, for the day before.

One query is not a pipeline. A pipeline is the whole chain, and it runs again and again without help.

Imagine an analyst who copies the orders by hand each morning. She exports a file, loads it into the warehouse, then runs the sum.

This works on day one. By day thirty it breaks in small ways. This table shows three of them:

A log is a written record of what a job did and when. The pipeline does not make people cleverer. It makes the work repeatable.

Every pipeline passes data through five stages. Here they are, with our shop job under each name:

Each stage has one job, and each can fail in its own way:

The dashed line in the picture means checks and alerts. A check tests the data, for example "every paid order has an amount". An alert tells a person when a check fails.

Real pipelines add stages or change their order. The next chapter counts the stages in a second way.

On its first run, the job loads every order so far. The source is the shop.orders table:

-- Copy the orders as they are. Change nothing.
INSERT INTO warehouse.raw_orders
SELECT order_id, customer_id, status, amount, order_date
FROM shop.orders;

SELECT COUNT(*) AS rows_copied
FROM warehouse.raw_orders;

All six orders arrive, including order 105, whose amount is still empty. Ingest copies the rows. It does not check or change them.

Now the transform and store stages. The job keeps paid orders and adds up the amounts for each day:

CREATE TABLE warehouse.daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM warehouse.raw_orders
WHERE status = 'paid'
GROUP BY order_date;

SELECT order_date, revenue
FROM warehouse.daily_revenue
ORDER BY order_date;

Four paid orders give four days. Refunded order 103 and pending order 105 are gone, because of the WHERE filter. The filter and the sum together are the transform.

Last, the serve stage. The dashboard asks for revenue from 2026-01-10 to 2026-01-21:

SELECT SUM(revenue) AS revenue
FROM warehouse.daily_revenue
WHERE order_date BETWEEN '2026-01-10' AND '2026-01-21';

Three days match: 25.00 + 15.50 + 80.00 = 120.50. The dashboard reads this small table. It never touches the app database.

Run these scripts a second time and there are problems. CREATE TABLE fails, because the table exists. The INSERT copies all six orders again.

There are many tool names, and this can feel confusing. Each tool does the job of one or two stages. This table gives common examples:

A scheduler runs the stages in order, at the right time. Airflow is a common scheduler. Chapter 10 teaches it.

You do not need these tools yet. Our shop job used only SQL. When you meet a new tool, ask which box it fills.

Our job is a batch job. A batch pipeline collects data and moves it in groups, on a schedule, such as once a night.

A streaming pipeline moves each event soon after it happens, often in seconds. An event is one thing that happened, such as one order placed.

Ask how fresh the result must be. A daily report can wait until morning, and a fraud alert cannot.

Chapter 3 teaches how to choose. The next sections use batch.

Jobs fail halfway. A person or the scheduler then runs the job again. Sometimes a job runs twice by mistake.

A safe job gives the same result after one run or two. You learned the word for this: idempotent. Idempotent means safe to run twice.

See what happens without it. The job copies one day, 2026-01-15, with a plain INSERT.

For this test, imagine the warehouse table is empty. Run this script twice:

INSERT INTO warehouse.raw_orders
SELECT order_id, customer_id, status, amount, order_date
FROM shop.orders
WHERE order_date = '2026-01-15';

-- After the second run:
SELECT order_id, status, amount
FROM warehouse.raw_orders
WHERE order_date = '2026-01-15';

Order 104 is in the table twice. The revenue for 2026-01-15 would now show 31.00 instead of 15.50. No error appears.

The fix: each run first deletes its own day, then loads the day again. You saw this in the OLTP vs OLAP chapter. Run the script twice:

BEGIN;
DELETE FROM warehouse.raw_orders
WHERE order_date = '2026-01-15';
INSERT INTO warehouse.raw_orders
SELECT order_id, customer_id, status, amount, order_date
FROM shop.orders
WHERE order_date = '2026-01-15';
COMMIT;

SELECT order_id, status, amount
FROM warehouse.raw_orders
WHERE order_date = '2026-01-15';

The second run deletes the first load, then loads the day again. One row stays.

BEGIN and COMMIT make a transaction. A transaction is a group of statements that all succeed, or all fail together.

Another fix is a key and an upsert, which means "insert, or replace if the key exists". The Python chapter showed INSERT OR REPLACE. Chapter 11 teaches both.

Pipelines fail in four common ways. Each one has a shop version. A schema is the list of columns in a table and their types:

A schema change breaks any job that expects the old list of columns.

One more risk is in the copy step. You met watermarks in the Python chapters. If the job moves the marker before the load ends and then crashes, the next run never copies those rows.

Good pipelines count the rows they copy, check the result, and alert a person. The Practice Lab at the end of this chapter lets you design these checks.

Interviewers often say: "Describe a pipeline you built, from start to end." Answer in this order, with our shop job:

1. Goal: the business needed paid revenue for each day.
2. Schedule: a batch job runs at 02:00 and reads yesterday’s orders from the app database.
3. Stages: it copies the rows to the warehouse, keeps paid orders, adds them up by day and saves daily_revenue.
4. Serve: a dashboard reads that table.
5. Safety: a re-run gives the same result.
6. Checks: it counts the rows it copies, checks that yesterday’s orders have arrived, watches for schema changes, and alerts a person.

You can now say what a pipeline is and follow one end to end. Remember these points:

• A pipeline moves data from where it is made to where it is used, in steps, on a schedule.
• The five stages are source, ingest, transform, store and serve. Checks and alerts watch all of them.
• Batch moves data in groups. Streaming moves each event as it happens.
• A safe job gives the same result when it runs twice.
• Late data, duplicate runs, bad rows and schema changes are the four common failures.

Try these on paper:

1. Draw the five stages for a pipeline that loads taxi rides into a warehouse. Name one failure for each stage.
2. The shop job ran twice for 2026-01-10 with a plain INSERT. Which order doubles, and what revenue shows? (Answer: order 102, and 50.00 instead of 25.00.)
3. Say the one-minute answer out loud, without looking.

Each exercise opens on the pipeline canvas, a tool where you draw a pipeline as boxes and arrows. A check tells you which requirement your design missed.

The last three exercises are optional. Try them after the chapters they name.