What is a data pipeline? A beginner's guide with a tiny example in SQL

Every morning a manager opens a dashboard that shows yesterday's sales. Nobody typed those numbers in. Overnight, something collected the orders, cleaned them, added them up and updated the dashboard. That "something" is a data pipeline, and building and running pipelines is the core of data engineering.

A pipeline is just a series of steps that moves and prepares data, set up so that it runs again and again without a person pressing buttons. Think of a factory line: raw material goes in one end, and a finished product comes out the other.

The parts of a pipeline

1. 1Source. Where the data is created: an app database, a payment system, files, an API.
2. 2Ingest. Copy the data out of the source. Usually on a schedule (every night) or as it happens.
3. 3Store. Keep the raw data somewhere safe, so you can always go back to it.
4. 4Transform. Clean, combine and calculate: fix types, remove duplicates, build totals.
5. 5Serve. Put the final tables where people use them: a warehouse (a database built for reports and analysis), a dashboard, a report.
6. 6Orchestrate and monitor. Something runs the steps in the right order, retries a failed step, and warns a human when it cannot recover. Apache Airflow is the best known tool for this.

The first 5 steps are the data's journey. The sixth is what makes it a pipeline instead of a one-off script.

A tiny pipeline you can run

Here is the smallest useful pipeline, in SQL. The source is a table of orders. The job is to build a daily_revenue table with 1 row per day. The queries ran in DuckDB, and each output is exactly what it printed.

CREATE TABLE source_orders (order_id INTEGER, order_date DATE, amount INTEGER);
INSERT INTO source_orders VALUES
  (1, '2026-03-01', 1200),
  (2, '2026-03-01',  800),
  (3, '2026-03-02',  450),
  (4, '2026-03-02', 2500),
  (5, '2026-03-02',  300),
  (6, '2026-03-03',  950);

CREATE TABLE daily_revenue (order_date DATE, orders INTEGER, revenue INTEGER);

The pipeline job for 1 day is "delete that day's rows from the result, then insert the freshly calculated ones". Run it for 1 March:

DELETE FROM daily_revenue WHERE order_date = DATE '2026-03-01';

INSERT INTO daily_revenue
SELECT order_date, COUNT(*), SUM(amount)
FROM source_orders
WHERE order_date = DATE '2026-03-01'
GROUP BY order_date;

SELECT * FROM daily_revenue ORDER BY order_date;

order_date | orders | revenue
-----------+--------+--------
2026-03-01 | 2      | 2000
(1 row)

1 row for 1 March: 2 orders, 2000 in revenue.

Why running it twice must be safe

Jobs fail. A server restarts at 3 am, so the scheduler runs the job again. If running a job twice changes the answer, you now have a wrong number and no error to tell you. A job that gives the same result however many times you run it is called idempotent, and it is the most useful word to know in this field.

Run the same job a second time:

DELETE FROM daily_revenue WHERE order_date = DATE '2026-03-01';

INSERT INTO daily_revenue
SELECT order_date, COUNT(*), SUM(amount)
FROM source_orders
WHERE order_date = DATE '2026-03-01'
GROUP BY order_date;

SELECT * FROM daily_revenue ORDER BY order_date;

order_date | orders | revenue
-----------+--------+--------
2026-03-01 | 2      | 2000
(1 row)

The result is exactly the same: still 1 row, still 2000. That is because the job deletes the day first and then rebuilds it. Compare that with a careless version that only inserts:

CREATE TABLE daily_revenue_careless (order_date DATE, orders INTEGER, revenue INTEGER);

INSERT INTO daily_revenue_careless
SELECT order_date, COUNT(*), SUM(amount) FROM source_orders
WHERE order_date = DATE '2026-03-01' GROUP BY order_date;

INSERT INTO daily_revenue_careless
SELECT order_date, COUNT(*), SUM(amount) FROM source_orders
WHERE order_date = DATE '2026-03-01' GROUP BY order_date;

SELECT order_date, SUM(revenue) AS reported_revenue
FROM daily_revenue_careless
GROUP BY order_date;

order_date | reported_revenue
-----------+-----------------
2026-03-01 | 4000
(1 row)

4000 is reported for a day that really made 2000, because the same day was added twice. Nothing failed, and the dashboard is now wrong. This is the single most common beginner bug in pipelines.

Check the result, always

A pipeline can finish without errors and still be wrong. So you add a check that compares the output with the source. Load the other 2 days too, then compare the total:

DELETE FROM daily_revenue WHERE order_date IN (DATE '2026-03-02', DATE '2026-03-03');

INSERT INTO daily_revenue
SELECT order_date, COUNT(*), SUM(amount)
FROM source_orders
WHERE order_date IN (DATE '2026-03-02', DATE '2026-03-03')
GROUP BY order_date;

SELECT (SELECT SUM(amount)  FROM source_orders)  AS source_total,
       (SELECT SUM(revenue) FROM daily_revenue)  AS result_total,
       (SELECT SUM(amount)  FROM source_orders)
         - (SELECT SUM(revenue) FROM daily_revenue) AS difference;

source_total | result_total | difference
-------------+--------------+-----------
6200         | 6200         | 0
(1 row)

A difference of 0 means every rupee in the source reached the result. In a real pipeline, a check like this fails the job and sends an alert whenever the number is not zero, so a human hears about the problem before the dashboard does.

Batch and streaming

This pipeline runs once a day on a pile of data, which is called batch processing. Some pipelines handle each event as it arrives, which is called streaming. Beginners should start with batch, and the next step is batch vs stream processing.

What to learn next

1. 1Write the transform step well: SQL for beginners and GROUP BY.
2. 2Learn the language of the steps: ETL vs ELT.
3. 3Learn how pipelines are scheduled: what is Apache Airflow.

Build one end to end for your portfolio. The capstone projects page has ideas, and the Orchestration & Reliable Pipelines track teaches scheduling and safe reruns step by step.