The Data Pipeline Lifecycle

Chapter 1 showed the shop pipeline in five stages: source, ingest, transform, store and serve. Checks and alerts watched all of them.

This chapter counts the stages in a second way, as the life of the data. The five stages are ingest, store, transform, serve and observe.

We follow one night of the same pipeline, the run at 02:00 on 2026-01-22. The data is the same six orders from the SQL chapters.

In this chapter you will learn:

1. How the five stages of the lifecycle fit together.
2. Why ingest copies the data and changes nothing.
3. Why you keep the raw data, and then clean it in layers.
4. Why the order of the transform steps changes the answer.
5. How a change in a table can break the dashboards that read it.
6. What to check at every stage, and why late data exists.

The lifecycle is the path data takes through a pipeline. The pipeline repeats it every night.

Chapter 1 called the app database the source. It is where the data starts, so it is not a stage here. Two things change in this view.

• Store comes before transform. You save the raw data first, then you change it.
• Observe is a stage, with its own checks. It watches the other four.

A warehouse is a database built for analysis. A layer is one table, or a set of tables, at one level of cleaning.

This table follows the 02:00 run on 2026-01-22. Look at the last column. It gives what each stage leaves behind.

Transform reads the raw table, and serve reads the table transform builds. So you can fix and re-run transform without copying the data again.

The next sections open each stage.

Ingest copies data from the source into the warehouse. The main rule is to copy it exactly as it is and change nothing.

The job adds one column, loaded_at. It records when each row arrived.

This is the job’s first run, so raw_orders is empty before it. Here is the 02:00 run:

INSERT INTO raw_orders
SELECT order_id, customer_id, status, amount, order_date,
       CURRENT_TIMESTAMP AS loaded_at
FROM shop.orders;

SELECT * FROM raw_orders
ORDER BY order_id;

All six orders arrive, even order 103 (refunded) and order 105 (pending, with no amount). Ingest does not decide which rows are good.

Now a made-up problem. Suppose the job used a tool that sends rows one at a time, instead of the INSERT above.

It sends order 106, but the "done" message never comes back. So the tool sends order 106 again at 02:05.

This query finds orders that were copied more than once:

SELECT order_id, COUNT(*) AS copies
FROM raw_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Raw now holds order 106 two times. This is fine for now. Ingest only records what arrived, and the next stages fix it.

Store means you save the data in layers. The raw layer is the copy exactly as it arrived. Later layers are cleaner.

Our shop uses three layers. This table shows each one with its row count at the end of the run:

Many teams use these names: raw is bronze, cleaned is silver, and business-ready is gold. The names are a habit, not a rule. Some teams use two layers and some use five.

Why keep the raw layer? Only raw shows when each copy arrived.

Silver keeps one row for each order. Raw still shows both copies of order 106:

SELECT order_id, loaded_at
FROM raw_orders
WHERE order_id = 106
ORDER BY loaded_at;

The two copies arrived at 02:00 and 02:05. A cleaned layer that kept one row could not show this.

If a cleaning rule is wrong, you fix the rule and rebuild the cleaned layer from raw. You do not need to read the shop database again. The shop may have changed those rows by then.

Transform changes raw data into data you can trust. It has two steps in our shop: clean, then add up.

Step one is the clean step. It builds silver_orders with one row for each order. The two copies of order 106 differ only in loaded_at, so we leave that column out:

CREATE OR REPLACE TABLE silver_orders AS
SELECT DISTINCT order_id, customer_id, status, amount, order_date
FROM raw_orders;

SELECT * FROM silver_orders
ORDER BY order_id;

DISTINCT keeps one copy of rows that are exactly the same. The seven raw rows become six, so order 106 appears once.

If two copies differ in any column, such as a new status, DISTINCT keeps both. Chapter 8, Transformation Fundamentals, shows how to keep one row for each order.

Step two adds up the paid orders for each day. It reads the cleaned table:

CREATE OR REPLACE TABLE daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM silver_orders
WHERE status = 'paid'
GROUP BY order_date;

SELECT * FROM daily_revenue
ORDER BY order_date;

Four paid orders give four days. The WHERE filter removes refunded order 103 and pending order 105.

Now swap the order. Add up first, straight from raw, and the copy of order 106 counts twice:

SELECT order_date, SUM(amount) AS revenue
FROM raw_orders
WHERE status = 'paid'
GROUP BY order_date
ORDER BY order_date;

Chapter 1 added up straight from raw. That was safe there, because raw held each order once.

Serve gives the finished data to the people and programs that use it. You ran this query in chapter 1, with the table name shortened:

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

It returns 120.50, as in chapter 1.

Now the dashboard depends on daily_revenue. Its column names and its meaning are a promise. Here are three changes the data team could make later:

The error is the good case, because a person sees it. The other two are silent. Nobody is told, and people decide with a wrong number.

Old numbers are called stale data. Chapter 22, Schema Evolution & Data Contracts, covers how to change a table without breaking its readers.

Observe means you check the other four stages and alert a person when a check fails. Every stage above can fail with no error message. Observe finds those failures.

Remember: a schema is the list of columns in a table and their types.

Observe watches four things. This table gives each one with a check for our shop:

Here are the volume checks for tonight. We count distinct order ids in raw, because raw holds a retried copy. Each (SELECT …) runs on its own and gives one number:

SELECT
  (SELECT COUNT(*) FROM shop.orders) AS source_rows,
  (SELECT COUNT(DISTINCT order_id) FROM raw_orders) AS raw_orders_seen,
  (SELECT COUNT(*) FROM silver_orders) AS silver_rows;

All three counts are 6, so no order was lost. If one count were lower, you would know which stage lost the rows. The quality check comes next:

SELECT COUNT(*) AS paid_without_amount
FROM silver_orders
WHERE status = 'paid' AND amount IS NULL;

The answer 0 means the check passes. Order 105 has no amount, but its status is pending, so it is not counted. A paid order with no amount would give 1, and SUM would skip it silently.

Lineage is a map of which table reads which. Ours is shop.orders, then raw_orders, then silver_orders, then daily_revenue, then the dashboard.

If raw_orders is wrong, silver_orders, daily_revenue and the dashboard are all in doubt. shop.orders is not.

Bounded data has an end. Yesterday’s orders file is bounded, because all of it exists once the day is over.

Unbounded data never ends. The shop’s order events are unbounded, because a new order can arrive at any second. An event log is a list of events that only grows.

Data that never ends can only be added up between two times. So you choose a cut-off, a moment when the job stops waiting. Anything that arrives after it is late data.

Here is a made-up example. The job at 02:00 adds up orders from 2026-01-21 that arrived before the cut-off:

SELECT SUM(amount) AS revenue
FROM order_events
WHERE status = 'paid'
  AND order_time >= '2026-01-21'
  AND order_time <  '2026-01-22'
  AND arrived_at <  '2026-01-22 02:00';

Order 107 was placed at 23:59, but it arrived at 02:01. That is after the cut-off, so it is late. The report shows 80.00, and the true total is 92.00.

There are two common fixes. Move the cut-off later, or re-run the day after the late rows arrive.

Chapter 1 gave you batch and streaming. Keep them apart from bounded and unbounded:

Batch vs Streaming vs Micro-Batch, the next chapter, shows how to choose between the ways to run.

You can now follow data through all five stages. Remember these points:

• The five stages are ingest, store, transform, serve and observe. Ingest and transform leave saved tables for the next stage.
• Ingest copies and changes nothing. Raw is the copy you can always go back to.
• Clean first, then add up. The wrong order gave 160.00 instead of 80.00.
• A table that people read is a promise. Silent changes are worse than errors.
• Observe checks freshness, volume, quality and schema, at every stage.
• Data that never ends needs a cut-off, and some rows will arrive after it.

Try these on paper:

1. Name the five stages of this chapter for a pipeline that loads bike rentals. For each stage, write one check that observe runs.
2. Raw holds refunded order 103 twice. Does daily_revenue change if you add up before you clean? (Answer: No. The WHERE filter removes both copies.)
3. Order 105 turns into a paid order, still with no amount. What does the quality check return? (Answer: 1.)

These exercises use the same pipeline canvas as chapter 1. Put a check on every stage.

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